Moving from RJDBC to odbc

[grrrck]

I'm trying to migrate some inherited scripts that use RJDBC to connect to an Oracle database to use the odbc package instead. I've reviewed the https://db.rstudio.com docs and tried a number of configurations but haven't been able to connect. I'm in uncharted territory within my org, so haven't been able to get much help internally.

Essentially, all I have is RJDBC code (like below) that successfully connects me to the database. How can I use this information, or information that I can query from the DB, to set up or diagnose my odbc connection? Or, more generally, what steps do I need to take to get from here to odbc?

library(RJDBC)
drv <- JDBC("oracle.jdbc.OracleDriver", classPath="instantclient_12_2/ojdbc8.jar")
con <- dbConnect(drv,"jdbc:oracle:thin:@server-ip:1521:database", 
                 rstudioapi::askForSecret("Username"), 
                 rstudioapi::askForSecret("Database Password"))

[cole]

This is a fantastic question and one that I think others will have helpful input on! In your connection string, you specify the JDBC driver. Do you have an odbc driver (usually a .so file) that you can use to facilitate the ODBC connection? Oracle likely provides one, or if you are an RStudio Professional customer, RStudio provides an Oracle ODBC driver for free use with our Professional products.

[grrrck]

I've been ollowing the instructions at https://db.rstudio.com/databases/oracle/

Oracle - Please refer to Oracle’s website for instructions on how to download and setup their official driver: Oracle ODBC driver page

From there I wasn't sure which driver I should use. I downloaded the Oracle Instant Client "Base" version (which is also where ojdbc8.jar came from). This version contains the following files ... I'm on MacOS, hence .dylib instead of .so files

Instant Client Base Files

libclntsh.dylib
libclntsh.dylib.12.1
libclntshcore.dylib.12.1
libnnz12.dylib
libocci.dylib
libocci.dylib.12.1
libociei.dylib
libocijdbc12.dylib
libons.dylib
liboramysql12.dylib
ojdbc8.jar

I just reviewed the list of Instant Client download options and noticed the "ODBC package" option, which includes libsqora.dylib.12.1.

[davidski]

The Oracle driver process is unfortunately complicated. My process for this on MacOS, pattered after the RStudio site is at https://github.com/davidski/database_connections

Hope this helps!

[cole]

Did @davidski's link help out? Make sure to select a "Solution" if/when your issue is resolved so we (or future visitors) know whether your question was resolved. If you're still having trouble, do you mind providing more detail about what you have tried? I second the feeling that the Oracle driver process is pretty complicated (and annoying). Specifying the appropriate driver and having the client installed / configured should hopefully allow the connection to succeed!

[grrrck]

Thanks @davidski for the link, it's definitely a great start. But unfortunately, it hasn't worked for me.

Setting aside my worry that I'm derailing my own thread into a Mac-specific workflow, from my extensive Googling, it seems that we also need to handle the @rpath issue, which is that the libsqora.dylib uses @rpath to locate dependent libraries, but R apparently doesn't follow these paths . This RStudio community post gives some hints to use install_name_tool to change the relative paths to specific locations.

I've followed both @davidski's recommendation and @seanw's answer in the linked post. I've also played with DYLD_LIBRARY_PATH and have made sure that /usr/local/lib is in that library path. Currently, trying to connect to the DB via odbc inside RStudio causes an immediate "R Session Aborted" fatal error that crashes RStudio (v. 1.2.1114, Build 1122 (2e0f7658)). Attempting to connect via a plain R console session gives me the following error that I suppose brings me back full circle to my original question.

Error: nanodbc/nanodbc.cpp:950: HY000: [Oracle][ODBC][Ora]ORA-12162: TNS:net service name is incorrectly specified

[cole]

This is terrifying. I feel your pain! Is there any chance you could get support from Oracle for how to debug this shenanigans? I know I am certainly out of my depth.

[Blair09M]

Sorry to hear about the hassle @grrrck! I've been trying to follow along on my end to see if I can get things properly configured with Oracle and I also haven't had much luck. If I discover the happy path I'll make sure I post an update here.

[cderv]

@grrrck I have been using ORACLE odbc connection for linux centos 7 server. I have an ansible playbook for that at work. I do not know if it could help for MacOS. Are you interested about the step we use on centos server to connect to oracle through odbc or will it be different any way (i never used MacOs) ?

[grrrck]

Thanks @Blair09M -- likewise I'll let you know if I ever get this worked out.

@cderv - I do think that would be super helpful. At the very least, I can use it to build an odbc-enabled Docker container and maybe get out of MacOS dependency config hell.