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

[Marc Schwartz]

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 <- odbcConnect("ORESTE_prod", 
> >uid="osis_r",  pwd="12miss15" ,case="oracle")
> >
> >>  sqlTables(essai)$ORESTE
> >
> >...
> >
> >1315      <NA>      ORESTE              S_PROFESSIONS_OLD        TABLE    <NA>
> >1316      <NA>      ORESTE                  S_PROVENANCES        TABLE    <NA>
> >1317      <NA>      ORESTE                        S_SEXES        TABLE    <NA>
> >1318      <NA>      ORESTE                 S_SOUS_CLASSES        TABLE    <NA>
> >1319      <NA>      ORESTE                 S_TYP_COLLEGES        TABLE    <NA>
> >1320      <NA>      ORESTE             S_TYP_ENSEIGNEMENT        TABLE    <NA>
> >
> >...
> >
> >>  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 <- odbcDriverConnect(connection="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
> >  >>