oracle

Connecting R and Oracle Database with ADBC

Instructions

Tip

If you already have an Oracle Database instance running, skip the steps to set up and clean up Oracle Database.

Prerequisites

1. Install R

2. Install dbc

3. Install R packages adbcdrivermanager, arrow, and tibble:

   install.packages(c("adbcdrivermanager", "arrow", "tibble"))

Set up Oracle Database

1. Install Docker

2. Start a Oracle Database instance:

   docker run -d --rm --name oracle-db -p 1521:1521 -e ORACLE_PWD=password container-registry.oracle.com/database/free:latest

3. Wait about a minute for the database to initialize.

Connect to Oracle Database

1. The ADBC driver for Oracle is available from Columnar's private driver registry. Create a Columnar Console account and activate a 14-day free trial. Then authenticate to the registry:

   dbc auth login

2. Install the ADBC driver for Oracle:

   dbc install oracle

3. Install the Oracle Instant Client libraries.

4. Set LD_LIBRARY_PATH (Linux), DYLD_LIBRARY_PATH (macOS), or PATH (Windows) to make sure the Oracle Instant Client libraries are discoverable by your application.

5. Customize the R script main.R as needed

   • Change the connection arguments in adbc_database_init()
     • Change uri as needed, using query parameters to add more connection arguments. Format uri according to the the following syntax: oracle://[user[:password]@]host[:port][/serviceName][?param1=value1&param2=value2], or keep it as is.
   • Change the SQL SELECT statement in read_adbc(), or keep it as is.

6. Run the R script:

   Rscript main.R

Clean up

Stop the Docker container running Oracle Database:

docker stop oracle-db