Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: -odbc load- command for Oracle views: Workaround?


From   David Radwin <radwin@berkeley.edu>
To   statalist@hsphsun2.harvard.edu
Subject   st: -odbc load- command for Oracle views: Workaround?
Date   Tue, 21 Apr 2009 10:02:32 -0700

Dear Statalisters,

For some irresolvable reason, I cannot -odbc load- views of tables from our Oracle database when my username is not the owner of the view even when I have permission to read them. (I also have the same problem with Excel 2008 v. 12.1.2, so this is not strictly a Stata problem.) I can load views that I own as well as tables that I own or have permission to read. I don't have the technical knowledge or support to fully diagnose the problem, and I have given up on trying to solve it directly.

I can -odbc describe- and -odbc exec, (SELECT * from)- the same view. But what I really need to do is load the file into memory. Is there some workaround that I might employ to achieve the same result as -odbc load-?

I am using Actual Oracle driver 1.0 rc3 with Oracle 10 and 32-bit Intercooled Stata 10.1 on an Intel Mac Pro with OS 10.5.5. The actual (slightly redacted) output is below. The owner of the view is "osr1" and my username is "david".

Thank you for your help.

David

================================

. odbc describe "TEMP1_DBR_VW", dsn("###") user(david) password(###)
Connection Parameters: DSN=###;UID=david;PWD=###;USER_TYPE=0;SERVER=###;PORT=1521;DATABASE=###;TYPE=;

DataSource: ### (query)
Table:      TEMP1_DBR_VW (load)
-------------------------------------------------------------------------------
Variable Name                               Variable Type
-------------------------------------------------------------------------------
STRING1                                     VARCHAR2
STRING2                                     VARCHAR2
NUM1                                        VARCHAR2
NUM2                                        VARCHAR2
-------------------------------------------------------------------------------

. odbc exec("SELECT * FROM osr1.TEMP1_DBR_VW"), dsn("osr") user(david) password(###)
Connection Parameters: DSN=###;UID=david;PWD=###;
SELECT * FROM osr1.TEMP1_DBR_VW
1. +---------------------------------------------------------------------------------------------------------------------------------+ |STRING1 | entry1 | |STRING2 | entry2 | |NUM1 | 12345678 | |NUM2 | 1 |

+---------------------------------------------------------------------------------------------------------------------------------+

2. +---------------------------------------------------------------------------------------------------------------------------------+ |STRING1 | entry3 | |STRING2 | entry4 | |NUM1 | 90909 | |NUM2 | 2 |

+---------------------------------------------------------------------------------------------------------------------------------+

3. +---------------------------------------------------------------------------------------------------------------------------------+ |STRING1 | entry5 | |STRING2 | entry6 | |NUM1 | 666666 | |NUM2 | 3 |

+---------------------------------------------------------------------------------------------------------------------------------+



. odbc load , table("TEMP1_DBR_VW") lowercase clear user(david) password(**) sqlshow
Connection Parameters (IN): DSN=###;UID=david;PWD=###;
Connection Parameters (OUT): DSN=###;UID=david;PWD=###;USER_TYPE=0;SERVER=###;PORT=1521;DATABASE=###;TYPE=;
SELECT * FROM "TEMP1_DBR_VW"
[Actual][Oracle] ORA-00942: table or view does not exist

r(682);

end of do-file

r(682);


--
David Radwin // radwin@berkeley.edu
Office of Student Research and Campus Surveys, University of California, Berkeley
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index