Skip to content

Oracle Database connector

Oracle Database is reached over OCI through ODPI-C. Compiled SQL uses Oracle's own forms — FETCH FIRST for limits, TRUNC(x, 'FMT') for time-grain truncation, quoted interval literals (INTERVAL '1' MONTH), and table aliases without AS.

There is no build-time Oracle dependency: ODPI-C compiles with the crate, and the free Oracle Instant Client is loaded at first connect. Only machines that actually reach Oracle need it installed. Connections are pooled and opened lazily. Gated by exec-oracle.

Connection profile

datasources:
  oracle:
    type: oracle
    host: db.internal
    port: 1521
    username: main
    password: ${ORACLE_PASSWORD}
    database: ORCLPDB1        # the service name

Or as an EZConnect URL:

datasources:
  oracle:
    type: oracle
    uri: oracle://main:${ORACLE_PASSWORD}@db.internal:1521/ORCLPDB1

Parameters

Key Type Required Default Notes
type string yes — oracle
host string yes* — *Unless uri: is given.
port int no listener default Optional: EZConnect accepts //host/service.
username string yes* — Required in the discrete form.
password string yes* — Required in the discrete form. Prefer ${VAR}.
database string yes* — The service name, not a schema. Required in the discrete form.
uri string no — oracle://user:pass@host:port/service. A @ inside the password is handled — the host is split from the last @.
default bool no false See connection profiles.

Parsed and ignored on this connector: schema (there is no CURRENT_SCHEMA pin — see below), sslmode, sslrootcert, arrow_flight_port, catalog, account, role, warehouse, compat_mode.

Every connection is opened with autocommit on and four ALTER SESSION statements: TIME_ZONE = 'UTC' for the zone-less value contract, and ISO NLS_DATE_FORMAT / NLS_TIMESTAMP_FORMAT — without them Oracle's DD-MON-RR default would reject the compiled CAST('2024-01-01' AS DATE) literals.

Schema resolution

In Oracle a schema is a user. Compiled SQL references <schema>.<table> as written in the model, so the account you log in as must own those objects or be able to see them. There is no schema: key to redirect this: create the login user with the name the model uses, or grant access and use a synonym.

One semantic difference Dosi does not normalize: Oracle treats '' as NULL.

Limitations

None declared for SQL generation on Oracle 23ai, which ships a native 3-argument DATEDIFF — the case every other engine declares as a gap runs here.

Two version caveats: 19c and 21c have no DATEDIFF, so a metric using it is not portable to those releases, and multi-row INSERT, DROP TABLE IF EXISTS and a native BOOLEAN type — used by seeding paths — are 23ai features.

The runtime dependency is the practical limitation: without Instant Client on the machine, this connector cannot connect at all.

Verify the connection

$ dosi query --model model.yaml \
    --metrics revenue --group-by orders.status --execute --connection oracle

Troubleshooting

Message Cause and fix
A config error mentioning DPI-1047, with the hint that connecting to Oracle needs the free Oracle Instant Client installed and on the loader path Install it (dnf install oracle-instantclient-basic) or unzip the package and point LD_LIBRARY_PATH at it. The library architecture must match the binary.
connection "x": oracle needs database: (the service name) database: is the service name (for example FREEPDB1), not a schema.
connection "x": oracle needs username: / … needs password: Both are mandatory in the discrete form.
connection "x": expected an oracle:// url The uri: must use the oracle:// scheme.
connection "x": oracle url needs user:pass@host/service The URL is missing the credentials or the service part.
server rejected SQL (ORA-NNNNN): <message> Oracle refused the compiled statement; ORA-00942 usually means the model's schema is not the login user.
this build has no oracle executor (feature "exec-oracle" not enabled) Published binaries include it; a hand-built one needs --features exec-oracle.

Reference