Oracle

Driver Options

Connections to Oracle require the Oracle Instant Client to be installed. The minimum required version of the Oracle Instant Client is documented by each driver and may differ between the Professional Drivers and the Oracle ODBC Driver.

  • Oracle - Please refer to Oracle’s website for instructions on how to download and setup their official driver: Oracle ODBC driver page

  • Posit Professional Drivers - Workbench, RStudio Desktop Pro, Connect, or Shiny Server Pro users can download and use Posit Professional Drivers at no additional charge. These drivers include an ODBC connector for Oracle databases. Posit delivers standards-based, supported, professional ODBC drivers. Use Posit Professional Drivers when you run R or Shiny with your production systems. See the Posit Professional Drivers for more information.

Package Options

There are packages that either connect via ODBC but do not provide support for DBI, or offer DBI support but connect via JDBC. The odbc package, in combination with a driver, satisfies both requirements.

Another package that provides both ODBC connectivity and DBI support is ROracle. The current version of dbplyr in CRAN does not yet fully support a connection coming from ROracle, but we are working on it.

Connection Settings

There are six settings needed to make a connection:

  • Driver - See the Drivers section for more setup information
  • Host - A network path to the database server
  • SVC - The name of the schema
  • UID - The user’s network ID or server local account
  • PWD - The account’s password
  • Port - Should be set to 1521

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = "[your driver's name]",
                      Host   = "[your server's path]",
                      SVC    = "[your schema's name]",
                      UID    = rstudioapi::askForPassword("Database user"),
                      PWD    = rstudioapi::askForPassword("Database password"),
                      Port   = 1521)

dplyr Translation

Known Issues

This section will be updated as new issues are found, and when fixes are applied in the development version of the dbplyr package. The issue will be fully removed when the fix is part of the package’s version in CRAN.

  • RJDBC support - Even though it is not considered an issue, we have found a workaround. The approach is to point the current JDBC connection to the Oracle translation inside dbplyr:
sql_translation.JDBCConnection <- dbplyr:::sql_translation.Oracle
sql_select.JDBCConnection <- dbplyr:::sql_query_select.Oracle
sql_subquery.JDBCConnection <- dbplyr:::sql_query_wrap.Oracle

Please refer to the Issues section in dplyr to find out the latest regarding bugs and resolutions.

Back to top