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

[Marc Schwartz]

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.

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.