spiceai/docs

spiceai/

docs

Help Login

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.5.x/components/data-connectors/oracle.md

spiceai/docs | Spice Cloud Platform

trunk

Edit on GitHub

Fork

/docs/website/versioned_docs/version-1.5.x/components/data-connectors/oracle.md

spiceai/docs/README.md

title: 'Oracle Data Connector' sidebar_label: 'Oracle Data Connector' description: 'Oracle Data Connector Documentation' tags:

data-connectors
oracle

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

:::warning[Limitations]

Only basic filter predicates are currently pushed down to the Oracle database. Full query federation is not currently supported. Joins, subqueries, and complex query constructs are not pushed down to the Oracle database; these operations are performed in-memory after data retrieval. Enable Data Acceleration for full federation support.
The Oracle connector does not support filter push-down optimization for datetime columns. Filtering on these columns is performed in-memory after data retrieval.
The following Oracle data types are not currently supported; columns with these types will be ignored: INTERVAL YEAR TO MONTH (Code 182), INTERVAL DAY TO SECOND (Code 183), UROWID (Code 208), BFILE (Code 114), JSON (Code 119).

:::

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:

`name`

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

Example:

`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 Name	Description
`oracle_connection_string`	The 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_host`	The hostname or IP address of the Oracle Database instance. Required when not using `oracle_connection_string`.
`oracle_port`	Optional. The port of the Oracle Database server. Default: `1521`
`oracle_username`	The Oracle username. Required.
`oracle_password`	The password to connect with. Required.
`oracle_service_name`	The Oracle Database service name to connect to. Default: `XEPDB1`
`oracle_wallet_sso_cert`	The base64-encoded `cwallet.sso` (wallet auto-login certificate) to use for mTLS authentication with Oracle Cloud.

Types

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

Oracle Type	Arrow Type
`ROWID`	`Utf8`
`CHAR`	`Utf8`
`NCHAR`	`Utf8`
`VARCHAR2`	`Utf8`
`NVARCHAR2`	`Utf8`
`LONG`	`Utf8`
`CLOB`	`LargeUtf8`
`NCLOB`	`LargeUtf8`
`NUMBER`	for integer types (scale=0, precision≤18), otherwise

:::note

The Oracle TIMESTAMP WITH LOCAL TIME ZONE value is retrieved as a UTC time value.

:::

Examples

Connecting to On-Premises Oracle Database

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:

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.

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

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

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.

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

A cookbook recipe to connect to and accelerate data from an Oracle database in Spice. Oracle Data Connector

spiceai/docs/README.md

title: 'Oracle Data Connector' sidebar_label: 'Oracle Data Connector' description: 'Oracle Data Connector Documentation' tags:

data-connectors
oracle

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

:::warning[Limitations]

Only basic filter predicates are currently pushed down to the Oracle database. Full query federation is not currently supported. Joins, subqueries, and complex query constructs are not pushed down to the Oracle database; these operations are performed in-memory after data retrieval. Enable Data Acceleration for full federation support.
The Oracle connector does not support filter push-down optimization for datetime columns. Filtering on these columns is performed in-memory after data retrieval.
The following Oracle data types are not currently supported; columns with these types will be ignored: INTERVAL YEAR TO MONTH (Code 182), INTERVAL DAY TO SECOND (Code 183), UROWID (Code 208), BFILE (Code 114), JSON (Code 119).

:::

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:

`name`

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

Example:

`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 Name	Description
`oracle_connection_string`	The 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_host`	The hostname or IP address of the Oracle Database instance. Required when not using `oracle_connection_string`.
`oracle_port`	Optional. The port of the Oracle Database server. Default: `1521`
`oracle_username`	The Oracle username. Required.
`oracle_password`	The password to connect with. Required.
`oracle_service_name`	The Oracle Database service name to connect to. Default: `XEPDB1`
`oracle_wallet_sso_cert`	The base64-encoded `cwallet.sso` (wallet auto-login certificate) to use for mTLS authentication with Oracle Cloud.

Types

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

Oracle Type	Arrow Type
`ROWID`	`Utf8`
`CHAR`	`Utf8`
`NCHAR`	`Utf8`
`VARCHAR2`	`Utf8`
`NVARCHAR2`	`Utf8`
`LONG`	`Utf8`
`CLOB`	`LargeUtf8`
`NCLOB`	`LargeUtf8`
`NUMBER`	for integer types (scale=0, precision≤18), otherwise

:::note

The Oracle TIMESTAMP WITH LOCAL TIME ZONE value is retrieved as a UTC time value.

:::

Examples

Connecting to On-Premises Oracle Database

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

Wallet Folder Exists Locally

Example:

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

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

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

Installation Requirements

Secrets

Cookbook

A cookbook recipe to connect to and accelerate data from an Oracle database in Spice. Oracle Data Connector

oracle_wallet

Int64

Decimal128

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

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

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

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

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: products
    params: ...

datasets:
  - from: oracle:"SH"."PRODUCTS"
    name: products
    params: ...

SELECT COUNT(*) FROM products;

SELECT COUNT(*) FROM products;

+----------+
| count(*) |
+----------+
|  10500   |
+----------+

+----------+
| count(*) |
+----------+
|  10500   |
+----------+

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

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

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'

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'

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'

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'

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

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

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'

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'