Skip to content

Oracle Database connector

Oracle Database is reached over OCI through ODPI-C. Dosi compiles native Oracle SQL, including FETCH FIRST for limits, TRUNC(x, 'FMT') for time-grain truncation, quoted interval literals such as INTERVAL '1' MONTH, and table aliases without AS.

ODPI-C is compiled into Dosi's Oracle executor, but Oracle's native client library is not bundled. The free Oracle Instant Client is loaded only when Dosi first opens an Oracle connection. Compiling SQL or running a dry run therefore does not prove that the runtime can execute it.

Prerequisites

Before executing an Oracle query, the runtime machine needs:

  • the Oracle Instant Client Basic Package (Basic Light is also suitable if its reduced character-set support is sufficient); and
  • an Oracle account that can connect to the target service and read the objects referenced by the semantic model.

Dosi does not need the Instant Client SDK, SQL*Plus, Tools, JDBC, or ODBC packages. Match the package to the operating system and CPU architecture of the Dosi binary, and to Python as well when using dosi-engine from Python.

Runtime platform Instant Client package Deployment notes
Ubuntu 20.04 or 22.04; Debian 10, 11, or 12 Linux ZIP for linux.x64 or linux.arm64 Install libaio1; current 23ai packages require glibc 2.28 or newer.
Ubuntu 24.04; Debian 13 Linux ZIP for linux.x64 or linux.arm64 Install libaio1t64; a compatibility library name may be needed as described in Troubleshooting.
RHEL 8 or 9 Matching EL8/EL9 RPM, or Linux ZIP The RPM is the simplest system-wide installation; ZIP also requires libaio.
macOS with an ARM64 runtime macos.arm64 Basic DMG Native Apple Silicon Dosi or Python.
macOS with an x86-64 runtime macos.x64 Basic DMG Intel or Rosetta Dosi/Python. Oracle's newest Intel package is 19.16 and is listed only through macOS Monterey; treat this as a legacy platform.

This table describes practical Dosi deployment paths, not Oracle operating system certification. Cross-architecture combinations, 32-bit clients, and Alpine Linux or another musl-based distribution are not supported. The availability of an Instant Client package also does not imply that a prebuilt Dosi artifact is published for that platform.

Oracle Client 23ai can connect to Oracle Database 19c and later. That is a client/server interoperability statement only; it does not make SQL generated and tested against 23ai automatically compatible with every older database release. See Version compatibility and limitations.

Install Oracle Instant Client

The installer detects Linux or macOS, inspects the architecture of dosi (or Python when no Dosi executable is present), selects a pinned Basic package, verifies its SHA-256 checksum, installs native dependencies, and configures the loader. Copy the whole block into the shell that will run Dosi:

# Optional: inspect a specific runtime or select a universal-binary slice.
# export DOSI_RUNTIME=/path/to/python3
# export DOSI_ARCH=x86_64

oracle_installer="$(mktemp)"
curl -fsSL https://dosi.datus.ai/assets/install-oracle-instant-client.sh \
  -o "$oracle_installer" &&
  bash "$oracle_installer" &&
  eval "$(bash "$oracle_installer" --print-env)"
rm -f "$oracle_installer"
unset oracle_installer

--print-env is empty on Linux; on macOS the final eval exports the matching directory through DYLD_LIBRARY_PATH in the current shell. Set and export DOSI_RUNTIME before running the block to inspect a specific Dosi or Python executable. A universal non-Python executable is intentionally ambiguous: set DOSI_ARCH=x86_64 or DOSI_ARCH=arm64 to choose the slice that will run.

The installer source is available at install-oracle-instant-client.sh. It pins packages from Oracle's Linux x86-64, Linux ARM64, macOS ARM64, and macOS Intel download pages. Run it with --print-package to inspect the selected version, URL, checksum, and destination without installing anything.

Connection profile

Use either discrete connection fields:

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

or 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.

The shared profile format also accepts schema, sslmode, sslrootcert, arrow_flight_port, catalog, account, role, warehouse, and compat_mode, but the Oracle connector does not use them. In particular, schema: does not issue ALTER SESSION SET CURRENT_SCHEMA; see Schema resolution.

Verify the connection

The final check is a real query. Replace the model, metric, dimension, and connection name below, then run it in the same environment where Dosi will run:

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

Returned rows confirm that Instant Client loaded and that the network, credentials, service name, object access, and SQL all worked. DPI-1047 means the runtime still cannot load Instant Client; a network or ORA-... error means the client loaded and the remaining problem is on the connection or database side. A compile-only command or dry run is not a connection test.

Runtime behavior

Session initialization

Connections use autocommit and are initialized with four ALTER SESSION statements. Dosi pins TIME_ZONE = 'UTC' for the zone-less value contract and uses ISO NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT values. Without those NLS settings, Oracle's usual DD-MON-RR default can reject generated literals such as CAST('2024-01-01' AS DATE).

Schema resolution

In Oracle, a schema is a user. Compiled SQL preserves the model's <schema>.<table> references, so the login account must own those objects or have access to them. To use a different login, grant it access and expose the expected names through synonyms; the profile's schema: key does not redirect object resolution.

One semantic difference is intentionally not normalized: Oracle treats '' as NULL.

Version compatibility and limitations

The Oracle connector and its executable SQL corpus are currently verified against Oracle Database 23ai Free. This is narrower than a claim of complete Oracle support:

  • Client connectivity: Instant Client 23ai can connect to Oracle Database 19c and later, provided the server and network accept the connection.
  • Metric SQL: Oracle 19c and 21c do not provide 23ai's native three-argument DATEDIFF. A generated metric query that uses it is not portable to those releases without a dialect lowering or model change.
  • Test data loading: Dosi's Oracle fixture seeding uses multi-row INSERT, DROP TABLE IF EXISTS, and native BOOLEAN, which are 23ai features. This prevents that test setup from running unchanged on 19c or 21c, but does not by itself prevent ordinary read-only metric queries on those databases.

Until a dedicated corpus is run against 19c or 21c, treat support for those servers as query-dependent rather than formally verified. The required Instant Client is a separate runtime condition: no Oracle query can execute when its native library is absent or unloadable.

Troubleshooting

Message or symptom Cause and fix
A configuration error mentioning DPI-1047 ODPI-C could not load a compatible Instant Client. Follow Install Oracle Instant Client, confirm that its architecture matches Dosi, and check all native dependencies.
libaio.so.1 => not found on Ubuntu 24.04 or Debian 13 libaio1t64 may expose only libaio.so.1t64; add the compatibility link shown below and rerun ldconfig.
connection "x": oracle needs database: (the service name) database: is the service name, such as FREEPDB1, not a schema.
connection "x": oracle needs username: / … needs password: Both values 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 credentials or the service name.
server rejected SQL (ORA-NNNNN): <message> Oracle rejected the compiled statement. ORA-00942 usually indicates an object-visibility or schema-name problem. A syntax or function error can indicate use of a feature unavailable on that database version.

The installer handles the libaio1t64 compatibility case without requiring the dpkg-dev package. For manual recovery, locate the installed library with dpkg-query, then create the name expected by Instant Client:

$ library="$(dpkg-query -L libaio1t64 | awk '/\/libaio\.so\.1t64$/ {print; exit}')"
$ sudo ln -sfn "$(basename "$library")" "$(dirname "$library")/libaio.so.1"
$ sudo ldconfig

To inspect every location ODPI-C tries while diagnosing DPI-1047, set DPI_DEBUG_LEVEL=64 before starting Dosi:

$ DPI_DEBUG_LEVEL=64 dosi query --model model.yaml \
    --metrics revenue --execute --connection oracle

Do not set ORACLE_HOME for an Instant Client installation. Set TNS_ADMIN only when optional files such as tnsnames.ora or sqlnet.ora live in a separate directory.

Reference