Oracle

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

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

{
  "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

{
  "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

{
  "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" }
  ]
}

Oracle column names are uppercase by default. Use uppercase names in your schema definition and data_args fields to match Oracle’s metadata.

Authentication Modes

The auth_mode field supports Oracle authentication modes for privileged connections:

{
  "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"
}

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

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:

{
  "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.

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.

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

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

Troubleshooting

Connection refused

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

# 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:

-- 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:

GRANT SELECT ON MARKET_DATA.DAILY_TRADES TO libuser;

Debug timing

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

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

Connections

​Oracle

​Connection Configuration

​URI Format

​Required Fields

​Optional Fields

​Environment Variables

​Example Connection

​Dataset Configuration (data_args)

​Required Fields

​Optional Fields

​table_options Entry

​Complete Example

​Connection

​Dataset

​Authentication Modes

​Schema-Qualified Tables

​Microsecond Timestamp Precision

​Driver and Dependencies

​Troubleshooting

​Connection refused

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

​ORA-01017: invalid username/password

​Table not found (ORA-00942)

​Debug timing

​Slow queries

Oracle

Connection Configuration

URI Format

Required Fields

Optional Fields

Environment Variables

Example Connection

Dataset Configuration (data_args)

Required Fields

Optional Fields

table_options Entry

Complete Example

Connection

Dataset

Authentication Modes

Schema-Qualified Tables

Microsecond Timestamp Precision

Driver and Dependencies

Troubleshooting

Connection refused

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

ORA-01017: invalid username/password

Table not found (ORA-00942)

Debug timing

Slow queries