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

MacQueen, Don m@cqueen1 @end|ng |rom ||n|@gov
Thu Dec 22 19:24:23 CET 2011


Perhaps not relevant now that you have ROracle running, but RJDBC is
another option that I have had success with.

Especially with respect running the same queries from both RHEL and
Macintosh clients.

-Don

--
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062





On 12/20/11 6:37 PM, "Stuart Uren" <s.uren using ctru.auckland.ac.nz> wrote:

>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/lo
>>cal/lib64:/usr/lib/jvm/jre/lib/amd64/server:/usr/lib/jvm/jre/lib/amd64:/u
>>sr/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/a
>>md64:/usr/lib/jvm/java/lib/amd64:/usr/java/packages/lib/amd64:/lib:/usr/l
>>ib")
>>
>> 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.R
>>d: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]]
>
>_______________________________________________
>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




More information about the R-sig-DB mailing list