RODBC on Oracle DB
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.