[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 03:37:22 CET 2011
Ahh,... I just unzipped / untarred the ROracle_0.5-12.tar.gz file, and
read the inst/README.Oracle9 doc which suggests a fix to this error:
Workaround
----------
Add the libsqlplus (-lsqplus) library during ROracle configuration.
Starting with version 0.5-4, you may specify the --enable-extralibs
configuration argument:
R CMD INSTALL--configure-args='--enable-extralibs' ROracle_0.5-4.tar.gz
So,... I have tried this (substituting 0.5-12 for 0.5-4) and ROracle has
now installed without errors.
I can now load the ROracle library, connect and run a query.
Maybe someone could comment on the RODBC, however, that doesn't matter
too much to me now that I have ROracle working.
I didn't think of looking in the .gz file, mainly because R
unzips/untars/installs for me. Perhaps this item could be included in
this doc: http://cran.r-project.org/web/packages/ROracle/INSTALL
Regards
Stuart.
On 21/12/11 14:27, Stuart Uren wrote:
> 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
>
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list