Show ROracle connection with OCI (*not ODBC) in connections pane

Posit Professional & Hosted Products
, , ,
1

I've been connecting successfully to our Oracle server using ROracle/DBI for some time, and now I wonder if there is a way to show/manage/use this connection in the connections pane. Our server uses Oracle's OCI and currently does not have ODBC available. Also, this server and the RStudio Server Pro installation I'm using when I connect to it are both not connected to the internet except when our IT staff are performing system updates.

Supposing I have already set strings for host, port, svc, the connection I've been using goes something like this, adapted from the ROracle docs:

library(ROracle)

connect_string <- paste0(
  '(DESCRIPTION=',
  '(ADDRESS=(PROTOCOL=tcps)(HOST=', host, ')(PORT=', port, '))',
  '(CONNECT_DATA=(SERVICE_NAME=', svc, ')))')

drv <- dbDriver('Oracle')

con <- tryCatch(
  dbConnect(drv,
            username = rstudioapi::askForPassword('Enter username: '), 
            password = rstudioapi::askForPassword('Enter your Oracle database password: '), 
            dbname = connect_string),
  error = function(cond) message(cond),
  warning = function(cond) message(cond)
)

Where con is of class OraConnection and drv is OraDriver, implementations of DBI provided by the ROracle package. From here I can send SQL statements with dbSendQuery(), fetch(), etc.

Is there some way I can get this set up in the connections panel?

Bonus: Will dplyr/dbplyr support this?

Thanks!
Brian

1 Like
2

Hi @brianstamper , the way RStudio's Connections pane integrate is via what we call a "Contract". The contract resides in the connecting package, and currently, only odbc and sparklyr have a contract developed. One option is to reach out to the RORACLE developers so they can build the contract in their package: http://db.rstudio.com/advanced/contract/. Another option is to hold off until we are able to add a generic implementation to the DBI package, which may provide most of the interactions you need, I currently don't have a timeline for that.

As far as your bonus question, yes, the latest version of dbplyr recognizes the ora connection and applies the Oracle translation automatically.

1 Like
3

Oh! I did not realize I could have been using dplyr like that already, that is a nice discovery. I had confused what is supported by the connections pane and what is supported by dbplyr.

I'll look into contacting the ROracle devs and pointing them here.

Thank you.

1 Like
4

BTW I ran into a bug with db_explain(), looks like some of the syntax will need custom methods for Oracle. Posted an issue here: https://github.com/tidyverse/dplyr/issues/3471

5

Dear Brian, did you succeed in contacting ROracle developers/maintainers?
Do you have a reference?
Maybe, I could add myself to the list of interested parties for ROracle to support a "contract"...

Thank you in advance for considering my request
Regards
Enrico