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 // [email protected]
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/
