[R] [R-sig-DB] RODBC on Oracle DB

[eric at net2000.ch]

Yes, it's a quesion of rights. My system administrator just confirme that.
And I also try with a small Oracle client, and same as R, no permission to read
tables. 
On R with RODBC I could list the tables but no permission to read or import tables.
But I get confused because I was using the same Oracle user with an MS Access
client, wich works perfectly even to importe table from Oracle DB.
My system admin told me that this Oracle user is in fact a synomyme or duplicata
from the real Oracle account. But why this very same account can import table via
the MS Access client ? This remains mystery to me.  
As soon as may sysadmin return from vacations, I will clear that out. 
grettings et thanks to everyone.
Eric R. 



On Mer Juil 18 17:50 , Marc Schwartz  sent:

>I think that you are on to something there Don.
>
>I just tried accessing a table from our Oracle server, which I do know
>exists, but for which I do not have access permissions.
>
>Using the following query in the Oracle Instant Client:
>
>  select table_name from all_tables;
>
>I can get a list of all tables on the server, which includes a table
>called INCOMPATIBLE_USER_AGENTS, for which I do not have access
>permissions.
>
>When attempting to query the table in the Instant Client I get:
>
>SQL> select * from INCOMPATIBLE_USER_AGENTS;
>select * from INCOMPATIBLE_USER_AGENTS
>              *
>ERROR at line 1:
>ORA-00942: table or view does not exist
>
>
>When running the same query from R using RODBC I get:
>
>> sqlQuery(db, "select * from INCOMPATIBLE_USER_AGENTS")
>[1] "[RODBC] ERROR: Could not SQLExecDirect"                                
>[2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n"
>
>
>So it looks like permission issues may be possible here.  Eric,
>definitely confirm with your SysAdmins that you have appropriate
>permissions.
>
>HTH,
>
>Marc
>
>
>On Wed, 2007-07-18 at 07:43 -0700, Don MacQueen wrote:
>> I believe I have seen that error message from 
>> Oracle when I tried to query a table for which I 
>> did not have "select" privileges (and when I knew 
>> for certain that the table existed). Ask your 
>> database administrator about the table, and make 
>> sure that you do have that privilege.
>> 
>> What I am uncertain about is whether Oracle, when 
>> asked to list tables, returns a list that 
>> includes tables for which the user does not have 
>> select privileges.
>> 
>> -Don
>> 
>> At 9:24 AM +0200 7/17/07, eric at net2000.ch wrote:
>> >essai 
>> >uid="osis_r",  pwd="12miss15" ,case="oracle")
>> >
>> >>  sqlTables(essai)$ORESTE
>> >
>> >...
>> >
>> >1315            ORESTE              S_PROFESSIONS_OLD        TABLE    
>> >1316            ORESTE                  S_PROVENANCES        TABLE    
>> >1317            ORESTE                        S_SEXES        TABLE    
>> >1318            ORESTE                 S_SOUS_CLASSES        TABLE    
>> >1319            ORESTE                 S_TYP_COLLEGES        TABLE    
>> >1320            ORESTE             S_TYP_ENSEIGNEMENT        TABLE    
>> >
>> >...
>> >
>> >>  sqlQuery(essai, "select * from S_TYP_COLLEGES")
>> >[1] "[RODBC] ERROR: Could not SQLExecDirect"                           
>> >[2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
>> >
>> >I have also tried the
>> >essai2 
>> >But with no succes.
>> >
>> >
>> >
>> >On Lun Juil 16 15:32 , Prof Brian Ripley ripley at stats.ox.ac.uk> sent:
>> >
>> >>The problem could be quoting, if Oracle is not standards-compliant.
>> >>See the options in ?odbcConnect.
>> >>
>> >>If sqlQuery(essai, "select * from S_TYP_COLLEGES") works, this is likely
>> >>to be the problem.
>> >>
>> >>On Mon, 16 Jul 2007, eric at net2000.ch wrote:
>> >>
>> >>>
>> >>>
>> >>>>  essai
>> >>>>  odbcGetInfo(essai)
>> >>>        DBMS_Name         DBMS_Ver  Driver_ODBC_Ver
>> >>>         "Oracle"     "09.00.0121"          "03.51"
>> >>>  Data_Source_Name      Driver_Name       Driver_Ver
>> >>>    "ORESTE_prod"    "SQORA32.DLL"     "09.00.0101"
>> >>>         ODBC_Ver      Server_Name
>> >>>     "03.52.0000"           "weba"
>> >>>
>> >>>
>> >>>>  sqlTables(essai)
>> >>>
>> >>>  The result of this function is a liste of tables, one of them is called:
>> >>>  S_TYP_COLLEGES.
>> >>>
>> >>>
>> >>>>  sqlFetch(essai,"S_TYP_COLLEGES")
>> >>>  [1] "[RODBC] ERROR: Could not SQLExecDirect"
>> >>>  [2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
>> >>>
>> >>>>  sqlFetch(essai, "S_TYP_COLLEGES", colnames=TRUE, rownames=FALSE)
>> >>>  [1] "[RODBC] ERROR: Could not SQLExecDirect"
>> >>>  [2] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: Table ou vue inexistante\n"
>> >  >>
>> >  >>
>> >  >> What could be the problem here ?
>> >  >> Any help is welcome
>> >  >> Eric Röthlisberger, Neuchâtel
>> >  >>
>