Skip to main content

​
Overview

The Oracle dialect provides comprehensive support for Oracle Database, including advanced features like sequences, IDENTITY columns (Oracle 12+), and Oracle-specific types.
Version Support
  • Normal support: Oracle Database 11+
  • Best effort: Oracle Database 9+

​
Installation

Oracle is no longer releasing updates in the cx_Oracle namespace. Use python-oracledb for new projects.

​
Connecting to Oracle

​
Connection Strings

from sqlalchemy import create_engine

engine = create_engine(
    "oracle+oracledb://user:pass@hostname:1521/?service_name=FREEPDB1"
)

​
Thin vs Thick Mode

python-oracledb supports two modes:

​
Thin Mode (Default)

No Oracle Client libraries required: 
engine = create_engine(
    "oracle+oracledb://user:pass@host/?service_name=FREEPDB1"
)
# Thin mode is automatic

​
Thick Mode

Uses Oracle Client libraries for advanced features: 
engine = create_engine(
    "oracle+oracledb://user:pass@host/?service_name=FREEPDB1",
    thick_mode=True
)
Thick mode provides features like:
  • Oracle Application Continuity
  • Advanced security features
  • Oracle Net Services configuration files
  • Oracle Wallet support

​
Auto Increment Behavior

​
IDENTITY Columns (Oracle 12+)

Recommended for Oracle 12 and above: 
from sqlalchemy import Table, Column, Integer, String, Identity

mytable = Table(
    'mytable',
    metadata,
    Column('id', Integer, Identity(start=3), primary_key=True),
    Column('name', String(50))
)
Generates: 
CREATE TABLE mytable (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 3),
    name VARCHAR2(50),
    PRIMARY KEY (id)
)

​
Oracle-Specific Identity Options

Column(
    'id',
    Integer,
    Identity(
        start=1,
        increment=1,
        minvalue=1,
        maxvalue=999999,
        cycle=True,
        cache=20,
        order=None,  # Use True for ordered (Oracle RAC)
        on_null=None  # Oracle-specific: True for ON NULL
    ),
    primary_key=True
)

​
Sequences (All Oracle Versions)

For Oracle 11 and earlier: 
from sqlalchemy import Sequence

mytable = Table(
    'mytable',
    metadata,
    Column(
        'id',
        Integer,
        Sequence('id_seq', start=1),
        primary_key=True
    ),
    Column('name', String(50))
)
When using table reflection with sequences, you must explicitly specify the sequence:
mytable = Table(
    'mytable',
    metadata,
    Column('id', Integer, Sequence('id_seq'), primary_key=True),
    autoload_with=engine
)

​
Oracle-Specific Types

​
String Types

from sqlalchemy.dialects.oracle import (
    VARCHAR,     # Deprecated, use VARCHAR2
    VARCHAR2,    # Variable-length string (Oracle standard)
    NVARCHAR,    # Deprecated, use NVARCHAR2
    NVARCHAR2,   # National variable-length string
    CHAR,        # Fixed-length string
    NCHAR        # National fixed-length string
)

users = Table(
    'users',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('username', VARCHAR2(50)),
    Column('code', CHAR(10)),
    Column('unicode_name', NVARCHAR2(100))
)

​
Large Object Types

from sqlalchemy.dialects.oracle import CLOB, NCLOB, BLOB, BFILE

documents = Table(
    'documents',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('text_content', CLOB),        # Character LOB
    Column('unicode_content', NCLOB),    # National Character LOB
    Column('binary_data', BLOB),         # Binary LOB
    Column('external_file', BFILE)       # External file reference
)

​
Numeric Types

from sqlalchemy.dialects.oracle import (
    NUMBER,           # Oracle's numeric type
    FLOAT,            # Floating point
    BINARY_DOUBLE,    # IEEE double precision
    BINARY_FLOAT,     # IEEE single precision
    DOUBLE_PRECISION  # Double precision
)

scores = Table(
    'scores',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('value', NUMBER(10, 2)),      # NUMBER(precision, scale)
    Column('exact', BINARY_DOUBLE),
    Column('approximate', FLOAT)
)

​
Date and Time Types

from sqlalchemy.dialects.oracle import DATE, TIMESTAMP, INTERVAL
from datetime import datetime, timedelta

events = Table(
    'events',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('created_date', DATE),
    Column('created_ts', TIMESTAMP),
    Column('duration', INTERVAL)  # INTERVAL DAY TO SECOND
)

​
Other Oracle Types

from sqlalchemy.dialects.oracle import (
    RAW,      # Raw binary data
    LONG,     # Long text (deprecated, use CLOB)
    ROWID,    # Row identifier
    BOOLEAN   # Oracle 23c+
)

​
Vector Types (Oracle 23c+)

from sqlalchemy.dialects.oracle import (
    VECTOR,
    VectorIndexType,
    VectorIndexConfig,
    VectorStorageFormat,
    SparseVector
)

vector_data = Table(
    'embeddings',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('embedding', VECTOR(1536))  # 1536-dimensional vector
)

​
Returning Clause

Oracle supports RETURNING clause for INSERT, UPDATE, and DELETE: 
from sqlalchemy import insert

stmt = insert(users).values(name='Alice').returning(users.c.id)
result = connection.execute(stmt)
inserted_id = result.scalar()

# Multiple columns
stmt = insert(users).values(
    name='Bob'
).returning(users.c.id, users.c.name)
result = connection.execute(stmt)
row = result.fetchone()

​
LIMIT and OFFSET

Oracle uses ROW_NUMBER() for pagination (Oracle 11 and below): 
# SQLAlchemy handles this automatically
stmt = select(users).limit(10).offset(20)

# Oracle 12+: Uses OFFSET/FETCH FIRST
# Oracle 11 and below: Uses ROW_NUMBER() OVER ()

​
WITH Clause (CTEs)

Common Table Expressions: 
from sqlalchemy import select

# Define CTE
recent_orders = select(
    orders.c.customer_id,
    func.count().label('order_count')
).where(
    orders.c.created > '2024-01-01'
).group_by(
    orders.c.customer_id
).cte('recent_orders')

# Use CTE
stmt = select(
    customers.c.name,
    recent_orders.c.order_count
).join(
    recent_orders,
    customers.c.id == recent_orders.c.customer_id
)

​
Synonyms

Querying Oracle synonyms: 
from sqlalchemy import Table

# Reflect a synonym like a regular table
synonym_table = Table('my_synonym', metadata, autoload_with=engine)
Access remote databases: 
from sqlalchemy import Table

# Reference table via database link
remote_table = Table(
    'remote_table@dblink',
    metadata,
    autoload_with=engine
)

​
Connection Pooling

Oracle-specific pooling configuration: 
engine = create_engine(
    "oracle+oracledb://user:pass@host/?service_name=XE",
    pool_size=10,
    max_overflow=20,
    pool_recycle=3600,
    pool_pre_ping=True
)

​
Two-Phase Commit

python-oracledb supports two-phase commit: 
from sqlalchemy import create_engine

engine = create_engine(
    "oracle+oracledb://...",
    use_twophase=True
)

# Use with distributed transactions
with engine.begin_twophase() as connection:
    connection.execute(users.insert().values(name='Alice'))
    connection.execute(orders.insert().values(user_id=1))

​
Case Sensitivity

Oracle converts unquoted identifiers to uppercase: 
# These are equivalent:
Table('users', metadata, ...)        # Stored as USERS
Table('USERS', metadata, ...)        # Stored as USERS

# Force case-sensitivity with quotes:
Table('Users', metadata, ...)        # Stored as "Users"
For maximum compatibility, use lowercase names everywhere and let Oracle uppercase them automatically.

​
Schema Specification

# Specify schema (Oracle user/owner)
users = Table(
    'users',
    metadata,
    Column('id', Integer, primary_key=True),
    schema='HR'
)

# Reflects as HR.USERS

​
Performance Tips

  1. Use bind parameters (SQLAlchemy does this automatically)
  2. Enable statement caching in the driver
  3. Use connection pooling with appropriate pool size
  4. Create proper indexes on frequently queried columns
  5. Use EXPLAIN PLAN to analyze query performance
  6. Consider partitioning for large tables
  7. Use bulk operations for large inserts/updates

​
Common Issues

​
ORA-01000: Maximum Open Cursors Exceeded

Increase open_cursors or ensure cursors are closed: 
# Increase open_cursors in Oracle
ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

# Or ensure proper connection management
with engine.connect() as conn:
    result = conn.execute(stmt)
    # Connection and cursors automatically closed

​
TNS: Could Not Resolve Service Name

Ensure tnsnames.ora is configured or use Easy Connect: 
# Use Easy Connect instead of TNS name
engine = create_engine(
    "oracle+oracledb://user:pass@host:1521/?service_name=XE"
)

​
See Also

Dialect Overview

Learn about SQLAlchemy’s dialect system

python-oracledb Documentation

Official driver documentation