Skip to content

Unable to get RODBC or ROracle to work on Linux

4 messages · Stuart Uren, Dirk Eddelbuettel, MacQueen, Don

Original
Stuart Uren
# 
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 at predict: export LD_LIBRARY_PATH=/opt/oracle-instantclient/P
root at predict: export ORACLE_HOME=/u04/app/oracle/product/11.2.0.3/dbhome_1
root at predict: export ORACLE_SID=PREDICT
root at 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 at predict: export 
TNS_ADMIN=/u04/app/oracle/product/11.2.0.3/dbhome_1/network/admin

root at 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 at 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 at r-project.org/msg121901.html
... and the ROracle install package at 
http://cran.r-project.org/web/packages/ROracle/INSTALL

root at 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>
Stuart Uren
# 
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:

  
  


  


      

    

    

      
      

        


  

        

      Dirk Eddelbuettel
    

    

      
      #
    

  


  

      
On 21 December 2011 at 15:37, Stuart Uren wrote:

        
| Maybe someone could comment on the RODBC, however, that doesn't matter 
| too much to me now that I have ROracle working.

It's been a while, but I used to use ROBDC before we had dedicated drivers
for PostgreSQL. 

It seemed like you did the hard part: getting ODBC connectivity from your
box.  Once you have the basic "ini" files set, and isql works, RODBC should
follow.  

I have been maintaining RODBC for Debian since 2003. For those builds, we
have switched between iODBC and unixodbc, so either should work.  As you have
a working installation, maybe all it takes is to make sure the right header
files get picked up.  But in any event, ROracle may be more efficient.

Dirk

  
    

      
      
    

  


  


      

    

    
1 day later

    

      
      

        


  

        

      MacQueen, Don
    

    

      
      #
    

  


  

      
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 at ctru.auckland.ac.nz> wrote: