Oracle Database Backend¶

Overview¶

Oracle Database is a relational database system commonly used for business-critical applications requiring high reliability and advanced features. The SQLSpec ADK integration provides intelligent version-specific JSON storage that automatically adapts to your Oracle version.

Key Features:

Database Features: ACID compliance, advanced security features, and high availability options
Version-Adaptive JSON Storage: Automatic detection and optimization for Oracle 21c, 12c, and legacy versions
Timezone-Aware: TIMESTAMP WITH TIME ZONE for accurate global timestamps
Connection Pooling: Built-in pool management
Thin & Thick Modes: Choose between pure Python or Oracle Client deployment
Advanced Data Types: BLOB, CLOB, and native JSON support

Ideal Use Cases:

AI agent deployments requiring high reliability
Organizations with existing Oracle infrastructure
Applications requiring advanced security and compliance features
Multi-region deployments with timezone awareness
Business-critical systems requiring high availability

Installation¶

Oracle supports two deployment modes:

Thin Mode (Pure Python - Recommended)¶

Install SQLSpec with Oracle thin mode support:

pip install sqlspec[oracledb] google-genai
# or
uv pip install sqlspec[oracledb] google-genai

Advantages:

No Oracle Client installation required
Smaller deployment footprint
Easier containerization
Cross-platform compatibility
Suitable for most use cases

Thick Mode (Oracle Client)¶

For advanced features requiring Oracle Client libraries:

# 1. Install Oracle Instant Client
# Download from: https://www.oracle.com/database/technologies/instant-client/downloads.html

# 2. Install SQLSpec with Oracle support
pip install sqlspec[oracledb] google-genai

import oracledb

# Initialize thick mode (before creating connections)
oracledb.init_oracle_client(
    lib_dir="/path/to/instantclient"
)

Required For:

Kerberos authentication
LDAP-based authentication
Advanced Oracle Wallet features
Some legacy Oracle features

Tip

Start with thin mode. Switch to thick mode only if you need specific features. Thin mode covers 95% of use cases with zero installation overhead.

Quick Start¶

Async Store (Recommended)¶

from sqlspec.adapters.oracledb import OracleAsyncConfig
from sqlspec.adapters.oracledb.adk import OracleAsyncADKStore
from sqlspec.extensions.adk import SQLSpecSessionService

# Configure Oracle connection
config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "oracle.example.com:1521/XEPDB1",
        "min": 2,
        "max": 10,
    }
)

# Create store and initialize tables
store = OracleAsyncADKStore(config)
await store.create_tables()

# Use with session service
service = SQLSpecSessionService(store)

# Create session
session = await service.create_session(
    app_name="enterprise_agent",
    user_id="user_123",
    state={"context": "active", "priority": "high"}
)

Sync Store¶

from sqlspec.adapters.oracledb import OracleSyncConfig
from sqlspec.adapters.oracledb.adk import OracleSyncADKStore

# Configure Oracle connection
config = OracleSyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "oracle.example.com:1521/XEPDB1",
        "min": 2,
        "max": 10,
    }
)

# Create store and initialize tables
store = OracleSyncADKStore(config)
store.create_tables()

# Use directly
session = store.create_session(
    session_id="unique_id",
    app_name="enterprise_agent",
    user_id="user_123",
    state={"context": "active"}
)

Configuration¶

Connection String Formats¶

Oracle supports multiple DSN (Data Source Name) formats:

Easy Connect (Recommended):

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "hostname:1521/service_name",
    }
)

Easy Connect Plus:

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "tcps://hostname:2484/service_name?ssl_server_cert_dn=CN=server",
    }
)

TNS Connect Descriptor:

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": """(DESCRIPTION=
                    (ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
                    (CONNECT_DATA=(SERVICE_NAME=service_name)))""",
    }
)

TNS Alias (from tnsnames.ora):

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "PROD_DB",  # Name from tnsnames.ora
    }
)

Connection Pool Configuration¶

Oracle connection pooling is mandatory for production:

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "oracle.example.com:1521/XEPDB1",
        "min": 2,                 # Minimum connections (keep warm)
        "max": 10,                # Maximum connections
        "increment": 1,           # How many to add when growing
        "threaded": True,         # Thread-safe pool
        "getmode": oracledb.POOL_GETMODE_WAIT,
    }
)

Pool Parameters:

min: Minimum pool size (keep connections warm)
max: Maximum pool size (prevent resource exhaustion)
increment: How many connections to add when scaling up
threaded: Enable thread safety (required for multi-threaded apps)
getmode: WAIT (block until available) or NOWAIT (error if full)

Custom Table Names¶

Configure custom table names via extension_config:

config = OracleAsyncConfig(
    pool_config={"dsn": "oracle://..."},
    extension_config={
        "adk": {
            "session_table": "agent_sessions",
            "events_table": "agent_events"
        }
    }
)
store = OracleAsyncADKStore(config)

Oracle In-Memory Tables¶

Oracle Database In-Memory Column Store enables dramatic performance improvements for analytics and queries on ADK session data. When enabled, tables are stored in columnar format in memory for 10-100x faster read performance.

Warning

Licensing Required: Oracle Database In-Memory is a separately licensed option for Oracle Database Enterprise Edition:

Oracle Database 12.1.0.2 or higher required
Oracle Database In-Memory option license ($23,000 per processor)
Sufficient INMEMORY_SIZE configured in the database instance

Using in_memory=True without proper licensing will result in ORA-00439 or ORA-62142 errors.

Configuration¶

Enable In-Memory via extension_config:

from sqlspec.adapters.oracledb import OracleAsyncConfig
from sqlspec.adapters.oracledb.adk import OracleAsyncADKStore

config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": "oracle.example.com:1521/XEPDB1",
        "min": 5,
        "max": 20,
    },
    extension_config={
        "adk": {
            "in_memory": True  # Enable In-Memory Column Store
        }
    }
)

store = OracleAsyncADKStore(config)
await store.create_tables()

Generated DDL:

CREATE TABLE adk_sessions (
    id VARCHAR2(128) PRIMARY KEY,
    app_name VARCHAR2(128) NOT NULL,
    user_id VARCHAR2(128) NOT NULL,
    state JSON NOT NULL,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
) INMEMORY;

CREATE TABLE adk_events (
    id VARCHAR2(128) PRIMARY KEY,
    session_id VARCHAR2(128) NOT NULL,
    -- ... other columns ...
    CONSTRAINT fk_adk_events_session FOREIGN KEY (session_id)
        REFERENCES adk_sessions(id) ON DELETE CASCADE
) INMEMORY;

Performance Benefits¶

In-Memory Column Store provides significant performance improvements for:

Analytical queries aggregating session data (COUNT, AVG, SUM)
Complex filtering with JSON state queries
Large scans across thousands or millions of sessions
Real-time analytics on active sessions without impacting OLTP

Performance Comparison:

-- Query 1 million sessions for analytics
SELECT app_name, COUNT(*) as session_count,
       AVG(EXTRACT(EPOCH FROM (update_time - create_time))) as avg_duration_sec
FROM adk_sessions
WHERE create_time >= SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY app_name;

-- Without In-Memory: 8-12 seconds (row format scan)
-- With In-Memory:    200-500 ms (columnar scan with SIMD)

Performance gain: 10-30x faster for typical analytical workloads.

Database Requirements¶

Oracle Version: Oracle Database 12.1.0.2 or higher (19c+ recommended)

Instance Configuration: The database instance must have INMEMORY_SIZE configured:

-- Check current setting
SELECT value FROM v$parameter WHERE name = 'inmemory_size';

-- Set INMEMORY_SIZE (requires restart)
ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
-- Restart database

Minimum Size: At least 100 MB per table (recommend 1-2 GB for production).

Verification¶

After table creation, verify In-Memory status:

async with config.provide_connection() as conn:
    cursor = conn.cursor()

    # Check In-Memory status
    await cursor.execute("""
        SELECT table_name, inmemory, inmemory_priority
        FROM user_tables
        WHERE table_name IN ('ADK_SESSIONS', 'ADK_EVENTS')
    """)

    for row in cursor.fetchall():
        print(f"Table: {row[0]}, In-Memory: {row[1]}, Priority: {row[2]}")

Expected Output:

Table: ADK_SESSIONS, In-Memory: ENABLED, Priority: NONE
Table: ADK_EVENTS, In-Memory: ENABLED, Priority: NONE

Use Cases¶

When to Use In-Memory:

✅ Analytics on session data

Dashboard queries aggregating thousands of sessions
Real-time reporting on AI agent usage patterns
Session duration analysis and user behavior insights

✅ Large-scale deployments

Millions of sessions with frequent analytical queries
Multi-tenant platforms with cross-tenant analytics
Historical session analysis without impacting OLTP performance

✅ Complex filtering

JSON state queries (WHERE JSON_VALUE(state, '$.key') = 'value')
Multi-column filtering across large datasets
Ad-hoc analytics and data science queries

When NOT to Use In-Memory:

❌ Small deployments

< 10,000 sessions (overhead not justified)
Primarily OLTP workload (inserts/updates)
No analytics requirements

❌ Budget constraints

Licensing cost prohibitive ($23K+ per processor)
Can achieve performance through standard indexes

❌ No In-Memory license

Tables will fail to create with ORA-00439 or ORA-62142

Troubleshooting¶

ORA-00439: Feature not enabled: Database In-Memory

Cause: In-Memory option not licensed or not enabled.

Solution:

Verify licensing with Oracle

Check INMEMORY_SIZE is set:

SELECT value FROM v$parameter WHERE name = 'inmemory_size';

If 0, set and restart:

ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;

ORA-62142: INMEMORY column store not available

Cause: Database instance doesn’t have In-Memory configured.

Solution: Same as ORA-00439 - configure INMEMORY_SIZE.

Schema¶

Version-Adaptive JSON Storage¶

The Oracle ADK store automatically detects your Oracle version and uses the optimal JSON storage type:

JSON Storage Evolution¶
Oracle Version	Storage Type	Details
21c+ (compatible >= 20)	Native JSON	`state JSON NOT NULL` - Best performance, native validation
12c - 20c	BLOB with JSON constraint	`state BLOB CHECK (state IS JSON) NOT NULL` - Recommended by Oracle
11g and earlier	BLOB (plain)	`state BLOB NOT NULL` - No validation, maximum compatibility

Note

Version detection happens once at table creation by querying:

product_component_version for Oracle version
v$parameter for compatibility setting

The result is cached to avoid repeated checks.

Sessions Table¶

Oracle 21c+ (Native JSON):

CREATE TABLE adk_sessions (
    id VARCHAR2(128) PRIMARY KEY,
    app_name VARCHAR2(128) NOT NULL,
    user_id VARCHAR2(128) NOT NULL,
    state JSON NOT NULL,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE INDEX idx_adk_sessions_app_user
    ON adk_sessions(app_name, user_id);

CREATE INDEX idx_adk_sessions_update_time
    ON adk_sessions(update_time DESC);

Oracle 12c - 20c (BLOB with JSON Constraint):

CREATE TABLE adk_sessions (
    id VARCHAR2(128) PRIMARY KEY,
    app_name VARCHAR2(128) NOT NULL,
    user_id VARCHAR2(128) NOT NULL,
    state BLOB CHECK (state IS JSON) NOT NULL,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

Oracle 11g and earlier (BLOB):

CREATE TABLE adk_sessions (
    id VARCHAR2(128) PRIMARY KEY,
    app_name VARCHAR2(128) NOT NULL,
    user_id VARCHAR2(128) NOT NULL,
    state BLOB NOT NULL,
    create_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    update_time TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

Events Table¶

Oracle 21c+ (Native JSON):

CREATE TABLE adk_events (
    id VARCHAR2(128) PRIMARY KEY,
    session_id VARCHAR2(128) NOT NULL,
    app_name VARCHAR2(128) NOT NULL,
    user_id VARCHAR2(128) NOT NULL,
    invocation_id VARCHAR2(256),
    author VARCHAR2(256),
    actions BLOB,
    long_running_tool_ids_json CLOB,
    branch VARCHAR2(256),
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    content JSON,
    grounding_metadata JSON,
    custom_metadata JSON,
    partial NUMBER(1),
    turn_complete NUMBER(1),
    interrupted NUMBER(1),
    error_code VARCHAR2(256),
    error_message VARCHAR2(1024),
    CONSTRAINT fk_adk_events_session FOREIGN KEY (session_id)
        REFERENCES adk_sessions(id) ON DELETE CASCADE
);

CREATE INDEX idx_adk_events_session
    ON adk_events(session_id, timestamp ASC);

Oracle 12c - 20c (BLOB with JSON Constraint):

CREATE TABLE adk_events (
    id VARCHAR2(128) PRIMARY KEY,
    session_id VARCHAR2(128) NOT NULL,
    -- ... other fields ...
    content BLOB CHECK (content IS JSON),
    grounding_metadata BLOB CHECK (grounding_metadata IS JSON),
    custom_metadata BLOB CHECK (custom_metadata IS JSON),
    -- ... rest of schema ...
);

Data Type Mappings¶

Python Type	Oracle Type	Notes
`str`	`VARCHAR2(n)`	Text fields
`dict`	`JSON` / `BLOB`	Version-specific
`bytes`	`BLOB`	Actions field
`bool`	`NUMBER(1)`	0 = False, 1 = True
`datetime`	`TIMESTAMP WITH TIME ZONE`	Timezone-aware
`None`	`NULL`	Nullable fields

Important

Boolean Conversion: Oracle doesn’t have a native BOOLEAN type. The store automatically converts:

True → 1
False → 0
None → NULL

Usage Patterns¶

Version Detection¶

The store automatically detects and logs the Oracle version:

store = OracleAsyncADKStore(config)
await store.create_tables()

# Logs output:
# INFO: Detected Oracle 21.3.0.0.0 with compatible >= 20, using JSON_NATIVE
# OR
# INFO: Detected Oracle 19.8.0.0.0, using BLOB_JSON (recommended)
# OR
# INFO: Detected Oracle 11.2.0.4.0 (pre-12c), using BLOB_PLAIN

Session Management¶

# Create session
session = await store.create_session(
    session_id="unique_session_id",
    app_name="enterprise_agent",
    user_id="user_123",
    state={"context": "active", "workflow": "approval"}
)

# Get session
session = await store.get_session("unique_session_id")

# Update state (replaces entire state dict)
await store.update_session_state(
    "unique_session_id",
    {"context": "completed", "result": "approved"}
)

# List sessions for user
sessions = await store.list_sessions("enterprise_agent", "user_123")

# Delete session (cascades to events)
await store.delete_session("unique_session_id")

Event Management¶

Python 3.9+

from datetime import datetime, timezone

# Append event
event = EventRecord(
    id="event_id",
    session_id="session_id",
    app_name="enterprise_agent",
    user_id="user_123",
    author="user",
    actions=b"pickled_actions_data",
    timestamp=datetime.now(timezone.utc),
    content={"message": "User input"},
    partial=False,
    turn_complete=True,
)

await store.append_event(event)

# Get events for session
events = await store.get_events("session_id")

# Get recent events only
from datetime import timedelta
yesterday = datetime.now(timezone.utc) - timedelta(days=1)
recent_events = await store.get_events(
    "session_id",
    after_timestamp=yesterday,
    limit=100
)

Python 3.11+

from datetime import datetime, UTC

# Append event
event = EventRecord(
    id="event_id",
    session_id="session_id",
    app_name="enterprise_agent",
    user_id="user_123",
    author="user",
    actions=b"pickled_actions_data",
    timestamp=datetime.now(UTC),
    content={"message": "User input"},
    partial=False,
    turn_complete=True,
)

await store.append_event(event)

# Get events for session
events = await store.get_events("session_id")

# Get recent events only
from datetime import timedelta, UTC
yesterday = datetime.now(UTC) - timedelta(days=1)
recent_events = await store.get_events(
    "session_id",
    after_timestamp=yesterday,
    limit=100
)

LOB Handling¶

Oracle LOBs (Large Objects) require special handling:

# Store handles LOB reads automatically
session = await store.get_session("session_id")
state = session["state"]  # Automatically deserialized from LOB

# Large JSON documents (> 4KB) are efficiently stored as BLOBs
large_state = {
    "conversation_history": [...],  # Large list
    "user_context": {...},
}
await store.update_session_state("session_id", large_state)

Performance Considerations¶

JSON Storage Types Performance¶

Storage Type	Read Performance	Write Performance	Validation
Native JSON (21c+)	Excellent	Excellent	Built-in
BLOB + IS JSON (12c+)	Very Good	Very Good	Database-enforced
BLOB Plain (11g)	Good	Good	Application-level

Tip

Upgrade Recommendation: If using Oracle 12c-20c, upgrade to 21c+ for native JSON performance improvements.

Connection Pooling Impact¶

Without Pooling (❌ Not Recommended):

Each query creates a new connection
Significant overhead (100-500ms per connection)
Resource exhaustion under load

With Pooling (✅ Recommended):

Reuse warm connections (< 1ms overhead)
Predictable resource usage
Better performance under concurrent load

# Good: Reuse pooled connection
async with config.provide_connection() as conn:
    cursor = conn.cursor()
    await cursor.execute(query1)
    await cursor.execute(query2)  # Same connection
    await conn.commit()

Statement Caching¶

Oracle automatically caches prepared statements:

# Connection-level statement cache
connection.stmtcachesize = 40  # Default is 20

Batch Operations¶

For bulk event inserts, consider batch operations:

# Instead of: (Slow)
for event in events:
    await store.append_event(event)

# Consider: (Faster - if implementing)
# await store.append_events_batch(events)

Best Practices¶

Oracle Version Considerations¶

Oracle 21c+:

✅ Use native JSON features
✅ Leverage JSON query syntax
✅ Benefit from automatic indexing

Oracle 12c - 20c:

✅ BLOB storage with validation is efficient
⚠️ Consider upgrading to 21c for JSON improvements
✅ Check constraints ensure data integrity

Oracle 11g and earlier:

⚠️ No automatic JSON validation
⚠️ Consider upgrading for security and features
✅ Application-level validation still works

Thin vs Thick Mode¶

Prefer Thin Mode When:

✅ Deploying in containers (Docker, Kubernetes)
✅ Using cloud environments
✅ Want zero-install deployment
✅ Standard authentication (user/password)

Use Thick Mode When:

❌ Require Kerberos authentication
❌ Need LDAP-based authentication
❌ Using Oracle Wallet
❌ Need specific legacy features

Security Best Practices¶

# 1. Use environment variables for credentials
import os

config = OracleAsyncConfig(
    pool_config={
        "user": os.environ["ORACLE_USER"],
        "password": os.environ["ORACLE_PASSWORD"],
        "dsn": os.environ["ORACLE_DSN"],
    }
)

# 2. Use Oracle Wallet (thick mode)
oracledb.init_oracle_client()
config = OracleAsyncConfig(
    pool_config={
        "dsn": "wallet_alias",
        # No user/password needed - from wallet
    }
)

# 3. Limit connection pool size
config = OracleAsyncConfig(
    pool_config={
        "max": 10,  # Prevent resource exhaustion
    }
)

Error Handling¶

from oracledb import DatabaseError

try:
    session = await store.get_session("session_id")
except DatabaseError as e:
    error_obj = e.args[0] if e.args else None
    if error_obj:
        if error_obj.code == 942:  # ORA-00942: Table does not exist
            await store.create_tables()
        elif error_obj.code == 1:  # ORA-00001: Unique constraint violated
            # Handle duplicate
            pass

Common Oracle Error Codes¶

ORA-00001: Unique constraint violation
ORA-00054: Resource busy (lock contention)
ORA-00942: Table or view does not exist
ORA-01017: Invalid username/password
ORA-12541: TNS:no listener

Use Cases¶

Enterprise AI Agent Platform¶

from sqlspec.adapters.oracledb import OracleAsyncConfig
from sqlspec.adapters.oracledb.adk import OracleAsyncADKStore
from sqlspec.extensions.adk import SQLSpecSessionService

# Production configuration
config = OracleAsyncConfig(
    pool_config={
        "user": os.environ["ORACLE_USER"],
        "password": os.environ["ORACLE_PASSWORD"],
        "dsn": "prod-oracle.example.com:1521/PROD",
        "min": 5,
        "max": 20,
        "threaded": True,
    }
)

store = OracleAsyncADKStore(config)
await store.create_tables()

service = SQLSpecSessionService(store)

# Handle thousands of concurrent sessions
async def handle_user_request(user_id: str, message: str):
    session = await service.get_or_create_session(
        app_name="enterprise_assistant",
        user_id=user_id,
    )
    # Process with ADK
    # ...

Multi-Region Deployment¶

Oracle’s timezone support ensures correct timestamps across regions:

Python 3.9+

from datetime import datetime, timezone

# Store creates events with timezone-aware timestamps
event = EventRecord(
    id="event_id",
    session_id="session_id",
    timestamp=datetime.now(timezone.utc),  # UTC
    # ...
)

await store.append_event(event)

# Timestamps are preserved with timezone information
events = await store.get_events("session_id")
for event in events:
    local_time = event["timestamp"].astimezone()  # Convert to local

Python 3.11+

from datetime import datetime, UTC

# Store creates events with timezone-aware timestamps
event = EventRecord(
    id="event_id",
    session_id="session_id",
    timestamp=datetime.now(UTC),  # UTC
    # ...
)

await store.append_event(event)

# Timestamps are preserved with timezone information
events = await store.get_events("session_id")
for event in events:
    local_time = event["timestamp"].astimezone()  # Convert to local

High-Availability Setup¶

# Oracle RAC (Real Application Clusters)
config = OracleAsyncConfig(
    pool_config={
        "user": "agent_user",
        "password": "secure_password",
        "dsn": """(DESCRIPTION=
                    (ADDRESS_LIST=
                      (ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521))
                      (ADDRESS=(PROTOCOL=TCP)(HOST=node2)(PORT=1521))
                      (LOAD_BALANCE=yes)
                      (FAILOVER=yes))
                    (CONNECT_DATA=(SERVICE_NAME=PROD)))""",
    }
)

Troubleshooting¶

Version Detection Issues¶

If version detection fails:

# Check Oracle version manually
async with config.provide_connection() as conn:
    cursor = conn.cursor()
    await cursor.execute("""
        SELECT version FROM product_component_version
        WHERE product LIKE 'Oracle%Database%'
    """)
    version = await cursor.fetchone()
    print(f"Oracle version: {version[0]}")

Solution: The store defaults to BLOB_JSON (safe for 12c+) if detection fails.

JSON Storage Problems¶

Symptom: ORA-02290: check constraint violated

Cause: Invalid JSON in BLOB with IS JSON constraint.

Solution: Ensure data is valid JSON before storing:

import json

# Validate JSON
state = {"key": "value"}
json.dumps(state)  # Raises exception if invalid

await store.update_session_state("session_id", state)

Connection Errors¶

ORA-12541: TNS:no listener

Solutions:

Verify Oracle listener is running: lsnrctl status
Check firewall rules
Verify DSN format

ORA-01017: Invalid username/password

Solutions:

Verify credentials
Check user account is unlocked: ALTER USER agent_user ACCOUNT UNLOCK;
Verify user has necessary privileges

Required Privileges¶

Grant minimum required privileges:

-- Create user
CREATE USER agent_user IDENTIFIED BY secure_password;

-- Grant basic privileges
GRANT CREATE SESSION TO agent_user;
GRANT CREATE TABLE TO agent_user;
GRANT CREATE INDEX TO agent_user;

-- Grant quota on tablespace
ALTER USER agent_user QUOTA UNLIMITED ON USERS;

-- Grant privileges on tables (if already created)
GRANT SELECT, INSERT, UPDATE, DELETE ON adk_sessions TO agent_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON adk_events TO agent_user;

Comparison with Other Backends¶

Oracle vs PostgreSQL¶

Feature	Oracle	PostgreSQL
JSON Storage	Native JSON (21c+), BLOB (12c+)	Native JSONB with GIN indexes
Enterprise Features	RAC, Data Guard, Partitioning	Streaming replication, logical replication
Licensing	Commercial (paid)	Open source (free)
Deployment	Complex setup	Simpler setup
Performance	Excellent (enterprise-tuned)	Excellent (open source)
Best For	Existing Oracle shops, enterprise	New deployments, cost-sensitive

Oracle vs DuckDB¶

Feature	Oracle	DuckDB
Deployment	Client-server	Embedded (single file)
Concurrency	Excellent	Limited writes
Use Case	Production AI agents	Development, analytics
Setup	Complex	Zero config
Cost	Commercial license	Free, open source

When to Choose Oracle¶

Choose Oracle When:

✅ Already using Oracle infrastructure ✅ Require enterprise support and SLAs ✅ Need advanced HA features (RAC, Data Guard) ✅ Compliance requires certified databases ✅ Multi-region deployments with global transactions

Choose Alternatives When:

❌ Starting fresh (use PostgreSQL) ❌ Cost-sensitive (use PostgreSQL) ❌ Development/testing (use DuckDB or SQLite) ❌ Small-scale deployment (use PostgreSQL or DuckDB)

API Reference¶

Async Store¶

class sqlspec.adapters.oracledb.adk.OracleAsyncADKStore[source]

Bases: BaseAsyncADKStore[OracleAsyncConfig]

Oracle async ADK store using oracledb async driver.

Implements session and event storage for Google Agent Development Kit using Oracle Database via the python-oracledb async driver. Provides: - Session state management with version-specific JSON storage - Event history tracking with BLOB-serialized actions - TIMESTAMP WITH TIME ZONE for timezone-aware timestamps - Foreign key constraints with cascade delete - Efficient upserts using MERGE statement

Parameters:: config¶ (OracleAsyncConfig) – OracleAsyncConfig with extension_config[“adk”] settings.

Example

from sqlspec.adapters.oracledb import OracleAsyncConfig from sqlspec.adapters.oracledb.adk import OracleAsyncADKStore

config = OracleAsyncConfig(

pool_config={“dsn”: “oracle://…”}, extension_config={

“adk”: {
“session_table”: “my_sessions”, “events_table”: “my_events”, “owner_id_column”: “tenant_id NUMBER(10) REFERENCES tenants(id)”

}

}

) store = OracleAsyncADKStore(config) await store.create_tables()

Notes

JSON storage type detected based on Oracle version (21c+, 12c+, legacy)
BLOB for pre-serialized actions from Google ADK
TIMESTAMP WITH TIME ZONE for timezone-aware timestamps
NUMBER(1) for booleans (0/1/NULL)
Named parameters using :param_name
State merging handled at application level
owner_id_column supports NUMBER, VARCHAR2, RAW for Oracle FK types
Configuration is read from config.extension_config[“adk”]

__init__(config)[source]

Initialize Oracle ADK store.

Parameters:: config¶ (OracleAsyncConfig) – OracleAsyncConfig instance.

Notes

Configuration is read from config.extension_config[“adk”]: - session_table: Sessions table name (default: “adk_sessions”) - events_table: Events table name (default: “adk_events”) - owner_id_column: Optional owner FK column DDL (default: None) - in_memory: Enable INMEMORY PRIORITY HIGH clause (default: False)

async create_tables()[source]

Create both sessions and events tables if they don’t exist.

Notes

Detects Oracle version to determine optimal JSON storage type. Uses version-appropriate table schema.

Return type:: None

async create_session(session_id, app_name, user_id, state, owner_id=None)[source]

Create a new session.

Parameters:

session_id¶ (str) – Unique session identifier.
app_name¶ (str) – Application name.
user_id¶ (str) – User identifier.
state¶ (dict[str, typing.Any]) – Initial session state.
owner_id¶ (Optional[typing.Any]) – Optional owner ID value for owner_id_column (if configured).

Return type:

SessionRecord

Returns:

Created session record.

Notes

Uses SYSTIMESTAMP for create_time and update_time. State is serialized using version-appropriate format. owner_id is ignored if owner_id_column not configured.

async get_session(session_id)[source]

Get session by ID.

Parameters:: session_id¶ (str) – Session identifier.
Return type:: SessionRecord | None
Returns:: Session record or None if not found.

Notes

Oracle returns datetime objects for TIMESTAMP columns. State is deserialized using version-appropriate format.

async update_session_state(session_id, state)[source]

Update session state.

Parameters:

session_id¶ (str) – Session identifier.
state¶ (dict[str, typing.Any]) – New state dictionary (replaces existing state).

Return type:

None

Notes

This replaces the entire state dictionary. Updates update_time to current timestamp. State is serialized using version-appropriate format.

async delete_session(session_id)[source]

Delete session and all associated events (cascade).

Parameters:: session_id¶ (str) – Session identifier.
Return type:: None

Notes

Foreign key constraint ensures events are cascade-deleted.

async list_sessions(app_name, user_id=None)[source]

List sessions for an app, optionally filtered by user.

Parameters:

app_name¶ (str) – Application name.
user_id¶ (str | None) – User identifier. If None, lists all sessions for the app.

Return type:

list[SessionRecord]

Returns:

List of session records ordered by update_time DESC.

Notes

Uses composite index on (app_name, user_id) when user_id is provided. State is deserialized using version-appropriate format.

async append_event(event_record)[source]

Append an event to a session.

Parameters:: event_record¶ (EventRecord) – Event record to store.
Return type:: None

Notes

Uses SYSTIMESTAMP for timestamp if not provided. JSON fields are serialized using version-appropriate format. Boolean fields are converted to NUMBER(1).

async get_events(session_id, after_timestamp=None, limit=None)[source]

Get events for a session.

Parameters:

session_id¶ (str) – Session identifier.
after_timestamp¶ (datetime | None) – Only return events after this time.
limit¶ (int | None) – Maximum number of events to return.

Return type:

list[EventRecord]

Returns:

List of event records ordered by timestamp ASC.

Notes

Uses index on (session_id, timestamp ASC). JSON fields deserialized using version-appropriate format. Converts BLOB actions to bytes and NUMBER(1) booleans to Python bool.

property config: ConfigT: Return the database configuration.

property events_table: str: Return the events table name.

property owner_id_column_ddl: str | None: Return the full owner ID column DDL (or None if not configured).

property owner_id_column_name: str | None: Return the owner ID column name only (or None if not configured).

property session_table: str: Return the sessions table name.

Sync Store¶

class sqlspec.adapters.oracledb.adk.OracleSyncADKStore[source]

Bases: BaseSyncADKStore[OracleSyncConfig]

Oracle synchronous ADK store using oracledb sync driver.

Implements session and event storage for Google Agent Development Kit using Oracle Database via the python-oracledb synchronous driver. Provides: - Session state management with version-specific JSON storage - Event history tracking with BLOB-serialized actions - TIMESTAMP WITH TIME ZONE for timezone-aware timestamps - Foreign key constraints with cascade delete - Efficient upserts using MERGE statement

Parameters:: config¶ (OracleSyncConfig) – OracleSyncConfig with extension_config[“adk”] settings.

Example

from sqlspec.adapters.oracledb import OracleSyncConfig from sqlspec.adapters.oracledb.adk import OracleSyncADKStore

config = OracleSyncConfig(

pool_config={“dsn”: “oracle://…”}, extension_config={

“adk”: {
“session_table”: “my_sessions”, “events_table”: “my_events”, “owner_id_column”: “account_id NUMBER(19) REFERENCES accounts(id)”

}

}

) store = OracleSyncADKStore(config) store.create_tables()

Notes

JSON storage type detected based on Oracle version (21c+, 12c+, legacy)
BLOB for pre-serialized actions from Google ADK
TIMESTAMP WITH TIME ZONE for timezone-aware timestamps
NUMBER(1) for booleans (0/1/NULL)
Named parameters using :param_name
State merging handled at application level
owner_id_column supports NUMBER, VARCHAR2, RAW for Oracle FK types
Configuration is read from config.extension_config[“adk”]

__init__(config)[source]

Initialize Oracle synchronous ADK store.

Parameters:: config¶ (OracleSyncConfig) – OracleSyncConfig instance.

Notes

create_tables()[source]

Create both sessions and events tables if they don’t exist.

Notes

Detects Oracle version to determine optimal JSON storage type. Uses version-appropriate table schema.

Return type:: None

create_session(session_id, app_name, user_id, state, owner_id=None)[source]

Create a new session.

Parameters:

session_id¶ (str) – Unique session identifier.
app_name¶ (str) – Application name.
user_id¶ (str) – User identifier.
state¶ (dict[str, typing.Any]) – Initial session state.
owner_id¶ (Optional[typing.Any]) – Optional owner ID value for owner_id_column (if configured).

Return type:

SessionRecord

Returns:

Created session record.

Notes

Uses SYSTIMESTAMP for create_time and update_time. State is serialized using version-appropriate format. owner_id is ignored if owner_id_column not configured.

get_session(session_id)[source]

Get session by ID.

Parameters:: session_id¶ (str) – Session identifier.
Return type:: SessionRecord | None
Returns:: Session record or None if not found.

Notes

Oracle returns datetime objects for TIMESTAMP columns. State is deserialized using version-appropriate format.

update_session_state(session_id, state)[source]

Update session state.

Parameters:

session_id¶ (str) – Session identifier.
state¶ (dict[str, typing.Any]) – New state dictionary (replaces existing state).

Return type:

None

Notes

This replaces the entire state dictionary. Updates update_time to current timestamp. State is serialized using version-appropriate format.

delete_session(session_id)[source]

Delete session and all associated events (cascade).

Parameters:: session_id¶ (str) – Session identifier.
Return type:: None

Notes

Foreign key constraint ensures events are cascade-deleted.

list_sessions(app_name, user_id=None)[source]

List sessions for an app, optionally filtered by user.

Parameters:

app_name¶ (str) – Application name.
user_id¶ (str | None) – User identifier. If None, lists all sessions for the app.

Return type:

list[SessionRecord]

Returns:

List of session records ordered by update_time DESC.

Notes

Uses composite index on (app_name, user_id) when user_id is provided. State is deserialized using version-appropriate format.

create_event(event_id, session_id, app_name, user_id, author=None, actions=None, content=None, **kwargs)[source]

Create a new event.

Parameters:

event_id¶ (str) – Unique event identifier.
session_id¶ (str) – Session identifier.
app_name¶ (str) – Application name.
user_id¶ (str) – User identifier.
author¶ (str | None) – Event author (user/assistant/system).
actions¶ (bytes | None) – Pickled actions object.
content¶ (dict[str, typing.Any] | None) – Event content (JSONB/JSON).
**kwargs¶ (Any) – Additional optional fields.

Return type:

EventRecord

Returns:

Created event record.

Notes

Uses SYSTIMESTAMP for timestamp if not provided. JSON fields are serialized using version-appropriate format. Boolean fields are converted to NUMBER(1).

list_events(session_id)[source]

List events for a session ordered by timestamp.

Parameters:: session_id¶ (str) – Session identifier.
Return type:: list[EventRecord]
Returns:: List of event records ordered by timestamp ASC.

Notes

Uses index on (session_id, timestamp ASC). JSON fields deserialized using version-appropriate format. Converts BLOB actions to bytes and NUMBER(1) booleans to Python bool.

property config: ConfigT: Return the database configuration.

property events_table: str: Return the events table name.

property owner_id_column_ddl: str | None: Return the full owner ID column DDL (or None if not configured).

property owner_id_column_name: str | None: Return the owner ID column name only (or None if not configured).

property session_table: str: Return the sessions table name.

Oracle Database Backend¶

Overview¶

Installation¶

Thin Mode (Pure Python - Recommended)¶

Thick Mode (Oracle Client)¶

Quick Start¶

Async Store (Recommended)¶

Sync Store¶

Configuration¶

Connection String Formats¶

Connection Pool Configuration¶

Custom Table Names¶

Oracle In-Memory Tables¶

Configuration¶

Performance Benefits¶

Database Requirements¶

Verification¶

Use Cases¶

Troubleshooting¶

Schema¶

Version-Adaptive JSON Storage¶

Sessions Table¶

Events Table¶

Data Type Mappings¶

Usage Patterns¶

Version Detection¶

Session Management¶

Event Management¶

LOB Handling¶

Performance Considerations¶

JSON Storage Types Performance¶

Connection Pooling Impact¶

Statement Caching¶

Batch Operations¶

Best Practices¶

Oracle Version Considerations¶

Thin vs Thick Mode¶

Security Best Practices¶

Error Handling¶

Common Oracle Error Codes¶

Use Cases¶

Enterprise AI Agent Platform¶

Multi-Region Deployment¶

High-Availability Setup¶

Troubleshooting¶

Version Detection Issues¶

JSON Storage Problems¶

Connection Errors¶

Required Privileges¶

Comparison with Other Backends¶

Oracle vs PostgreSQL¶

Oracle vs DuckDB¶

When to Choose Oracle¶

API Reference¶

Async Store¶

Sync Store¶

See Also¶