OracleDB#

Type Handling#

SQLSpec installs composable Oracle input and output handlers when a pooled connection is initialized. The handlers preserve Python values where the database has a matching native type and use explicit Oracle storage conventions where it does not.

JSON Storage By Oracle Version#

The server version selects the automatic JSON bind rung:

BLOB IS JSON is the preferred pre-native JSON storage because UTF-8 byte storage avoids CLOB character-set conversion and typically uses less space for JSON. Keep CLOB storage as an explicit compatibility or application choice.

Explicit CLOB CHECK (payload IS JSON) columns remain readable through metadata-driven conversion on supported servers. For BLOB IS JSON storage, SQLSpec serializes direct Python JSON values to UTF-8 and binds a BLOB locator; callers do not need to provide serialized strings.

Driver Feature Escape Hatches#

Pass these keys through driver_features on OracleSyncConfig or OracleAsyncConfig:

LOB And JSON Fetching#

Oracle configurations default fetch_lobs to False. With modern python-oracledb this returns supported LOB values under Oracle's 1 GB direct-fetch ceiling directly as str or bytes for normal SELECTs, streaming reads, and Arrow exports. SQLSpec still materializes readable locators when Oracle returns one, so buffered results and schema hydration do not expose driver handles by default.

Pass fetch_lobs=True on a query when application code needs native Oracle LOB locators, for example in a streaming workflow that wants to control when a large value is read.

JSON fetch conversion is metadata-driven:

• native JSON columns are returned by python-oracledb;

• IS JSON CLOB/BLOB/VARCHAR2 columns are decoded through Oracle fetch metadata;

• OSON BLOB values are decoded through Oracle's OSON support when the server and driver expose it.

Unconstrained CLOB or BLOB columns are returned as text or bytes even when their contents look like JSON. Add an Oracle JSON type or IS JSON constraint when you want automatic JSON decoding.

MERGE Upserts#

Oracle uses MERGE for an update-or-insert operation. PostgreSQL INSERT ... ON CONFLICT syntax is not valid Oracle SQL, and SQLSpec does not rewrite it into MERGE. For a single row, select the named bind values from DUAL and use the same source aliases in both branches:

merge_widget = """
MERGE INTO widget t
USING (
    SELECT :sku AS sku, :name AS name, :quantity AS quantity
    FROM DUAL
) s
ON (t.sku = s.sku)
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name,
        t.quantity = s.quantity,
        t.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, sku, name, quantity, created_at, updated_at)
    VALUES (
        widget_seq.NEXTVAL,
        s.sku,
        s.name,
        s.quantity,
        SYSTIMESTAMP,
        SYSTIMESTAMP
    )
"""

await session.execute(
    merge_widget,
    sku="W-100",
    name="Widget",
    quantity=3,
)

Do not add RETURNING to this MERGE. When the caller needs an ID generated by the insert branch, select it by the same unique key before the transaction is committed:

widget_id = await session.select_value(
    "SELECT id FROM widget WHERE sku = :sku",
    sku="W-100",
)

Keeping the MERGE and follow-up SELECT in one SQLSpec session preserves their transaction boundary. For large LOB values, the adapter's Litestar session store uses the same pattern to merge an EMPTY_BLOB(), select it FOR UPDATE, and write through the returned locator.

Extension Table Storage Options#

Oracle ADK, durable event, and Litestar session tables support the same opt-in storage concepts under their extension configuration: in_memory, compression, partitioning, and table options. For example, an events queue can use Advanced Compression and monthly interval partitions:

extension_config = {
    "events": {
        "compression": {"enabled": True, "algorithm": "advanced"},
        "partitioning": {
            "strategy": "range",
            "partition_key": "available_at",
            "interval": "month",
        },
        "table_options": "TABLESPACE event_data",
    }
}

Use the same keys under litestar; range partitioning defaults to expires_at. Under adk, per-table options use names such as session_table_options, events_table_options, and memory_table_options. ADK partition settings can likewise override a specific table key with session_partition_key for sessions, events_partition_key for ADK events, or queue_partition_key for the Events queue. ADK state and memory tables use their corresponding table keys.

SQLSpec emits the requested compression, in-memory, partitioning, and table option clauses directly. It does not check the option catalog or omit requested clauses based on feature availability. Oracle reports an error if the database cannot apply a requested option.

SQLSpec does not automatically add SECUREFILE LOB compression. Its safety also depends on tablespace segment-space management and database-level DB_SECUREFILE policy, which cannot be established from the option catalog alone. Add a reviewed LOB clause through the table-options setting when the deployment guarantees those prerequisites.

Sync Configuration#

class sqlspec.adapters.oracledb.OracleSyncConfig[source]#

Bases: SyncDatabaseConfig[Connection, OracleSyncConnectionPool, OracleSyncDriver]

Configuration for Oracle synchronous database connections.

driver_type#

alias of OracleSyncDriver

migration_tracker_type#

alias of OracleSyncMigrationTracker

__init__(*, connection_config=None, connection_instance=None, migration_config=None, statement_config=None, driver_features=None, bind_key=None, extension_config=None, **kwargs)[source]#

Initialize Oracle synchronous configuration.

Parameters:

• connection_config¶ -- Connection and pool configuration parameters.

• connection_instance¶ -- Existing pool instance to use.

• migration_config¶ -- Migration configuration.

• statement_config¶ -- Default SQL statement configuration.

• driver_features¶ -- Optional driver feature configuration (TypedDict or dict).

• bind_key¶ -- Optional unique identifier for this configuration.

• extension_config¶ -- Extension-specific configuration.

• **kwargs¶ -- Additional keyword arguments.

create_connection()[source]#

Create a single connection (not from pool).

Return type:

Connection

Returns:

An Oracle Connection instance.

provide_pool()[source]#

Provide pool instance.

Return type:

ConnectionPool

Returns:

The connection pool.

get_signature_namespace()[source]#

Get the signature namespace for OracleDB types.

Provides OracleDB-specific types for Litestar framework recognition.

Return type:

dict[str, typing.Any]

Returns:

Dictionary mapping type names to types.

get_event_runtime_hints()[source]#

Return polling defaults for Oracle table-backed event queues.

Return type:

EventRuntimeHints

Async Configuration#

class sqlspec.adapters.oracledb.OracleAsyncConfig[source]#

Bases: AsyncDatabaseConfig[AsyncConnection, OracleAsyncConnectionPool, OracleAsyncDriver]

Configuration for Oracle asynchronous database connections.

driver_type#

alias of OracleAsyncDriver

migration_tracker_type#

alias of OracleAsyncMigrationTracker

__init__(*, connection_config=None, connection_instance=None, migration_config=None, statement_config=None, driver_features=None, bind_key=None, extension_config=None, **kwargs)[source]#

Initialize Oracle asynchronous configuration.

Parameters:

• connection_config¶ -- Connection and pool configuration parameters.

• connection_instance¶ -- Existing pool instance to use.

• migration_config¶ -- Migration configuration.

• statement_config¶ -- Default SQL statement configuration.

• driver_features¶ -- Optional driver feature configuration (TypedDict or dict).

• bind_key¶ -- Optional unique identifier for this configuration.

• extension_config¶ -- Extension-specific configuration.

• **kwargs¶ -- Additional keyword arguments.

async create_connection()[source]#

Create a single async connection (not from pool).

Return type:

AsyncConnection

Returns:

An Oracle AsyncConnection instance.

async provide_pool()[source]#

Provide async pool instance.

Return type:

AsyncConnectionPool

Returns:

The async connection pool.

get_signature_namespace()[source]#

Get the signature namespace for OracleAsyncConfig types.

Return type:

dict[str, typing.Any]

Returns:

Dictionary mapping type names to types.

get_event_runtime_hints()[source]#

Return polling defaults for Oracle table-backed event queues.

Return type:

EventRuntimeHints

Connection Parameters#

class sqlspec.adapters.oracledb.OracleConnectionParams[source]#

Bases: TypedDict

OracleDB connection parameters.

Pool Parameters#

class sqlspec.adapters.oracledb.OraclePoolParams[source]#

Bases: OracleConnectionParams

OracleDB pool parameters.

Driver Features#

class sqlspec.adapters.oracledb.OracleDriverFeatures[source]#

Bases: TypedDict

Oracle driver feature flags.

enable_numpy_vectors: Enable automatic NumPy array ↔ Oracle VECTOR conversion.

Requires NumPy and Oracle Database 23ai or higher with VECTOR data type support. Defaults to True when NumPy is installed. Provides automatic bidirectional conversion between NumPy ndarrays and Oracle VECTOR columns. Supports float32, float64, int8, and uint8 dtypes.

enable_lowercase_column_names: Normalize implicit Oracle uppercase column names to lowercase.

Targets unquoted Oracle identifiers that default to uppercase while preserving quoted case-sensitive aliases. Defaults to True for compatibility with schema libraries expecting snake_case fields.

enable_uuid_binary: Enable automatic UUID ↔ RAW(16) binary conversion.

When True (default), Python UUID objects are automatically converted to/from RAW(16) binary format for optimal storage efficiency (16 bytes vs 36 bytes). Applies only to RAW(16) columns; other RAW sizes remain unchanged. Uses Python's stdlib uuid module (no external dependencies). Defaults to True for improved type safety and storage efficiency.

vector_return_format: Return type for VECTOR column reads. One of:

• "numpy" (default when NumPy is installed): np.ndarray, zero-copy compute path.

• "list": list[float|int], best for code that expects native Python sequences.

• "array": array.array, zero-copy oracledb passthrough.

Defaults to "numpy" when NumPy is installed, otherwise "list". Sparse VECTOR columns always bind and return python-oracledb SparseVector values.

oracle_varchar2_byte_limit: Threshold (in UTF-8 bytes) above which str

parameters are auto-coerced to DB_TYPE_CLOB. Defaults to 4000 (the Oracle SQL VARCHAR2 limit). Databases with MAX_STRING_SIZE=EXTENDED may set this to 32767 to keep larger strings as VARCHAR2.

oracle_raw_byte_limit: Threshold (in bytes) above which bytes parameters

are auto-coerced to DB_TYPE_BLOB. Defaults to 2000 (the Oracle SQL RAW limit).

arraysize: Optional per-cursor row fetch buffer size. When absent, the

python-oracledb cursor default is left unchanged.

prefetchrows: Optional per-cursor prefetch row count. When absent, the

python-oracledb cursor default is left unchanged.

fetch_lobs: Optional per-statement LOB fetch mode. False returns

supported LOB values as str/bytes instead of LOB locators.

fetch_decimals: Optional per-statement NUMBER fetch mode. True returns

decimal values where python-oracledb supports them.

on_connection_create: Callback executed when a connection is acquired from pool.

For sync: Callable[[OracleSyncConnection, str], None] - receives connection and tag For async: Callable[[OracleAsyncConnection, str], Awaitable[None]] Called after internal setup (numpy vectors, UUID handlers).

enable_events: Enable SQLSpec event queue support.

Defaults to True when extension_config["events"] is configured. Provides pub/sub capabilities via Oracle Advanced Queuing or table-backed fallback. Requires extension_config["events"] for migration setup when using poll_queue backend. This is separate from connection_config["events"], which enables python-oracledb Thick mode database event notifications for HA and continuous query notification.

events_backend: Event channel backend selection.

Options: "aq", "poll_queue", "txeventq" - "aq": Oracle Advanced Queuing (native messaging, requires DBMS_AQADM privileges) - "txeventq": Oracle Transactional Event Queues (native messaging, requires

DBMS_AQADM privileges; provisioned via DBMS_AQADM.CREATE_TRANSACTIONAL_EVENT_QUEUE)

• "poll_queue": Durable table-backed queue with lease-based retries and acknowledgements

Defaults to "poll_queue" (works on all Oracle editions without special privileges).

enable_direct_path_load: Route load_from_arrow through Connection.direct_path_load.

Thin-mode only; falls back to executemany when the API is absent or the connection is in Thick mode. Defaults to True; set to False to force executemany.

Native pipeline execution is runtime-gated by async Thin-mode driver API support,

Oracle Database 26ai or newer, and the SQLSPEC_ORACLE_DISABLE_PIPELINE environment override; there is no adapter config switch that can force-enable unsupported pipeline execution.

Sync Driver#

class sqlspec.adapters.oracledb.OracleSyncDriver[source]#

Bases: OraclePipelineMixin, SyncDriverAdapterBase

Synchronous Oracle Database driver.

Provides Oracle Database connectivity with parameter style conversion, error handling, and transaction management.

__init__(connection, statement_config=None, driver_features=None)[source]#

dispatch_execute(cursor, statement)[source]#

Execute single SQL statement with Oracle data handling.

For SELECT-like statements, fetches all rows, resolves row metadata, and applies LOB coercion if needed. For non-SELECT statements, resolves and returns the affected row count.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL statement to execute

Return type:

ExecutionResult

Returns:

Execution result containing data for SELECT statements or row count for others

dispatch_execute_many(cursor, statement)[source]#

Execute SQL with multiple parameter sets using Oracle batch processing.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL statement with multiple parameter sets

Return type:

ExecutionResult

Returns:

Execution result with affected row count

dispatch_execute_script(cursor, statement)[source]#

Execute SQL script with statement splitting and parameter handling.

Parameters are embedded as static values for script execution compatibility.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL script statement to execute

Return type:

ExecutionResult

Returns:

Execution result containing statement count and success information

begin()[source]#

Begin a database transaction.

Oracle starts a transaction implicitly on the first DML, so no explicit statement is issued; the active-transaction flag is set here.

Return type:

None

commit()[source]#

Commit the current transaction.

Raises:

SQLSpecError -- If commit fails

Return type:

None

rollback()[source]#

Rollback the current transaction.

Raises:

SQLSpecError -- If rollback fails

Return type:

None

release_savepoint(name)[source]#

Validate the savepoint name; Oracle releases savepoints when the transaction ends.

Return type:

None

set_migration_session_schema(schema)[source]#

Set Oracle CURRENT_SCHEMA for migration SQL.

Return type:

None

has_schema(schema)[source]#

Return whether an Oracle schema/user exists.

Return type:

bool

with_cursor(connection)[source]#

Create context manager for Oracle cursor.

Parameters:

connection¶ (Connection) -- Oracle database connection

Return type:

OracleSyncCursor

Returns:

Context manager for cursor operations

select_stream(statement, /, *parameters, schema_type=None, statement_config=None, chunk_size=1000, native_only=False, **kwargs)[source]#

Execute a query and stream rows in chunks with Oracle fetch tuning.

Overloads:

• self, statement (SQL | Statement | QueryBuilder), parameters (StatementParameters | StatementFilter), schema_type (type[SchemaT]), statement_config (StatementConfig | None), chunk_size (int), native_only (bool), kwargs (Any) → SyncRowStream[SchemaT]

• self, statement (SQL | Statement | QueryBuilder), parameters (StatementParameters | StatementFilter), schema_type (None), statement_config (StatementConfig | None), chunk_size (int), native_only (bool), kwargs (Any) → SyncRowStream[dict[str, Any]]

dispatch_select_stream(statement, chunk_size, fetch_lobs=None)[source]#

Return a native oracledb row stream backed by chunked fetchmany.

Return type:

Optional[SyncRowStream[dict[str, typing.Any]]]

handle_database_exceptions()[source]#

Handle database-specific exceptions and wrap them appropriately.

Return type:

OracleSyncExceptionHandler

select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)[source]#

Execute query and return results as Apache Arrow format using Oracle native support.

This implementation uses Oracle's native execute_df()/fetch_df_all() methods which return OracleDataFrame objects with Arrow PyCapsule interface, providing zero-copy data transfer and 5-10x performance improvement over dict conversion. If native Arrow is unavailable and native_only is False, it falls back to the conversion path.

Parameters:

• statement¶ -- SQL query string, Statement, or QueryBuilder

• *parameters¶ -- Query parameters (same format as execute()/select())

• statement_config¶ -- Optional statement configuration override

• return_format¶ -- "table" for pyarrow.Table (default), "batch" for RecordBatch, "batches" for list of RecordBatch, "reader" for RecordBatchReader

• native_only¶ -- If True, raise error if native Arrow is unavailable

• batch_size¶ -- Rows per batch when using "batch" or "batches" format

• arrow_schema¶ -- Optional pyarrow.Schema for type casting

• **kwargs¶ -- Additional keyword arguments

Returns:

ArrowResult containing pyarrow.Table or RecordBatch

execute_stack(stack, *, continue_on_error=False)[source]#

Execute a StatementStack using Oracle's pipeline when available.

Return type:

tuple[StackResult, ...]

select_to_storage(statement, destination, /, *parameters, statement_config=None, partitioner=None, format_hint=None, telemetry=None, **kwargs)[source]#

Execute a query and stream Arrow-formatted output to storage (sync).

load_from_arrow(table, source, *, partitioner=None, overwrite=False, telemetry=None)[source]#

Load Arrow data into Oracle using batched executemany calls.

Return type:

StorageBridgeJob

load_from_storage(table, source, *, file_format, partitioner=None, overwrite=False)[source]#

Load staged artifacts into Oracle.

Return type:

StorageBridgeJob

property data_dictionary: OracledbSyncDataDictionary#

Get the data dictionary for this driver.

Returns:

Data dictionary instance for metadata queries

collect_rows(cursor, fetched)[source]#

Collect Oracle sync rows for the direct execution path.

Return type:

tuple[list[typing.Any], list[str], int]

resolve_rowcount(cursor)[source]#

Resolve rowcount from Oracle cursor for the direct execution path.

Return type:

int

Async Driver#

class sqlspec.adapters.oracledb.OracleAsyncDriver[source]#

Bases: OraclePipelineMixin, AsyncDriverAdapterBase

Asynchronous Oracle Database driver.

Provides Oracle Database connectivity with parameter style conversion, error handling, and transaction management for async operations.

__init__(connection, statement_config=None, driver_features=None)[source]#

async dispatch_execute(cursor, statement)[source]#

Execute single SQL statement with Oracle data handling.

For SELECT-like statements, fetches all rows, resolves row metadata, and applies LOB coercion if needed. For non-SELECT statements, resolves and returns the affected row count.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL statement to execute

Return type:

ExecutionResult

Returns:

Execution result containing data for SELECT statements or row count for others

async dispatch_execute_many(cursor, statement)[source]#

Execute SQL with multiple parameter sets using Oracle batch processing.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL statement with multiple parameter sets

Return type:

ExecutionResult

Returns:

Execution result with affected row count

async dispatch_execute_script(cursor, statement)[source]#

Execute SQL script with statement splitting and parameter handling.

Parameters are embedded as static values for script execution compatibility.

Parameters:

• cursor¶ (Any) -- Oracle cursor object

• statement¶ (SQL) -- SQL script statement to execute

Return type:

ExecutionResult

Returns:

Execution result containing statement count and success information

async begin()[source]#

Begin a database transaction.

Oracle starts a transaction implicitly on the first DML, so no explicit statement is issued; the active-transaction flag is set here.

Return type:

None

async commit()[source]#

Commit the current transaction.

Raises:

SQLSpecError -- If commit fails

Return type:

None

async rollback()[source]#

Rollback the current transaction.

Raises:

SQLSpecError -- If rollback fails

Return type:

None

async release_savepoint(name)[source]#

Validate the savepoint name; Oracle releases savepoints when the transaction ends.

Return type:

None

async set_migration_session_schema(schema)[source]#

Set Oracle CURRENT_SCHEMA for migration SQL.

Return type:

None

async has_schema(schema)[source]#

Return whether an Oracle schema/user exists.

Return type:

bool

with_cursor(connection)[source]#

Create context manager for Oracle cursor.

Parameters:

connection¶ (AsyncConnection) -- Oracle database connection

Return type:

OracleAsyncCursor

Returns:

Context manager for cursor operations

select_stream(statement, /, *parameters, schema_type=None, statement_config=None, chunk_size=1000, native_only=False, **kwargs)[source]#

Execute a query and stream rows in chunks with Oracle fetch tuning.

Overloads:

• self, statement (SQL | Statement | QueryBuilder), parameters (StatementParameters | StatementFilter), schema_type (type[SchemaT]), statement_config (StatementConfig | None), chunk_size (int), native_only (bool), kwargs (Any) → AsyncRowStream[SchemaT]

• self, statement (SQL | Statement | QueryBuilder), parameters (StatementParameters | StatementFilter), schema_type (None), statement_config (StatementConfig | None), chunk_size (int), native_only (bool), kwargs (Any) → AsyncRowStream[dict[str, Any]]

dispatch_select_stream(statement, chunk_size, fetch_lobs=None)[source]#

Return a native oracledb row stream backed by chunked fetchmany.

Return type:

Optional[AsyncRowStream[dict[str, typing.Any]]]

handle_database_exceptions()[source]#

Handle database-specific exceptions and wrap them appropriately.

Return type:

OracleAsyncExceptionHandler

async select_to_arrow(statement, /, *parameters, statement_config=None, return_format='table', native_only=False, batch_size=None, arrow_schema=None, **kwargs)[source]#

Execute query and return results as Apache Arrow format using Oracle native support.

This implementation uses Oracle's native execute_df()/fetch_df_all() methods which return OracleDataFrame objects with Arrow PyCapsule interface, providing zero-copy data transfer and 5-10x performance improvement over dict conversion. If native Arrow is unavailable and native_only is False, it falls back to the conversion path.

Parameters:

• statement¶ -- SQL query string, Statement, or QueryBuilder

• *parameters¶ -- Query parameters (same format as execute()/select())

• statement_config¶ -- Optional statement configuration override

• return_format¶ -- "table" for pyarrow.Table (default), "batch" for RecordBatch, "batches" for list of RecordBatch, "reader" for RecordBatchReader

• native_only¶ -- If True, raise error if native Arrow is unavailable

• batch_size¶ -- Rows per batch when using "batch" or "batches" format

• arrow_schema¶ -- Optional pyarrow.Schema for type casting

• **kwargs¶ -- Additional keyword arguments

Returns:

ArrowResult containing pyarrow.Table or RecordBatch

async execute_stack(stack, *, continue_on_error=False)[source]#

Execute a StatementStack using Oracle's pipeline when available.

Return type:

tuple[StackResult, ...]

async select_to_storage(statement, destination, /, *parameters, statement_config=None, partitioner=None, format_hint=None, telemetry=None, **kwargs)[source]#

Execute a query and write Arrow-compatible output to storage (async).

async load_from_arrow(table, source, *, partitioner=None, overwrite=False, telemetry=None)[source]#

Asynchronously load Arrow data into Oracle.

Return type:

StorageBridgeJob

async load_from_storage(table, source, *, file_format, partitioner=None, overwrite=False)[source]#

Asynchronously load staged artifacts into Oracle.

Return type:

StorageBridgeJob

property data_dictionary: OracledbAsyncDataDictionary#

Get the data dictionary for this driver.

Returns:

Data dictionary instance for metadata queries

collect_rows(cursor, fetched)[source]#

Collect Oracle async rows for the direct execution path.

Falls back to the standard async dispatch path when rows contain async LOB locators, because those must be read with collect_async_rows.

Return type:

tuple[list[typing.Any], list[str], int]

resolve_rowcount(cursor)[source]#

Resolve rowcount from Oracle cursor for the direct execution path.

Return type:

int

Data Dictionary#

class sqlspec.adapters.oracledb.data_dictionary.OracleVersionInfo[source]#

Bases: VersionInfo

Oracle database version information.

__init__(major, minor=0, patch=0, compatible=None, is_autonomous=False)[source]#

Initialize Oracle version info.

Parameters:

• major¶ (int) -- Major version number.

• minor¶ (int) -- Minor version number.

• patch¶ (int) -- Patch version number.

• compatible¶ (str | None) -- Compatible parameter value.

• is_autonomous¶ (bool) -- Whether this is an Autonomous Database.

property compatible_major: int | None#

Get major version from compatible parameter.

supports_native_json()[source]#

Check if database supports native JSON data type.

Return type:

bool

supports_oson_blob()[source]#

Check if database supports BLOB with OSON format.

Return type:

bool

supports_json_blob()[source]#

Check if database supports BLOB with JSON validation.

Return type:

bool

__str__()[source]#

String representation of version info.

Return type:

str

class sqlspec.adapters.oracledb.data_dictionary.OracledbSyncDataDictionary[source]#

Bases: SyncDataDictionaryBase

Oracle-specific sync data dictionary.

dialect: ClassVar[str] = 'oracle'#

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()[source]#

get_dialect_config()[source]#

Return the dialect configuration for this data dictionary.

Return type:

DialectConfig

resolve_schema(schema)[source]#

Return a schema name using dialect defaults when missing.

Return type:

str | None

list_available_features()[source]#

List all features that can be checked via get_feature_flag.

Return type:

list[str]

Returns:

List of feature names this data dictionary supports

get_metadata_capabilities(driver, domains=None, *, include_privileged=False, include_diagnostics=False, acknowledge_diagnostics_license=False)[source]#

Report Oracle metadata capabilities and scope gates.

Return type:

MetadataCapabilityProfile

get_system_metadata_capabilities(driver, domains=None)[source]#

Get Oracle opt-in system metadata capability disclosures.

Return type:

tuple[SystemMetadataCapability, ...]

get_ddl(driver, object_name, schema=None, *, object_type='TABLE', include_dependencies=True, prefer_native=True, redact=True)[source]#

Get native Oracle DDL using DBMS_METADATA.

Return type:

DDLResult

get_system_metadata(driver, request=None, **kwargs)[source]#

Return Oracle system metadata only when diagnostics gates are accepted.

Return type:

SystemMetadataResult

get_schemas(driver)[source]#

Get Oracle user/schema metadata.

Return type:

MetadataResult

get_objects(driver, schema=None)[source]#

Get Oracle object metadata from ALL_OBJECTS.

Return type:

MetadataResult

get_table_details(driver, table, schema=None)[source]#

Get rich Oracle table metadata.

Return type:

MetadataResult

get_constraints(driver, table=None, schema=None)[source]#

Get Oracle constraint metadata.

Return type:

MetadataResult

get_views(driver, schema=None)[source]#

Get Oracle view metadata.

Return type:

MetadataResult

get_materialized_views(driver, schema=None)[source]#

Get Oracle materialized view metadata.

Return type:

MetadataResult

get_sequences(driver, schema=None)[source]#

Get Oracle sequence metadata.

Return type:

MetadataResult

get_routines(driver, schema=None)[source]#

Get Oracle routine, package, procedure, and function metadata.

Return type:

MetadataResult

get_triggers(driver, schema=None)[source]#

Get Oracle trigger metadata.

Return type:

MetadataResult

get_privileges(driver, object_name=None, schema=None)[source]#

Get Oracle table and column grants.

Return type:

MetadataResult

get_dependencies(driver, object_name=None, schema=None)[source]#

Get Oracle dependency metadata.

Return type:

MetadataResult

get_partitions(driver, table=None, schema=None)[source]#

Get Oracle partition and storage metadata.

Return type:

MetadataResult

get_lob_storage(driver, table=None, schema=None)[source]#

Get Oracle LOB storage metadata.

Return type:

MetadataResult

get_version(driver)[source]#

Get Oracle database version information through the pool-scoped cache.

Return type:

OracleVersionInfo | None

get_feature_flag(driver, feature)[source]#

Check if Oracle database supports a specific feature.

Return type:

bool

get_optimal_type(driver, type_category)[source]#

Get optimal Oracle type for a category.

Return type:

str

get_tables(driver, schema=None)[source]#

Get tables sorted by dependency order with full coverage.

Return type:

list[TableMetadata]

get_columns(driver, table=None, schema=None)[source]#

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

get_indexes(driver, table=None, schema=None)[source]#

Get index metadata for a table or schema.

Return type:

list[IndexMetadata]

get_foreign_keys(driver, table=None, schema=None)[source]#

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

class sqlspec.adapters.oracledb.data_dictionary.OracledbAsyncDataDictionary[source]#

Bases: AsyncDataDictionaryBase

Oracle-specific async data dictionary.

dialect: ClassVar[str] = 'oracle'#

Dialect identifier. Must be defined by subclasses as a class attribute.

__init__()[source]#

get_dialect_config()[source]#

Return the dialect configuration for this data dictionary.

Return type:

DialectConfig

resolve_schema(schema)[source]#

Return a schema name using dialect defaults when missing.

Return type:

str | None

list_available_features()[source]#

List all features that can be checked via get_feature_flag.

Return type:

list[str]

Returns:

List of feature names this data dictionary supports

async get_metadata_capabilities(driver, domains=None, *, include_privileged=False, include_diagnostics=False, acknowledge_diagnostics_license=False)[source]#

Report Oracle metadata capabilities and scope gates.

Return type:

MetadataCapabilityProfile

async get_system_metadata_capabilities(driver, domains=None)[source]#

Get Oracle opt-in system metadata capability disclosures.

Return type:

tuple[SystemMetadataCapability, ...]

async get_ddl(driver, object_name, schema=None, *, object_type='TABLE', include_dependencies=True, prefer_native=True, redact=True)[source]#

Get native Oracle DDL using DBMS_METADATA.

Return type:

DDLResult

async get_system_metadata(driver, request=None, **kwargs)[source]#

Return Oracle system metadata only when diagnostics gates are accepted.

Return type:

SystemMetadataResult

async get_schemas(driver)[source]#

Get Oracle user/schema metadata.

Return type:

MetadataResult

async get_objects(driver, schema=None)[source]#

Get Oracle object metadata from ALL_OBJECTS.

Return type:

MetadataResult

async get_table_details(driver, table, schema=None)[source]#

Get rich Oracle table metadata.

Return type:

MetadataResult

async get_constraints(driver, table=None, schema=None)[source]#

Get Oracle constraint metadata.

Return type:

MetadataResult

async get_views(driver, schema=None)[source]#

Get Oracle view metadata.

Return type:

MetadataResult

async get_materialized_views(driver, schema=None)[source]#

Get Oracle materialized view metadata.

Return type:

MetadataResult

async get_sequences(driver, schema=None)[source]#

Get Oracle sequence metadata.

Return type:

MetadataResult

async get_routines(driver, schema=None)[source]#

Get Oracle routine, package, procedure, and function metadata.

Return type:

MetadataResult

async get_triggers(driver, schema=None)[source]#

Get Oracle trigger metadata.

Return type:

MetadataResult

async get_privileges(driver, object_name=None, schema=None)[source]#

Get Oracle table and column grants.

Return type:

MetadataResult

async get_dependencies(driver, object_name=None, schema=None)[source]#

Get Oracle dependency metadata.

Return type:

MetadataResult

async get_partitions(driver, table=None, schema=None)[source]#

Get Oracle partition and storage metadata.

Return type:

MetadataResult

async get_lob_storage(driver, table=None, schema=None)[source]#

Get Oracle LOB storage metadata.

Return type:

MetadataResult

async get_version(driver)[source]#

Get Oracle database version information through the pool-scoped cache.

Return type:

OracleVersionInfo | None

async get_feature_flag(driver, feature)[source]#

Check if Oracle database supports a specific feature.

Return type:

bool

async get_optimal_type(driver, type_category)[source]#

Get optimal Oracle type for a category.

Return type:

str

async get_tables(driver, schema=None)[source]#

Get tables sorted by dependency order with full coverage.

Return type:

list[TableMetadata]

async get_columns(driver, table=None, schema=None)[source]#

Get column information for a table or schema.

Return type:

list[ColumnMetadata]

async get_indexes(driver, table=None, schema=None)[source]#

Get index metadata for a table or schema.

Return type:

list[IndexMetadata]

async get_foreign_keys(driver, table=None, schema=None)[source]#

Get foreign key metadata.

Return type:

list[ForeignKeyMetadata]

Migration Trackers#

class sqlspec.adapters.oracledb.migrations.OracleSyncMigrationTracker[source]#

Bases: OracleMigrationTrackerMixin, BaseMigrationTracker[SyncDriverAdapterBase]

Oracle-specific sync migration tracker.

ensure_tracking_table(driver)[source]#

Create the migration tracking table if it doesn't exist.

Uses a PL/SQL block to make the operation atomic and prevent race conditions. Also checks for and adds missing columns to support schema migrations.

Parameters:

driver¶ (SyncDriverAdapterBase) -- The database driver to use.

Return type:

None

get_current_version(driver)[source]#

Get the latest applied migration version.

Parameters:

driver¶ (SyncDriverAdapterBase) -- The database driver to use.

Return type:

str | None

Returns:

The current migration version or None if no migrations applied.

get_applied_migrations(driver)[source]#

Get all applied migrations in order.

Parameters:

driver¶ (SyncDriverAdapterBase) -- The database driver to use.

Return type:

list[AppliedMigrationRecord]

Returns:

List of migration records as dictionaries with lowercase keys.

record_migration(driver, version, description, execution_time_ms, checksum)[source]#

Record a successfully applied migration.

Parameters:

• driver¶ (SyncDriverAdapterBase) -- The database driver to use.

• version¶ (str) -- Version number of the migration.

• description¶ (str) -- Description of the migration.

• execution_time_ms¶ (int) -- Execution time in milliseconds.

• checksum¶ (str) -- MD5 checksum of the migration content.

Return type:

None

remove_migration(driver, version)[source]#

Remove a migration record.

Parameters:

• driver¶ (SyncDriverAdapterBase) -- The database driver to use.

• version¶ (str) -- Version number to remove.

Return type:

None

update_version_record(driver, old_version, new_version, applied_versions=None)[source]#

Update migration version record from timestamp to sequential.

Updates version_num and version_type while preserving execution_sequence, applied_at, and other tracking metadata. Used during fix command.

Idempotent: If the version is already updated, logs and continues without error. This allows fix command to be safely re-run after pulling changes.

Parameters:

• driver¶ (SyncDriverAdapterBase) -- The database driver to use.

• old_version¶ (str) -- Current timestamp version string.

• new_version¶ (str) -- New sequential version string.

• applied_versions¶ (set[str] | None) -- Previously loaded version set for batch reuse.

Raises:

ValueError -- If neither old_version nor new_version found in database.

Return type:

None

class sqlspec.adapters.oracledb.migrations.OracleAsyncMigrationTracker[source]#

Bases: OracleMigrationTrackerMixin, BaseMigrationTracker[AsyncDriverAdapterBase]

Oracle-specific async migration tracker.

async ensure_tracking_table(driver)[source]#

Create the migration tracking table if it doesn't exist.

Uses a PL/SQL block to make the operation atomic and prevent race conditions. Also checks for and adds missing columns to support schema migrations.

Parameters:

driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

Return type:

None

async get_current_version(driver)[source]#

Get the latest applied migration version.

Parameters:

driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

Return type:

str | None

Returns:

The current migration version or None if no migrations applied.

async get_applied_migrations(driver)[source]#

Get all applied migrations in order.

Parameters:

driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

Return type:

list[AppliedMigrationRecord]

Returns:

List of migration records as dictionaries with lowercase keys.

async record_migration(driver, version, description, execution_time_ms, checksum)[source]#

Record a successfully applied migration.

Parameters:

• driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

• version¶ (str) -- Version number of the migration.

• description¶ (str) -- Description of the migration.

• execution_time_ms¶ (int) -- Execution time in milliseconds.

• checksum¶ (str) -- MD5 checksum of the migration content.

Return type:

None

async remove_migration(driver, version)[source]#

Remove a migration record.

Parameters:

• driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

• version¶ (str) -- Version number to remove.

Return type:

None

async update_version_record(driver, old_version, new_version, applied_versions=None)[source]#

Update migration version record from timestamp to sequential.

Updates version_num and version_type while preserving execution_sequence, applied_at, and other tracking metadata. Used during fix command.

Idempotent: If the version is already updated, logs and continues without error. This allows fix command to be safely re-run after pulling changes.

Parameters:

• driver¶ (AsyncDriverAdapterBase) -- The database driver to use.

• old_version¶ (str) -- Current timestamp version string.

• new_version¶ (str) -- New sequential version string.

• applied_versions¶ (set[str] | None) -- Previously loaded version set for batch reuse.

Raises:

ValueError -- If neither old_version nor new_version found in database.

Return type:

None

Extension Settings#

Use the configuration types below in their corresponding extension_config namespace: "litestar", "events", or "adk" as supported by this adapter.

class sqlspec.adapters.oracledb.litestar.OracleLitestarCompressionConfig[source]#

Bases: TypedDict

Oracle session table compression settings.

enabled: NotRequired[bool]#

Enable table compression.

algorithm: NotRequired[str]#

Oracle compression algorithm key.

class sqlspec.adapters.oracledb.litestar.OracleLitestarConfig[source]#

Bases: LitestarConfig

Oracle-specific Litestar settings.

Use inside extension_config["litestar"] with this adapter's session store.

in_memory: NotRequired[bool]#

Enable Oracle INMEMORY table storage.

compression: NotRequired[OracleLitestarCompressionConfig]#

Session table compression settings.

partitioning: NotRequired[OracleLitestarPartitionConfig]#

Session table partition settings.

table_options: NotRequired[str]#

Additional session table DDL options.

class sqlspec.adapters.oracledb.litestar.OracleLitestarPartitionConfig[source]#

Bases: TypedDict

Oracle session table partition settings.

strategy: NotRequired[str]#

hash or range.

Type:

Partition strategy

partition_count: NotRequired[int]#

Hash partition count.

partitions: NotRequired[int]#

Alternative name for partition_count.

interval: NotRequired[str]#

Range partition interval key.

initial_less_than: NotRequired[str]#

Initial range partition upper bound expression.

partition_key: NotRequired[str]#

Partition key column.

session_partition_key: NotRequired[str]#

Session-specific partition key override.

class sqlspec.adapters.oracledb.events.OracleEventsCompressionConfig[source]#

Bases: TypedDict

Oracle queue-table compression settings.

enabled: NotRequired[bool]#

False.

Type:

Enable compression. Default

algorithm: NotRequired[str]#

advanced.

Type:

Oracle compression algorithm key. Default

class sqlspec.adapters.oracledb.events.OracleEventsConfig[source]#

Bases: EventsConfig

Oracle events settings for queue storage and supported native transports.

in_memory: NotRequired[bool]#

False.

Type:

Emit Oracle INMEMORY PRIORITY HIGH. Default

partitioning: NotRequired[OracleEventsPartitionConfig]#

Oracle hash/range queue partition settings; omitted for an unpartitioned table.

compression: NotRequired[OracleEventsCompressionConfig]#

Oracle queue-table compression; disabled unless explicitly enabled.

table_options: NotRequired[str]#

Additional Oracle queue-table DDL options; omitted by default.

json_storage: NotRequired[Literal['json', 'blob_json', 'blob_plain']]#

Override Oracle JSON storage detection with json, blob_json or blob_plain.

aq_queue: NotRequired[str]#

SQLSPEC_EVENTS_QUEUE.

Type:

Oracle AQ queue name or channel template. Default

aq_visibility: NotRequired[int | Literal['DEQ_IMMEDIATE', 'DEQ_ON_COMMIT']]#

Oracle AQ visibility integer or DEQ_IMMEDIATE/DEQ_ON_COMMIT name.

aq_wait_seconds: NotRequired[int]#

Type:

Oracle AQ dequeue wait in seconds. Default

class sqlspec.adapters.oracledb.events.OracleEventsPartitionConfig[source]#

Bases: TypedDict

Oracle queue-table hash or range partition settings.

strategy: NotRequired[str]#

hash or range; omitted to disable partitioning.

Type:

Partitioning strategy

partition_count: NotRequired[int]#

Type:

Hash partition count, at least two. Default

partitions: NotRequired[int]#

Alternative name for partition_count.

interval: NotRequired[str]#

month.

Type:

Range interval

Type:

day, week, month or year. Default

initial_less_than: NotRequired[str]#

00:00'.

Type:

Initial range upper bound. Default

Type:

TIMESTAMP '2000-01-01 00

partition_key: NotRequired[str]#

Partition key; defaults to event_id for hash and available_at for range.

queue_partition_key: NotRequired[str]#

Queue-specific override for partition_key.

class sqlspec.adapters.oracledb.adk.OracleADKCompressionConfig[source]#

Bases: TypedDict

Oracle-specific ADK table compression settings.

enabled: NotRequired[bool]#

Enable Oracle table compression clauses.

algorithm: NotRequired[str]#

Oracle compression algorithm key.

class sqlspec.adapters.oracledb.adk.OracleADKConfig[source]#

Bases: ADKConfig

Oracle ADK extension settings consumed by Oracle ADK stores.

in_memory: NotRequired[bool]#

Enable Oracle INMEMORY clauses on ADK tables.

compression: NotRequired[OracleADKCompressionConfig]#

Oracle ADK table compression settings.

partitioning: NotRequired[OracleADKPartitionConfig]#

Oracle ADK table partitioning settings.

session_table_options: NotRequired[str]#

Raw Oracle table options for the ADK session table.

events_table_options: NotRequired[str]#

Raw Oracle table options for the ADK events table.

app_state_table_options: NotRequired[str]#

Raw Oracle table options for the ADK app state table.

user_state_table_options: NotRequired[str]#

Raw Oracle table options for the ADK user state table.

memory_table_options: NotRequired[str]#

Raw Oracle table options for the ADK memory table.

class sqlspec.adapters.oracledb.adk.OracleADKPartitionConfig[source]#

Bases: TypedDict

Oracle-specific ADK table partitioning settings.

strategy: NotRequired[str]#

Partitioning strategy, such as hash or range.

partition_count: NotRequired[int]#

Hash partition count.

partitions: NotRequired[int]#

Alias for partition_count.

interval: NotRequired[str]#

Range partition interval key.

initial_less_than: NotRequired[str]#

Initial range partition upper bound expression.

partition_key: NotRequired[str]#

Default partition key for all ADK tables.

session_partition_key: NotRequired[str]#

Partition key override for the session table.

events_partition_key: NotRequired[str]#

Partition key override for the events table.

app_state_partition_key: NotRequired[str]#

Partition key override for the app state table.

user_state_partition_key: NotRequired[str]#

Partition key override for the user state table.

memory_partition_key: NotRequired[str]#

Partition key override for the memory table.