Connect to Oracle DB instance using R

Question

I try to connect to an oracle DB using Rstudio, but I can't figure out how to do it. I have connected to the database with PowerBi, so I know it is accessible (from my pc/ip) and that the database works fine, however using R I cannot get this done. Prior to asking this question I have been searching the web for days, I'm not new to R or SQL, but I am to Oracle. I have tried various packages (DBI, odbc, RODBC etc.) but still no success. I have talked to the administrators of the DB, they said I should be able to connect using the following information which is provided to me;

Server: xxx.xxx.xx.xx

Port : xxxx

Instance : ORCL

User: myusername

Pwd : mypwd

When I connect to the DB using PowerBI, my server name is: server/instance, in combination with the uid and pwd the connection than works fine.

The closest I (think I) got, was using the 'odbcDriverConnect()' function. First I ran the following command to check which drivers are installed;

sort(unique(odbcListDrivers()[[1]]))

which gave as output

"Oracle in OraClient12Home1" "Oracle in OraClient12Home2" "SQL Server"

Other topics here on stackoverflow, suggested the following site to check which string to use in the function:

https://www.connectionstrings.com/oracle/

I couldn't find a specific one for my listed drivers, but I altered the one for the Oracle in OraClient11g_home1 since it seemed to be the best match, this string is as follows;

Driver={Oracle in OraClient11g_home1};Server=serverSID;Uid=myUsername; Pwd=myPassword;

So I replaced the Oracle in OraClient11g_home1 with my drivers, they all gave another error;

con <- odbcDriverConnect("Driver={Oracle in OraClient12Home1};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;")


Warning messages:
1: In odbcDriverConnect("Driver={Oracle in OraClient12Home1};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  [RODBC] ERROR: state IM003, code 160, message Het opgegeven stuurprogramma kan niet worden geladen vanwege een systeemfout  126: Kan opgegeven module niet vinden. (Oracle in OraClient12Home1, C:\Program Files\Oracle\product\12.2.0\client_1\SQORA32.DLL).
2: In odbcDriverConnect("Driver={Oracle in OraClient12Home1};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  ODBC connection failed

The error is in Dutch, stating that the driver cannot be found due to systemerror 126

the next driver gives another error;

con <- odbcDriverConnect("Driver={Oracle in OraClient12Home2};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;")

Warning messages:
1: In odbcDriverConnect("Driver={Oracle in OraClient12Home2};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  [RODBC] ERROR: state HY000, code 12560, message [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error
2: In odbcDriverConnect("Driver={Oracle in OraClient12Home2};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  ODBC connection failed

The last driver, is the only one who takes a few seconds, suggesting its actually trying to connect, but eventually also gives an (other) error;

con <- odbcDriverConnect("Driver={SQL Server};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;")

Warning messages:
1: In odbcDriverConnect("Driver={SQL Server};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]De SQL-server bestaat niet of de toegang tot de server is geweigerd.
2: In odbcDriverConnect("Driver={SQL Server};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :
  [RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("Driver={SQL Server};Server=xxx.xxx.xx.xx/orcl;Uid=xxx;Pwd=xxx;") :

Stating that the server doesn't exists or that the acces is denied. Once again, I know it exists and that I am granted access because it works on powerBi, so I am kind off lost right now. I have tried the server with and without the /orcl. If anyone has any clue on what I am missing that would be greatly appreciated!

Kind regards

What is your environment ?.Did you install Oracle ODBC drivers 64 bit? — Suresh
– Suresh, Commented Apr 29, 2021 at 12:25
Yes, both windows and R run in 64 bit. Someone just commented here, his answer is deleted, I dont know why, but I think he gave me the solution. I tried; odbcDriverConnect("Driver={Oracle in OraClient12Home2};Dbq= myServerIP:myPort/orcl;Uid=myUsername;Pwd=myPassword;") I now got no errors and see a value for 'con' in environment with value 'RODBC int 1' still not sure on how to go forward, but it seems (I think) I have established a connection — BillyBouw
– BillyBouw, Commented Apr 29, 2021 at 12:50
Yes that was me.I deleted that answer because I was not sure of your environment.Did you get the message connection successful?.When you see that message copy and paste into console which will show you schemas in database — Suresh
– Suresh, Commented Apr 29, 2021 at 12:59
I did not get that message, only an object in my environment as described in my previous comment, when I coppy the object (con) in my console I get this, not sure what it is, the tables I expect are named diffrently but they could be underllying or something (?) RODBC Connection 1 Details: case=nochange DRIVER={Oracle in OraClient12Home2} SERVER=xxx.xxx.xx.xx:1521/orcl UID=*** PWD=****** DBQ=xxx.xxx.xx.xx:1521/orcl DBA=W APA=T EXC=F XSM=Default FEN=T QTO=T FRC=10 FDL=10 LOB=T RST=T BTD=F BNF=F BAM=IfAllSuccessful NUM=NLS DPM=F — BillyBouw
– BillyBouw, Commented Apr 29, 2021 at 13:08
the list goes on a little more, too much chars to print here — BillyBouw
– BillyBouw, Commented Apr 29, 2021 at 13:08

Suresh · Accepted Answer · 2021-04-29 13:08:51Z

2

With instant client and ipaddress of server

con <- odbcDriverConnect("Driver={Oracle in OraClient12Home2};Dbq=server_ipaddress:1521/orcl;Uid=xxx;Pwd=xxx;")

or if you can ping server

 con <- odbcDriverConnect("Driver={Oracle in OraClient12Home2};Dbq=server:1521/orcl;Uid=xxx;Pwd=xxx;")

Other option if you have tnsnames.ora in instant client home and set tns_admin variable you can use service name in tnsnames.ora file

con <- odbcDriverConnect("Driver={Oracle in OraClient12Home2};Dbq=orcl;Uid=xxx;Pwd=xxx;")

P.S:-I never worked on R just downloaded R and R studio

edited Apr 29, 2021 at 13:08

answered Apr 29, 2021 at 12:06

Suresh

4423 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BillyBouw Over a year ago

Thanks allot man you really made my day, after establishing it in de 'connections' sections I got everything I needed :)

Suresh Over a year ago

You're welcome I was about to post another screen shot for connections tab.Glad it worked out in the end.

Collectives™ on Stack Overflow

Connect to Oracle DB instance using R

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related