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

[Prof Brian Ripley]

On Tue, 17 Jul 2007, Marc Schwartz wrote:

> Try the sqlQuery() syntax with a semi-colon at the end of it:
>
>  sqlQuery(essai, "select * from S_TYP_COLLEGES;")
>
> Oracle requires the semi-colon at the end of the SQL statement.

Over ODBC?  I've never heard of that, and others have used RODBC to 
Oracle successfully when it does not itself add semicolons.  What sqlFetch 
is running is (in a vanilla setup) "SELECT * FROM 'S_TYP_COLLEGES'", and 
we've heard of a few cases where the quotes were a problem, hence my 
suggestion.

The error message is not a syntax error (which is what I would expect 
from a missing terminator), but that the table/view is not found.

> If that does not help, try these queries using the Oracle Instant Client
> command line application outside of R and see if your queries work
> there.  If so, then we can likely isolate the problem to R.  If not,
> then there is an ODBC/Oracle configuration issue.
>
> If you are unsure of how to use (or perhaps install) the Oracle Instant
> Client, check with one of your SysAdmins.
>
> BTW, unstated is the OS here, but I presume Windows, given the ODBC
> driver version and DLL noted previously.
>
> HTH,
>
> Marc Schwartz
>
>
> On Tue, 2007-07-17 at 09:24 +0200, 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.
>

-- 
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