[R-sig-DB] Unable to get RODBC or ROracle to work on Linux

Stuart Uren @@uren @end|ng |rom ctru@@uck|@nd@@c@nz
Wed Dec 21 02:27:32 CET 2011 

Hi,

First, my apologies for a lengthy email.

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

I have tried both RODBC and ROracle methods to connect, but having 
issues with both.

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.

Our server:
- RHEL 5.7 64-bit
- Oracle 11.2.0.3 64-bit
- R version 2.14.0 (2011-10-31) 64-bit  i.e. .Machine$sizeof.pointer = 8.

_RODBC_

I have installed unixODBC-2.2.14, as I had issues with the standard RHEL 
2.2.11.

Also installed is the following Oracle 11.2.0.3 64-bit instant client 
software:
- instantclient-basic-linux.x64-11.2.0.3.0
- instantclient-odbc-linux.x64-11.2.0.3.0
- instantclient-precomp-linux.x64-11.2.0.3.0
- instantclient-sdk-linux.x64-11.2.0.3.0
- instantclient-sqlplus-linux.x64-11.2.0.3.0

With the unixODBC 2.2.11 rpm, I kept getting the error with isql:
isql: symbol lookup error: 
/u04/app/oracle/product/11.2.0.3/dbhome_1/lib/libsqora.so.11.1: 
undefined symbol: SQLGetPrivateProfileStringW

This was due to unixODBC 2.2.12+ not being certified on RHEL5 (see 
attachment bug_12691067.txt for details).
After installing the 2.2.14 unixODBC, I can now connect to the local 
Oracle database with isql.

So,... now I do the following as 'root':
root using predict: export LD_LIBRARY_PATH=/opt/oracle-instantclient/P
root using predict: export ORACLE_HOME=/u04/app/oracle/product/11.2.0.3/dbhome_1
root using predict: export ORACLE_SID=PREDICT
root using predict: 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
root using predict: export 
TNS_ADMIN=/u04/app/oracle/product/11.2.0.3/dbhome_1/network/admin

root using predict:/u05/db_downloads> R

R version 2.14.0 (2011-10-31)
...
...
...
 >
 > Sys.getenv("ORACLE_SID")
[1] "PREDICT"
 > Sys.getenv("ORACLE_HOME")
[1] "/u04/app/oracle/product/11.2.0.3/dbhome_1"
 > Sys.getenv("LD_LIBRARY_PATH")
[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"
 > Sys.getenv("TNS_ADMIN")
[1] "/u04/app/oracle/product/11.2.0.3/dbhome_1/network/admin"
 > library(RODBC)
 > hdl <- odbcConnect('predict', '<user>', '<password>')
/usr/lib64/R/bin/exec/R: symbol lookup error: 
/opt/oracle-instantclient/P/libsqora.so.11.1: undefined symbol: 
SQLGetPrivateProfileStringW
root using predict:/u05/db_downloads>

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:
 > 
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")

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?



_ROracle_

I now turn to trying to install the ROracle package, but am unable to 
get that to install.

I have looked at this posting:   
http://www.mail-archive.com/r-help@r-project.org/msg121901.html
... and the ROracle install package at 
http://cran.r-project.org/web/packages/ROracle/INSTALL

root using predict:/u05/db_downloads> R CMD INSTALL ROracle_0.5-12.tar.gz
* installing to library '/usr/lib64/R/library'
* installing *source* package 'ROracle' ...
** package 'ROracle' successfully unpacked and MD5 sums checked
checking for gcc... gcc
checking for C compiler default output... a.out
checking whether the C compiler works... yes
checking whether we are cross compiling... no
checking for suffix of executables...
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ANSI C... none needed
checking how to run the C preprocessor... gcc -E
configure: creating ./config.status
config.status: creating src/Makevars
config.status: creating src/Makefile
** libs
** arch -
R CMD COMPILE RS-DBI.c
make[1]: Entering directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
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
make[1]: Leaving directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
proc CODE=ANSI_C MODE=ORACLE INCLUDE=/usr/lib64/R/include \
                 PARSE=NONE LINES=false PREFETCH=1 RS-Oracle.pc

Pro*C/C++: Release 11.2.0.3.0 - Production on Wed Dec 21 13:59:21 2011

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights 
reserved.

System default option values taken from: 
/u04/app/oracle/product/11.2.0.3/dbhome_1/precomp/admin/pcscfg.cfg

R CMD COMPILE RS-Oracle.c
make[1]: Entering directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
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
RS-Oracle.c: In function 'RS_Ora_varCharCpy':
RS-Oracle.c:6726: warning: pointer targets in passing argument 1 of 
'__builtin___strcpy_chk' differ in signedness
RS-Oracle.c:6726: warning: pointer targets in passing argument 1 of 
'__strcpy_ichk' differ in signedness
RS-Oracle.c: At top level:
RS-Oracle.c:114: warning: 'sqlstm' defined but not used
make[1]: Leaving directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
R CMD SHLIB -o ROracle.so RS-DBI.o RS-Oracle.o
make[1]: Entering directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
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
make[1]: Leaving directory `/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/src'
installing to /usr/lib64/R/library/ROracle/libs
** R
** inst
** preparing package for lazy loading
Creating a generic function for 'format' from package 'base' in package 
'ROracle'
** help
*** installing help indices
   converting help for package 'ROracle'
     finding HTML links ... done
     DBIPreparedStatement-class              html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/DBIPreparedStatement-class.Rd:17: 
missing file link 'dbPrepareStatement'
     OraConnection-class                     html
     OraDriver-class                         html
     OraObject-class                         html
     OraPreparedStatement-class              html
     OraResult-class                         html
     Oracle                                  html
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:90: 
missing file link 'dbRollback'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:99: 
missing file link 'dbRollback'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:102: 
missing file link 'dbGetQuery'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:106: 
missing file link 'dbWriteTable'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:128: 
missing file link 'dbUnloadDriver'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:133: 
missing file link 'dbDisconnect'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:139: 
missing file link 'dbGetQuery'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:140: 
missing file link 'dbClearResult'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:147: 
missing file link 'dbRollback'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:154: 
missing file link 'dbListFields'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:155: 
missing file link 'dbListConnections'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:156: 
missing file link 'dbListResults'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:157: 
missing file link 'dbGetException'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:158: 
missing file link 'dbGetStatement'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:159: 
missing file link 'dbHasCompleted'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:160: 
missing file link 'dbGetRowCount'
Rd warning: /tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/Oracle.Rd:161: 
missing file link 'dbGetRowsAffected'
     S4R                                     html
     dbCallProc-methods                      html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbCallProc-methods.Rd:30: 
missing file link 'dbGetQuery'
     dbCommit-methods                        html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbCommit-methods.Rd:31: 
missing file link 'dbGetQuery'
     dbConnect-methods                       html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbConnect-methods.Rd:51: 
missing file link 'dbGetQuery'
     dbDataType-methods                      html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbDataType-methods.Rd:32: 
missing file link 'isSQLKeyword'
     dbDriver-methods                        html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbDriver-methods.Rd:38: 
missing file link 'dbGetQuery'
     dbGetInfo-methods                       html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbGetInfo-methods.Rd:47: 
missing file link 'dbGetQuery'
     dbListTables-methods                    html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbListTables-methods.Rd:36: missing 
file link 'dbColumnInfo'
     dbObjectId-class                        html
     dbPrepareStatement-methods              html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:86: 
missing file link 'dbClearResult'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:87: 
missing file link 'dbGetStatement'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:88: 
missing file link 'dbGetRowsAffected'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbPrepareStatement-methods.Rd:109: 
missing file link 'dbGetQuery'
     dbReadTable-methods                     html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbReadTable-methods.Rd:80: 
missing file link 'dbGetQuery'
     dbSendQuery-methods                     html
     dbSetDataMappings-methods               html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/dbSetDataMappings-methods.Rd:34: 
missing file link 'dbColumnInfo'
     fetch-methods                           html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/fetch-methods.Rd:44: 
missing file link 'dbGetQuery'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/fetch-methods.Rd:45: 
missing file link 'dbClearResult'
     isIdCurrent                             html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/isIdCurrent.Rd:34: missing 
file link 'dbGetQuery'
     make.db.names-methods                   html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:67: 
missing file link 'dbWriteTable'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:68: 
missing file link 'dbExistsTable'
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/make.db.names-methods.Rd:69: 
missing file link 'dbRemoveTable'
     oraParseConParams                       html
     oraSupport                              html
     safe.write                              html
Rd warning: 
/tmp/Rtmp50pvF6/R.INSTALL6324d87b/ROracle/man/safe.write.Rd:21: missing 
file link 'oraWriteTable'
     summary-methods                         html
** building package indices ...
** testing if installed package can be loaded
Error in dyn.load(file, DLLpath = DLLpath, ...) :
   unable to load shared object 
'/usr/lib64/R/library/ROracle/libs/ROracle.so':
   /usr/lib64/R/library/ROracle/libs/ROracle.so: undefined symbol: sqlprc
Error: loading failed
Execution halted
ERROR: loading failed
* removing '/usr/lib64/R/library/ROracle'

As mentioned, I've been through the R forums for answers, but still get 
this error.

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.

Thanks in advance,

Stuart Uren
Database Administrator
Clinical Trials Research Unit
The University of Auckland
Auckland, New Zealand


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20111221/5921f0b1/attachment.html>

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: bug_12691067.txt
URL: <https://stat.ethz.ch/pipermail/r-sig-db/attachments/20111221/5921f0b1/attachment.txt>

More information about the R-sig-DB mailing list