Connecting to an Oracle Database in R to run SQL Query

Question

I am trying to connect to an Oracle Database in R. In Python, I simply use the cx_Oracle package, and can easily connect and run a query using the following code:

my_dsn = cx_Oracle.makedsn("host",port,sid="sid")
connection = cx_Oracle.connect(user="user", password="password", dsn=my_dsn)
cursor = connection.cursor()

querystring = "SQL query"
cursor.execute(querystring)

Is there an equivalent package in R to use? If so, what is it, and how do I set up my bearings to connect to the Oracle database and run a query--saving the result into a data.frame? I need to be able to construct a DSN, as I have done in the code above, too. Thank you!

This question is unfortunately specifically off-topic, so likely to be closed. A quick search found entries for ROracle on both CRAN and Oracle's website. You might also consider one of the ODBC variants, RODBC or odbc. — r2evans
– r2evans, Commented Apr 30, 2018 at 22:33
Thank you for the package names! Unfortunately, I do not see in the documentation how to create a DSN; it looks like both packages want you to already know what the DSN is. Is there a way to create a DSN, as I have in the code posted in my question? — SRVFan
– SRVFan, Commented Apr 30, 2018 at 22:38
DSN creation will be totally separate from the R package itself. Some of thise packages however deacribe how to set up a dsn in their documentation. — joran
– joran, Commented Apr 30, 2018 at 23:07

Christopher Jones · Accepted Answer · 2018-05-01 07:15:29Z

2

The ROracle equivalent of Python cx_Oracle's:

my_dsn = cx_Oracle.makedsn("host",port,sid="sid")
connection = cx_Oracle.connect(user="user", password="password", dsn=my_dsn)

is:

connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
  "(CONNECT_DATA=(SID=", sid, ")))", sep = "")
con <- dbConnect(drv, username = "user", password = "password", 
                 dbname = connect.string)

You can use any of Oracle's standard (non JDBC) connection strings. One reference is https://oracle.github.io/node-oracledb/doc/api.html#connectionstrings

answered May 1, 2018 at 7:15

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

1 Comment

Seymour Over a year ago

Though he was asking whether it was possible to perform a query like querystring of Python. Do you confirm that this is not possible in ROracle? Thank you

Collectives™ on Stack Overflow

Connecting to an Oracle Database in R to run SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related