Skip to main content

Oracle setup

  • Maintained by: Oracle
  • Authors: Oracle
  • GitHub repo: oracle/dbt-oracle
  • PyPI package: dbt-oracle
  • Slack channel: #db-oracle
  • Supported dbt Core version: v1.2.1 and newer
  • dbt Cloud support: Not Supported
  • Minimum data platform version: Oracle 12c and higher

Installing dbt-oracle

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-oracle

For Oracle-specific configuration, please refer to Oracle configs.

Configure the Python driver mode

info

python-oracledb is the renamed, major release of Oracle's popular cx_Oracle interface

python-oracledb makes it optional to install the Oracle Client libraries. This driver supports 2 modes

  1. Thin mode (preferred) : Python process directly connects to the Oracle database. This mode does not need the Oracle Client libraries
  2. Thick mode : Python process links with the Oracle Client libraries. Some advanced Oracle database functionalities (for e.g. Advanced Queuing and Scrollable cursors) are currently available via Oracle Client libraries

You can configure the driver mode using the environment variable ORA_PYTHON_DRIVER_TYPE. Use the thin mode as it vastly simplifies installation.

Driver ModeOracle Client libraries required?Configuration
ThinNoORA_PYTHON_DRIVER_TYPE=thin
ThickYesORA_PYTHON_DRIVER_TYPE=thick
cx_oracle (old driver)YesORA_PYTHON_DRIVER_TYPE=cx

The default value of ORA_PYTHON_DRIVER_TYPE is cx

Deprecation Warning

Default value of ORA_PYTHON_DRIVER_TYPE will change to thin in future release of dbt-oracle because cx_oracle is deprecated

export ORA_PYTHON_DRIVER_TYPE=thin

Configure wallet for Oracle Autonomous Database (ADB-S) in Cloud

dbt can connect to Oracle Autonomous Database (ADB-S) in Oracle Cloud using either TLS (Transport Layer Security) or mutual TLS (mTLS). TLS and mTLS provide enhanced security for authentication and encryption. A database username and password is still required for dbt connections which can be configured as explained in the next section Connecting to Oracle Database.

With TLS, dbt can connect to Oracle ADB without using a wallet. Both Thin and Thick modes of the python-oracledb driver support TLS.

info

In Thick mode, dbt can connect through TLS only when using Oracle Client library versions 19.14 (or later) or 21.5 (or later).

Refer to Oracle documentation to connect to an ADB instance using TLS authentication and the blog post Easy wallet-less connections to Oracle Autonomous Databases in Python to enable TLS for your Oracle ADB instance.

Connecting to Oracle Database

Define the following mandatory parameters as environment variables and refer them in the connection profile using env_var jinja function. Optionally, you can also define these directly in the profiles.yml file, but this is not recommended

export DBT_ORACLE_USER=<username>
export DBT_ORACLE_PASSWORD=***
export DBT_ORACLE_SCHEMA=<username>

Starting with dbt-oracle==1.0.2, it is optional to set the database name

export DBT_ORACLE_DATABASE=example_db2022adb

If database name is not set, adapter will retrieve it using the following query.

SELECT SYS_CONTEXT('userenv', 'DB_NAME') FROM DUAL

An Oracle connection profile for dbt can be set using any one of the following methods

The tnsnames.ora file is a configuration file that contains network service names mapped to connect descriptors. The directory location of tnsnames.ora file can be specified using TNS_ADMIN environment variable

tnsnames.ora
db2022adb_high = (description =
(retry_count=20)(retry_delay=3)
(address=(protocol=tcps)
(port=1522)
(host=adb.example.oraclecloud.com))
(connect_data=(service_name=example_high.adb.oraclecloud.com))
(security=(ssl_server_cert_dn="CN=adb.example.oraclecloud.com,
OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

The TNS alias db2022adb_high can be defined as environment variable and referred in profiles.yml

export DBT_ORACLE_TNS_NAME=db2022adb_high
~/.dbt/profiles.yml
dbt_test:
target: dev
outputs:
dev:
type: oracle
user: "{{ env_var('DBT_ORACLE_USER') }}"
pass: "{{ env_var('DBT_ORACLE_PASSWORD') }}"
database: "{{ env_var('DBT_ORACLE_DATABASE') }}"
tns_name: "{{ env_var('DBT_ORACLE_TNS_NAME') }}"
schema: "{{ env_var('DBT_ORACLE_SCHEMA') }}"
threads: 4

Supported Features

  • Table materialization
  • View materialization
  • Incremental materialization
  • Seeds
  • Data sources
  • Singular tests
  • Generic tests; Not null, Unique, Accepted values and Relationships
  • Operations
  • Analyses
  • Exposures
  • Document generation
  • Serve project documentation as a website
  • Python Models (from dbt-oracle version 1.5.1)
  • All dbt commands are supported

Not Supported features

  • Ephemeral materialization
0