<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi,<br>
<br>
First, my apologies for a lengthy email.<br>
<br>
I have been trying to get connectivity between R and Oracle over the
past few days (I am new to R, but know Oracle and Linux).<br>
<br>
I have tried both RODBC and ROracle methods to connect, but having
issues with both.<br>
<br>
Below is our environment, what I have done, and the issues to
date. I would much appreciate any help on this, as I'm not sure
what to do now.<br>
<br>
Our server:<br>
- RHEL 5.7 64-bit<br>
- Oracle 11.2.0.3 64-bit<br>
- R version 2.14.0 (2011-10-31) 64-bit i.e. .Machine$sizeof.pointer
= 8.<br>
<br>
<u>RODBC</u><br>
<br>
I have installed unixODBC-2.2.14, as I had issues with the standard
RHEL 2.2.11. <br>
<br>
Also installed is the following Oracle 11.2.0.3 64-bit instant
client software:<br>
- instantclient-basic-linux.x64-11.2.0.3.0<br>
- instantclient-odbc-linux.x64-11.2.0.3.0<br>
- instantclient-precomp-linux.x64-11.2.0.3.0<br>
- instantclient-sdk-linux.x64-11.2.0.3.0<br>
- instantclient-sqlplus-linux.x64-11.2.0.3.0<br>
<br>
With the unixODBC 2.2.11 rpm, I kept getting the error with isql:<br>
isql: symbol lookup error:
/u04/app/oracle/product/11.2.0.3/dbhome_1/lib/libsqora.so.11.1:
undefined symbol: SQLGetPrivateProfileStringW<br>
<br>
This was due to unixODBC 2.2.12+ not being certified on RHEL5 (see
attachment bug_12691067.txt for details). <br>
After installing the 2.2.14 unixODBC, I can now connect to the local
Oracle database with isql. <br>
<br>
So,... now I do the following as 'root':<br>
<tt>root@predict: </tt><tt>export
LD_LIBRARY_PATH=/opt/oracle-instantclient/P<br>
</tt><tt>root@predict: </tt><tt>export
ORACLE_HOME=/u04/app/oracle/product/11.2.0.3/dbhome_1<br>
</tt><tt>root@predict: </tt><tt>export ORACLE_SID=PREDICT<br>
</tt><tt>root@predict: </tt><tt>export
PATH=/u04/app/oracle/product/11.2.0.3/dbhome_1:/u04/app/oracle/product/11.2.0.3/dbhome_1/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin<br>
</tt><tt>root@predict: </tt><tt>export
TNS_ADMIN=/u04/app/oracle/product/11.2.0.3/dbhome_1/network/admin</tt><br>
<br>
<tt>root@predict:/u05/db_downloads> R<br>
<br>
R version 2.14.0 (2011-10-31)<br>
...<br>
...<br>
...<br>
> <br>
> Sys.getenv("ORACLE_SID")<br>
[1] "PREDICT"<br>
> Sys.getenv("ORACLE_HOME")<br>
[1] "/u04/app/oracle/product/11.2.0.3/dbhome_1"<br>
> Sys.getenv("LD_LIBRARY_PATH")<br>
[1]
"/usr/lib64/R/lib:/usr/local/lib64:/usr/lib/jvm/jre/lib/amd64/server:/usr/lib/jvm/jre/lib/amd64:/usr/lib/jvm/java/lib/amd64:/usr/java/packages/lib/amd64:/lib:/usr/lib:/opt/oracle-instantclient/P:/usr/lib64/R/lib:/usr/local/lib64:/usr/lib/jvm/jre/lib/amd64/server:/usr/lib/jvm/jre/lib/amd64:/usr/lib/jvm/java/lib/amd64:/usr/java/packages/lib/amd64:/lib:/usr/lib"<br>
> Sys.getenv("TNS_ADMIN")<br>
[1] "/u04/app/oracle/product/11.2.0.3/dbhome_1/network/admin"<br>
> library(RODBC)<br>
> hdl <- odbcConnect('predict', '<user>',
'<password>')<br>
<font color="#ff0000">/usr/lib64/R/bin/exec/R: symbol lookup
error: /opt/oracle-instantclient/P/libsqora.so.11.1: undefined
symbol: SQLGetPrivateProfileStringW</font><br>
root@predict:/u05/db_downloads> </tt><br>
<br>
I noticed the LD_LIBRARY_PATH had my instant client directory
appended to it, so I tried updating to be at the start, but I still
get the same error. i.e. set to:<br>
<tt>>
Sys.setenv(LD_LIBRARY_PATH="/opt/oracle-instantclient/P:/usr/lib64/R/lib:/usr/local/lib64:/usr/lib/jvm/jre/lib/amd64/server:/usr/lib/jvm/jre/lib/amd64:/usr/lib/jvm/java/lib/amd64:/usr/java/packages/lib/amd64:/lib:/usr/lib")</tt><br>
<br>
I find it strange that isql works, but ROracle gives the same error
as though I'm running on the unixODBC 2.2.11. Is there something
else I need to configure in R to fix this? <br>
<br>
<br>
<br>
<u>ROracle</u><br>
<br>
I now turn to trying to install the ROracle package, but am unable
to get that to install.<br>
<br>
I have looked at this posting:
<a class="moz-txt-link-freetext" href="http://www.mail-archive.com/r-help@r-project.org/msg121901.html">http://www.mail-archive.com/r-help@r-project.org/msg121901.html</a><br>
... and the ROracle install package at
<a class="moz-txt-link-freetext" href="http://cran.r-project.org/web/packages/ROracle/INSTALL">http://cran.r-project.org/web/packages/ROracle/INSTALL</a><br>
<br>
<tt>root@predict:/u05/db_downloads> R CMD INSTALL
ROracle_0.5-12.tar.gz<br>
* installing to library ‘/usr/lib64/R/library’<br>
* installing *source* package ‘ROracle’ ...<br>
** package ‘ROracle’ successfully unpacked and MD5 sums checked<br>
checking for gcc... gcc<br>
checking for C compiler default output... a.out<br>
checking whether the C compiler works... yes<br>
checking whether we are cross compiling... no<br>
checking for suffix of executables... <br>
checking for suffix of object files... o<br>
checking whether we are using the GNU C compiler... yes<br>
checking whether gcc accepts -g... yes<br>
checking for gcc option to accept ANSI C... none needed<br>
checking how to run the C preprocessor... gcc -E<br>
configure: creating ./config.status<br>
config.status: creating src/Makevars<br>
config.status: creating src/Makefile<br>
** libs<br>
** arch - <br>
R CMD COMPILE RS-DBI.c<br>
make[1]: Entering directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
gcc -m64 -std=gnu99 -I/usr/include/R -DRS_ORA_SQLGLS_WORKAROUND
-I/usr/local/include -fpic -O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -c RS-DBI.c -o
RS-DBI.o<br>
make[1]: Leaving directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
proc CODE=ANSI_C MODE=ORACLE INCLUDE=/usr/lib64/R/include \<br>
PARSE=NONE LINES=false PREFETCH=1 RS-Oracle.pc<br>
<br>
Pro*C/C++: Release 11.2.0.3.0 - Production on Wed Dec 21 13:59:21
2011<br>
<br>
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All
rights reserved.<br>
<br>
System default option values taken from:
/u04/app/oracle/product/11.2.0.3/dbhome_1/precomp/admin/pcscfg.cfg<br>
<br>
R CMD COMPILE RS-Oracle.c<br>
make[1]: Entering directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
gcc -m64 -std=gnu99 -I/usr/include/R -DRS_ORA_SQLGLS_WORKAROUND
-I/usr/local/include -fpic -O2 -g -pipe -Wall
-Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector
--param=ssp-buffer-size=4 -m64 -mtune=generic -c RS-Oracle.c -o
RS-Oracle.o<br>
RS-Oracle.c: In function ‘RS_Ora_varCharCpy’:<br>
RS-Oracle.c:6726: warning: pointer targets in passing argument 1
of ‘__builtin___strcpy_chk’ differ in signedness<br>
RS-Oracle.c:6726: warning: pointer targets in passing argument 1
of ‘__strcpy_ichk’ differ in signedness<br>
RS-Oracle.c: At top level:<br>
RS-Oracle.c:114: warning: ‘sqlstm’ defined but not used<br>
make[1]: Leaving directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
R CMD SHLIB -o ROracle.so RS-DBI.o RS-Oracle.o<br>
make[1]: Entering directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
gcc -m64 -std=gnu99 -shared -L/usr/local/lib64 -o ROracle.so
RS-DBI.o RS-Oracle.o
-L/u04/app/oracle/product/11.2.0.3/dbhome_1/lib
-L/u04/app/oracle/product/11.2.0.3/dbhome_1/network/lib -lclntst11
-lnbeq11 -lnhost11 -lnus11 -lnldap11 -lldapclnt11 -lnsslb11
-lntcp11 -lntcps11 -lnsslb11 -lntcp11 -lntns11 -ldl -lm -lpthread
-lnsl -lirc -lipgo -lsvml -ldl -lm -L/usr/lib64/R/lib -lR<br>
make[1]: Leaving directory
`/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'<br>
installing to /usr/lib64/R/library/ROracle/libs<br>
** R<br>
** inst<br>
** preparing package for lazy loading<br>
Creating a generic function for ‘format’ from package ‘base’ in
package ‘ROracle’<br>
** help<br>
*** installing help indices<br>
converting help for package ‘ROracle’<br>
finding HTML links ... done<br>
DBIPreparedStatement-class html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/DBIPreparedStatement-class.Rd:17:
missing file link ‘dbPrepareStatement’<br>
OraConnection-class html <br>
OraDriver-class html <br>
OraObject-class html <br>
OraPreparedStatement-class html <br>
OraResult-class html <br>
Oracle html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:90:
missing file link ‘dbRollback’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:99:
missing file link ‘dbRollback’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:102:
missing file link ‘dbGetQuery’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:106:
missing file link ‘dbWriteTable’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:128:
missing file link ‘dbUnloadDriver’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:133:
missing file link ‘dbDisconnect’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:139:
missing file link ‘dbGetQuery’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:140:
missing file link ‘dbClearResult’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:147:
missing file link ‘dbRollback’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:154:
missing file link ‘dbListFields’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:155:
missing file link ‘dbListConnections’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:156:
missing file link ‘dbListResults’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:157:
missing file link ‘dbGetException’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:158:
missing file link ‘dbGetStatement’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:159:
missing file link ‘dbHasCompleted’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:160:
missing file link ‘dbGetRowCount’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:161:
missing file link ‘dbGetRowsAffected’<br>
S4R html <br>
dbCallProc-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbCallProc-methods.Rd:30:
missing file link ‘dbGetQuery’<br>
dbCommit-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbCommit-methods.Rd:31:
missing file link ‘dbGetQuery’<br>
dbConnect-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbConnect-methods.Rd:51:
missing file link ‘dbGetQuery’<br>
dbDataType-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbDataType-methods.Rd:32:
missing file link ‘isSQLKeyword’<br>
dbDriver-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbDriver-methods.Rd:38:
missing file link ‘dbGetQuery’<br>
dbGetInfo-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbGetInfo-methods.Rd:47:
missing file link ‘dbGetQuery’<br>
dbListTables-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbListTables-methods.Rd:36:
missing file link ‘dbColumnInfo’<br>
dbObjectId-class html <br>
dbPrepareStatement-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:86:
missing file link ‘dbClearResult’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:87:
missing file link ‘dbGetStatement’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:88:
missing file link ‘dbGetRowsAffected’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:109:
missing file link ‘dbGetQuery’<br>
dbReadTable-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbReadTable-methods.Rd:80:
missing file link ‘dbGetQuery’<br>
dbSendQuery-methods html <br>
dbSetDataMappings-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbSetDataMappings-methods.Rd:34:
missing file link ‘dbColumnInfo’<br>
fetch-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/fetch-methods.Rd:44:
missing file link ‘dbGetQuery’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/fetch-methods.Rd:45:
missing file link ‘dbClearResult’<br>
isIdCurrent html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/isIdCurrent.Rd:34:
missing file link ‘dbGetQuery’<br>
make.db.names-methods html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:67:
missing file link ‘dbWriteTable’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:68:
missing file link ‘dbExistsTable’<br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:69:
missing file link ‘dbRemoveTable’<br>
oraParseConParams html <br>
oraSupport html <br>
safe.write html <br>
Rd warning:
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/safe.write.Rd:21:
missing file link ‘oraWriteTable’<br>
summary-methods html <br>
** building package indices ...<br>
** testing if installed package can be loaded<br>
<font color="#ff0000">Error in dyn.load(file, DLLpath = DLLpath,
...) : <br>
unable to load shared object
'/usr/lib64/R/library/ROracle/libs/ROracle.so':<br>
/usr/lib64/R/library/ROracle/libs/ROracle.so: undefined
symbol: sqlprc</font><br>
Error: loading failed<br>
Execution halted<br>
ERROR: loading failed<br>
* removing ‘/usr/lib64/R/library/ROracle’<br>
</tt><br>
As mentioned, I've been through the R forums for answers, but still
get this error.<br>
<br>
I am unsure what to do next, and would much appreciate if anyone
would provide some help with either the RODBC method or ROracle
method to connect to my database.<br>
<br>
Thanks in advance,<br>
<br>
Stuart Uren<br>
Database Administrator<br>
Clinical Trials Research Unit<br>
The University of Auckland<br>
Auckland, New Zealand<br>
<br>
</body>
</html>