Data Connectors

Oracle Data Connector

Oracle Data Connector Documentation

The Oracle Data Connector enables SQL queries on data stored in Oracle databases, including on-premises instances, Oracle Cloud User-Managed Databases, and Oracle Cloud Autonomous Databases (ADB).

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: my_dataset
    params:
      oracle_host: localhost
      oracle_port: 1521
      oracle_username: scott
      oracle_password: ${secrets:oracle_password}
      oracle_service_name: XEPDB1

Configuration

from

The from field takes the form oracle:"schema_name"."table_name" where both schema and table names should be quoted to handle case sensitivity properly.

Example:

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: products
    params:
      oracle_host: localhost
      oracle_username: scott
      oracle_password: ${secrets:ORACLE_PASSWORD}

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: products
    params: ...
SELECT COUNT(*) FROM products;
+----------+
| count(*) |
+----------+
|  10500   |
+----------+

params

The Oracle data connector can be configured by providing the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:MY_ORACLE_PASSWORD}.

Parameter NameDescription
oracle_connection_stringThe connection string to use to connect to the Oracle server. This can be a TNS alias from tnsnames.ora for local mTLS/Wallet connections or an Easy Connect string.
oracle_hostThe hostname or IP address of the Oracle Database instance. Required when not using oracle_connection_string.
oracle_portOptional. The port of the Oracle Database server. Default: 1521
oracle_usernameThe Oracle username. Required.
oracle_passwordThe password to connect with. Required.
oracle_service_nameThe Oracle Database service name to connect to. Default: XEPDB1
oracle_wallet_sso_certThe base64-encoded cwallet.sso (wallet auto-login certificate) to use for mTLS authentication with Oracle Cloud.
oracle_walletSpecifies the Oracle wallet location used to save the provided mTLS certificate (oracle_wallet_sso_cert) or retrieve an existing/pre-downloaded certificate.

Types

The table below shows the Oracle data types supported, along with the type mapping to Apache Arrow types in Spice.

Oracle TypeArrow Type
ROWIDUtf8
CHARUtf8
NCHARUtf8
VARCHAR2Utf8
NVARCHAR2Utf8
LONGUtf8
CLOBLargeUtf8
NCLOBLargeUtf8
NUMBERInt64 for integer types (scale=0, precision≤18), otherwise Decimal128
FLOATFloat32 for precision≤24, otherwise Float64
BINARY_FLOATFloat32
BINARY_DOUBLEFloat64
BOOLEANBoolean
DATEDate32
TIMESTAMPTimestamp(Second) for precision=0, otherwise Timestamp(Nanosecond)
TIMESTAMP WITH TIME ZONETimestamp(Second, UTC) for precision=0, otherwise Timestamp(Nanosecond, UTC)
TIMESTAMP WITH LOCAL TIME ZONETimestamp(Second, UTC) for precision=0, otherwise Timestamp(Nanosecond, UTC)
RAWBinary
LONG RAWBinary
BLOBLargeBinary

Examples

Connecting to On-Premises Oracle Database

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: products
    params:
      oracle_host: localhost
      oracle_port: 1521
      oracle_username: scott
      oracle_password: ${secrets:ORACLE_PASSWORD}
      oracle_service_name: XEPDB1

Connecting to Oracle Cloud Autonomous Database with mTLS (Wallet-based)

Wallet Folder Exists Locally

If your Oracle Cloud Autonomous Database wallet folder is available locally, specify its path using the oracle_wallet parameter. Set the oracle_connection_string to the TNS alias defined in your wallet's tnsnames.ora file.

Example:

datasets:
  - from: oracle:"SALES"
    name: sales
    params:
      oracle_username: admin
      oracle_password: ${secrets:ORACLE_PASSWORD}
      oracle_connection_string: 'fgp1tqs1e_low'  # TNS alias from tnsnames.ora
      oracle_wallet: '/path/to/wallet_folder'

Wallet Auto-Login (SSO) Certificate Provided via Application Secret

If your Oracle Cloud Autonomous Database wallet folder is not available locally, provide the base64-encoded wallet auto-login (SSO) certificate (cwallet.sso) using the oracle_wallet_sso_cert parameter. Set the oracle_connection_string to the Easy Connect string from the Database connection section.

datasets:
  - from: oracle:"SALES"
    name: sales
    params:
      oracle_username: admin
      oracle_password: ${secrets:ORACLE_PASSWORD}
      oracle_wallet_sso_cert: ${secrets:oracle_wallet_sso_cert}
      oracle_connection_string: 'tcps://adb.us-sanjose-1.oraclecloud.com:1522/g81f1d1d5c853_fgc1e_low.adb.oraclecloud.com?ssl_server_dn_match=yes'

To generate a base64-encoded wallet certificate for use as a secret:

base64 -i cwallet.sso > cwallet.b64.txt

Connecting with Easy Connect string (TLS-only, no wallet required)

datasets:
  - from: oracle:"SALES"
    name: sales
    params:
      oracle_username: admin
      oracle_password: ${secrets:ORACLE_PASSWORD}
      oracle_connection_string: 'tcps://adb.us-sanjose-1.oraclecloud.com:1522/g81f1d1d5c853_fgc1e_low.adb.oraclecloud.com?ssl_server_dn_match=yes'

Installation Requirements

The Oracle data connector requires the Oracle Instant Client or Oracle Database Client libraries to be installed on the system where Spice is running. Follow the Oracle installation guide for your platform.