> For the complete documentation index, see [llms.txt](https://docs.spice.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.spice.ai/building-blocks/data-connectors/oracle.md). # Oracle 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). ```yaml 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 ``` {% hint style="warning" %} **Limitations** 1. 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**. 2. The Oracle connector does not support filter push-down optimization for datetime columns. Filtering on these columns is performed in-memory after data retrieval. 3. 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). {% endhint %} ## 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: ```yaml 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: ```yaml datasets: - from: oracle:"SH"."PRODUCTS" name: products params: ... ``` ```sql SELECT COUNT(*) FROM products; ``` ```shell +----------+ | 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 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](https://download.oracle.com/ocomdocs/global/Oracle-Net-Easy-Connect-Plus.pdf) 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. | | `oracle_wallet` | Specifies 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 Type | Arrow Type | | -------------------------------- | -------------------------------------------------------------------------------- | | `ROWID` | `Utf8` | | `CHAR` | `Utf8` | | `NCHAR` | `Utf8` | | `VARCHAR2` | `Utf8` | | `NVARCHAR2` | `Utf8` | | `LONG` | `Utf8` | | `CLOB` | `LargeUtf8` | | `NCLOB` | `LargeUtf8` | | `NUMBER` | `Int64` for integer types (scale=0, precision≤18), otherwise `Decimal128` | | `FLOAT` | `Float32` for precision≤24, otherwise `Float64` | | `BINARY_FLOAT` | `Float32` | | `BINARY_DOUBLE` | `Float64` | | `BOOLEAN` | `Boolean` | | `DATE` | `Date32` | | `TIMESTAMP` | `Timestamp(Second)` for precision=0, otherwise `Timestamp(Nanosecond)` | | `TIMESTAMP WITH TIME ZONE` | `Timestamp(Second, UTC)` for precision=0, otherwise `Timestamp(Nanosecond, UTC)` | | `TIMESTAMP WITH LOCAL TIME ZONE` | `Timestamp(Second, UTC)` for precision=0, otherwise `Timestamp(Nanosecond, UTC)` | | `RAW` | `Binary` | | `LONG RAW` | `Binary` | | `BLOB` | `LargeBinary` | {% hint style="info" %} The Oracle `TIMESTAMP WITH LOCAL TIME ZONE` value is retrieved as a UTC time value. {% endhint %} ## Examples ### Connecting to On-Premises Oracle Database ```yaml 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: ```yaml 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. ```yaml 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: ```shell base64 -i cwallet.sso > cwallet.b64.txt ``` ### Connecting with Easy Connect string (TLS-only, no wallet required) ```yaml 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](https://oracle.github.io/odpi/) for your platform. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.spice.ai/building-blocks/data-connectors/oracle.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.