dbt-oracle-adapter

This plugin ports dbt functionality to Oracle.

This is only tested against Oracle XE-18c and dbt 0.19

Installation

pip install dbt-oracledb 🍰

Supported features

• Table materialization
• View materialization
• Ephemeral materialization
• Tests
• Documentation
• Sources
• Seeds
• Snapshots
• Incremental materialization

Example Profile

your_profile_name:
  target: dev
  outputs:
    dev:
      type: oracle
      username: [username/schema]
      password: [1234]
      host: [localhost]
      database: [xepdb1]
      schema: [username/schema]

      # optional
      port: [port]  # default 1521

Development

Setting up

The package was developed with poetry, so start by installing that in your system.

cd into the root of the cloned repo and run poetry install to install the package in editable mode with all dev dependencies.

We need an Oracle database running to do anything with the adapter. Install docker and start a test database with:

docker run -d -p 1521:1521 silverax/dbt-oracle-adapter:oracle-18.4.0-xe

This will take some time to finish setting up (~10 minutes), you can check the progress with docker ps and wait for the status to go from starting to healthy.

NOTE:

That's an Oracle 18.4.0 XE database with user SYSTEM and password oracle. The image is a very slightly modified version taken from the original Oracle repo I've built and uploaded to docker-hub

Run the tests with pytest specs/oracle.dbtspec

This is using the dbt-adapter-tests plugin. Most sequences are passing, and failing ones are commented out.

Of the failing ones. The incremental materialization is not implemented. While the rest I suspect are failing because of the plugin is hardcoding some queries. Running that capability on test_project works fine.

TODO / Adapter issues

• The alter_column_type macro is not implemented
• varchar2 columns are created with hardcoded precisions
• Oracle does not distinguish between schemas and users, so any new schemas are created with the 1234 password. I probably need to add more options to schema creation and set the default password as the same as the logged in user.
• Connection to db is passing username/password/database parameters directly to cx_oracle connect. I think there should be another optional parameter easy_connect_string that supersedes any others.
• There's some issues leftover with quoting. So table searching is calling upper on any inputs. So to be safe try disabling quoting and don't mix uppercase with lowercase in the same table/column name.