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

[Don MacQueen]

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
>  >>
>  >> _______________________________________________
>>>  R-sig-DB mailing list -- R Special Interest Group
>>>  R-sig-DB at stat.math.ethz.ch
>>>  https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>>
>>
>>--
>>Brian D. Ripley,                  ripley at stats.ox.ac.uk
>>Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
>>University of Oxford,             Tel:  +44 1865 272861 (self)
>>1 South Parks Road,                     +44 1865 272866 (PA)
>>Oxford OX1 3TG, UK                Fax:  +44 1865 272595
>
>______________________________________________
>R-help at stat.math.ethz.ch mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.


-- 
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
925-423-1062