> ## Documentation Index
> Fetch the complete documentation index at: https://knowledge.cloudquant.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Oracle

> Configure Oracle Database datasources with Oracle database driver thin mode

# Oracle

Oracle datasources use the `Oracle database driver` in thin mode. No Oracle Instant Client installation is required.

<Note>
  See [Supported Data Formats](/datasource-config/supported-formats) for database source categories and schema column types supported on Oracle connections.
</Note>

## Connection configuration

### URI format

```
oracle://user:password@host:1521/service_name
```

### Required fields

| Field                 | Type   | Description              |
| --------------------- | ------ | ------------------------ |
| `connection_type`     | string | Must be `"oracle"`       |
| `connection_sub_type` | string | Must be `"database"`     |
| `uri`                 | string | Oracle connection string |
| `server`              | string | Database host address    |
| `database`            | string | Oracle service name      |
| `username`            | string | Database user            |
| `password`            | string | Database password        |

### Optional fields

| Field       | Type   | Default | Description                                   |
| ----------- | ------ | ------- | --------------------------------------------- |
| `auth_mode` | string |         | Oracle authentication mode (e.g., `"SYSDBA"`) |
| `schema`    | string |         | Oracle schema name to query against           |

### Environment variables

| Variable                | Description                                     |
| ----------------------- | ----------------------------------------------- |
| `ORACLE_DEBUG_BOUNDARY` | Enable debug timing for Oracle query boundaries |

### Example connection

```json theme={null}
{
  "name": "oracle-market-data",
  "connection_type": "oracle",
  "connection_sub_type": "database",
  "uri": "oracle://libuser:s3cureP@ss@db.example.com:1521/ORCL",
  "server": "db.example.com",
  "database": "ORCL",
  "username": "libuser",
  "password": "s3cureP@ss"
}
```

## Dataset configuration (data\_args)

### Required fields

| Field              | Type                   | Description                                                |
| ------------------ | ---------------------- | ---------------------------------------------------------- |
| `table_options`    | list                   | Tables to query (see below)                                |
| `database`         | string                 | Connection reference name                                  |
| `data_dt_column`   | string or list         | Column(s) containing the datetime value                    |
| `data_dt_format`   | bool or string or list | `true` for native datetime auto-detect, or strptime format |
| `data_dt_timezone` | string                 | Timezone of the source data                                |
| `data_key_column`  | string or list         | Column(s) used as the symbol/key for query filtering       |

### Optional fields

| Field           | Type | Default | Description                              |
| --------------- | ---- | ------- | ---------------------------------------- |
| `batch_size`    | int  | `10000` | Number of rows per query batch           |
| `data_dt_nudge` | int  | `0`     | Microsecond offset applied to timestamps |

### table\_options entry

Each entry in `table_options` describes a table to query:

| Field          | Type   | Description                                         |
| -------------- | ------ | --------------------------------------------------- |
| `db`           | string | Database/service name                               |
| `table_like`   | string | Table name or pattern                               |
| `token`        | string | Table identifier token                              |
| `zone`         | string | Timezone, default `"America/New_York"`              |
| `dt_mode`      | string | *(Optional)* Datetime mode                          |
| `dt_regex`     | string | *(Optional)* Regex for partitioned tables           |
| `dt_format`    | string | *(Optional)* Datetime format for partitioned tables |
| `nudge_micros` | int    | *(Optional)* Microsecond nudge                      |

## Complete example

### Connection

```json theme={null}
{
  "name": "oracle-trades",
  "connection_type": "oracle",
  "connection_sub_type": "database",
  "uri": "oracle://libuser:s3cureP@ss@oradb.example.com:1521/TRADEDB",
  "server": "oradb.example.com",
  "database": "TRADEDB",
  "username": "libuser",
  "password": "s3cureP@ss",
  "schema": "MARKET_DATA"
}
```

### Dataset

```json theme={null}
{
  "name": "us-equity-trades",
  "connection": "oracle-trades",
  "data_args": {
    "table_options": [
      {
        "db": "TRADEDB",
        "table_like": "DAILY_TRADES",
        "token": "daily_trades",
        "zone": "America/New_York"
      }
    ],
    "database": "oracle-trades",
    "batch_size": 10000,
    "data_dt_column": "TRADE_TIME",
    "data_dt_format": true,
    "data_dt_timezone": "America/New_York",
    "data_dt_nudge": 0,
    "data_key_column": "SYMBOL"
  },
  "schema": [
    { "name": "SYMBOL", "type": "string", "group": "key", "description": "Ticker symbol" },
    { "name": "TRADE_TIME", "type": "string", "group": "time", "description": "Trade timestamp" },
    { "name": "PRICE", "type": "double", "group": "value", "description": "Trade price" },
    { "name": "VOLUME", "type": "int64", "group": "value", "description": "Trade volume" },
    { "name": "EXCHANGE", "type": "string", "group": "value", "description": "Exchange code" }
  ]
}
```

<Tip>
  Oracle column names are uppercase by default. Use uppercase names in your schema definition and `data_args` fields to match Oracle's metadata.
</Tip>

## Authentication modes

The `auth_mode` field supports Oracle authentication modes for privileged connections:

```json theme={null}
{
  "name": "oracle-admin",
  "connection_type": "oracle",
  "connection_sub_type": "database",
  "uri": "oracle://sys:adminP@ss@db.example.com:1521/ORCL",
  "server": "db.example.com",
  "database": "ORCL",
  "username": "sys",
  "password": "adminP@ss",
  "auth_mode": "SYSDBA"
}
```

<Warning>
  Using `SYSDBA` authentication in production datasource connections is not recommended. Create a dedicated read-only user with `SELECT` privileges on the required tables.
</Warning>

## Schema-qualified tables

When the `schema` field is set on the connection, CloudQuant Data Liberator queries tables within that schema. This is useful when the database user's default schema differs from where the data resides:

```json theme={null}
{
  "schema": "MARKET_DATA",
  "table_options": [
    {
      "db": "TRADEDB",
      "table_like": "DAILY_TRADES",
      "token": "daily_trades",
      "zone": "America/New_York"
    }
  ]
}
```

This queries `MARKET_DATA.DAILY_TRADES` rather than relying on the user's default schema.

## Microsecond timestamp precision

Oracle `TIMESTAMP(6)` columns support microsecond precision. A custom-patched `oracledb 3.4.0` is available for CloudQuant Data Liberator that preserves full microsecond precision during data extraction.

<Note>
  The standard `Oracle database driver 3.3+` driver supports microsecond timestamps. The custom-patched 3.4.0 build provides additional optimizations for high-frequency timestamp extraction. Contact your CloudQuant Data Liberator administrator for the patched driver.
</Note>

## Driver and dependencies

| Component              | Details                                   |
| ---------------------- | ----------------------------------------- |
| Driver                 | `Oracle database driver` 3.3+ (thin mode) |
| Protocol               | Oracle Net (TNS)                          |
| Arrow conversion       | Via Oracle driver native conversion       |
| Oracle Client required | No (thin mode)                            |
| Default port           | 1521                                      |

<Note>
  Thin mode connects directly to Oracle without requiring Oracle Instant Client. This simplifies deployment and eliminates native library dependencies.
</Note>

## Troubleshooting

### Connection refused

Verify the Oracle listener is running and accepting connections on the specified host and port:

```bash theme={null}
# Test port connectivity
nc -zv oradb.example.com 1521

# Test with sqlplus (if available)
sqlplus libuser/s3cureP@ss@oradb.example.com:1521/TRADEDB
```

### ORA-12514: TNS listener does not currently know of service

The service name in the URI does not match any registered service on the Oracle listener. Verify the service name:

```sql theme={null}
-- On the Oracle server
SELECT name FROM v$services;
```

### ORA-01017: invalid username/password

Ensure the username and password are correct. Oracle passwords are case-sensitive (depending on the `SEC_CASE_SENSITIVE_LOGON` parameter).

### Table not found (ORA-00942)

Check that the user has `SELECT` privileges on the target table and that the schema is correctly specified:

```sql theme={null}
GRANT SELECT ON MARKET_DATA.DAILY_TRADES TO libuser;
```

### Debug timing

To enable debug timing for Oracle query boundaries, set the environment variable:

```bash theme={null}
export ORACLE_DEBUG_BOUNDARY=1
```

This logs timing information for each query phase, useful for diagnosing performance bottlenecks.

### Slow queries

* Increase `batch_size` for large result sets (up to `100000`).
* Ensure the datetime and key columns are indexed in Oracle.
* For partitioned tables, verify that Oracle partition pruning is being used by checking the execution plan.
* Use `TIMESTAMP(6)` columns for microsecond precision rather than `DATE` (which only supports second precision).