ROracle and dplyr

tidyverse
,
1

Do not seem to be playing well.

Has anyone gotten this to work correctly?

The experience below has given me trust issues, :cry:.

Getting ROracle in the first place was difficult, spent a lot of time trying to make it work. Upon calling support, the representative described downloading and installing from CRAN as an 'effort in futility'. He sent a precompiled version that worked like a charm...

The sources below note that you are aware of the problem.

2

Hi, is ROracle a requisite for your deployment, or would using the odbc package + an ODBC driver be an acceptable avenue? That's what we basically recommend in the Oracle page on db.rstudio.com that you posted.

3

No. I was thinking of ROracle as being the database specific and therefore 'best' option to instantiate a connection.

I was previously having issues using the bind feature with the obdc package. However, that was before the latest release. I'll go back and give it a try.

if all you have is a hammer, everything looks like a nail

Thank you for the response!

4

I use ROracle with dplyr every day. I compiled ROracle from source against Oracle Instant Client 12.2. I agree it was not straightforward. What specific issues are you having? On Windows, the process I followed was roughly

  1. download and unzip the the Instant Client Package - Basic, e.g., to c:\oracle\instantclient_12_2
  2. download and unzip the Instant Client Package - SDK, place the sdk folder wherever you unzipped the Basic package, e.g., c:\oracle\instantclient_12_2\sdk
  3. set OCI_LIB64 environment variable to point to Instant client (c:\oracle\instantclient_12_2)
  4. set OCI_INC environment variable to point to the SDK include files (c:\oracle\instantclient_12_2\sdk\include)
  5. add the location of oci.dll to the PATH environment variable (c:\oracle\instantclient_12_2)
  6. install ROracle: install.packages("ROracle", type = "source")

Does that work for you?

2 Likes
5

Thanks for the help.

I have managed to install and use ROracle. Unfortunately, I do not see any real benefits compared to RODBC.

Neither package allows me to use the dplyr::tbl() function to do operations inside of the database. If you are able to create a connection in this way, please let me know.

My work flow is fine right now.

  1. Create a query string.
  2. Send the query.
  3. Interpolate by hand.
  4. Fetch the results.

Unfortunately, in larger corporations, there tend to be lots of tables with lots of columns. Schemas (visual aides/data definitions) can get left in the dust and scattered to the wind.

6

Right, for ROracle you need to tell dbplyr that this is an Oracle connection, you can add the following lines at the top of your script:

sql_translate_env.OraConnection <- dbplyr:::sql_translate_env.Oracle
sql_select.OraConnection <- dbplyr:::sql_select.Oracle
sql_subquery.OraConnection <- dbplyr:::sql_subquery.Oracle

For a full walk-through, check out this article: https://technology.amis.nl/2017/08/23/r-and-the-oracle-database-using-dplyr-dbplyr-with-roracle-on-windows-10/

7

FWIW, I abandoned RODBC with Oracle because RODBC will ignore the type declaration in result sets of CHAR and VARCHAR and just apply type.convert on them. This means that if you have a db column that is a VARCHAR with possible values 01, A1 and 02, your results in R might either be integer or character depending on what combination of values your query returns. (type.convert turns 01 and 02 into integers.)

1 Like
8

I'm curious, can you say what problems you were having with binding parameterized queries with odbc? Or more generally, what kinds of use cases do you deal with regarding parameterized queries with Oracle?

I ask because I have spent a lot of time building up my own custom code for dealing with a lot of this, and I've always wanted use something less homegrown but I seem to have 1-2 very unique requirements that apparently only I have, so nobody seems to build tools that work quite right for me.

So I'm interested in what other people are doing and what problems they encounter, in general.

9

I am unable to use the DBI::dbBind function, nor am I able to use the DBI::dbListTables function. Funny enough, the ROracle::ListTables function doesn't work either. I swear at some point in time the DBI functions worked.

Anyways, I just do what I am told to interpolate by "hand"...https://db.rstudio.com/best-practices/run-queries-safely.

I am not really a data scientist. I am a CPA in the Finance department of an Insurance company. We work with Actuaries to forecast Incurred But Not Reported (IBNR) claims to set a statutory Reserve for future payments. Then we turn around and create current Financial numbers for internal reports.

My area works with a couple of different processing platforms for claims, premium, and membership. Think: lots of acquisitions, so little time. Therefore, we have a lot of information entering an already highly de-normalized Data Warehouse.

10

So, I tried to go back and work out the process with the odbc package.

I am operating on a Windows computer. When I used RODBC, I used a 'User DSN' that was connected to the perfect Driver through the TNS Service Name. I knew it was good because it had a drop down menu to connect and the 'Test Connection' push button told me so :). None of that has changed.

con <- RODBC::odbcConnect("RcsdBASE Files",
uid="xxxxx",
pwd="xxxxx",
believeNRows=FALSE)

Unfortunately, when using similar call to odbc, it did not work.

con <- DBI::dbConnect(odbc::odbc(),
Driver = "Oracle in OraHome112_64_home_1",
Host = "RcsdBASE Files",
SVC = "CSDPRO",
UID = "xxxxx",
PWD = "xxxxx",
Port = 1521)

I got this error: ORA-12560: TNS:protocol adapter error.

I tried this a few different ways....

  1. I used the argument names from ?DBI:dbConnect instead of those specified in the database book.
  2. I stripped out the arguments which used defaults to see if I would get lucky.
  3. Instead of the fields from the Windows 'ODBC Data Source Administrator', I used those from the Oracle 'About' information dropdown.

Needless to say, I just started throwing a bunch of stuff at the wall to see what would stick. I think I need some background knowledge. Any good reads on ODBC connections not sponsored by Microsoft or Oracle?

1 Like
11

I won't be able to help (don't know much about Windows ODBC stuff, sorry!) but I can offer lots of empathy. I've struggled multiple times to get odbc to connect to an Oracle db, but I've never gotten it to work. I know it's possible, because other people have done it. I hope you have better luck than me!

1 Like
12

Are you using the schema argument to ROracle::dbListTables? This argument is likely necessary if you are attempting to list objects in a schema different from the authenticated user. Note that this argument appears to be case-sensitive. So, if you are looking for tables in the myschema schema, you would likely need something like

ROracle::dbListTables(conn, schema = "MYSCHEMA")

Does that work?

1 Like
13

Is “RcsdBASE Files” a DSN or a Database name?

14

It is a DSN. It is the only argument value needed to connect via RODBC above.

15

Ok, then your DBI connection string should be:

con <- DBI::dbConnect(odbc::odbc(), 
“RcsdBASE Files”,
UID = “xxxxx”,
PWD = “xxxxx”)
1 Like
16

Thank you! Everything works as I was hoping. So cool!

17

That's great! I'm glad it worked for you!

18

Sorry for the late reply....Thanks for your help.

Databases are a little frustrating. I can never be sure if it is something small or if it is impossible, a la the answer to my odbc problem.... A wee bit embarrassing, but I'll live and learn.

Your answer was on the money. And, it also led me to the realization that dbListFields will work with ROracle::dbListFields(conn = xxxxx, schema = "xxxxx", name = "xxxxx").