CODEWITHSUNDEEP
rdatabaseoracle-databasepackage
SRVFan
SRVFan

Reputation: 344

Connecting to an Oracle Database in R to run SQL Query

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!

Upvotes: 2

Views: 1993

Answers (1)

Christopher Jones
Christopher Jones

Reputation: 10496

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

Upvotes: 2

Related Questions