sqlalchemy.databases.oracle

Auto Increment Behavior

SQLAlchemy Table objects which include integer primary keys are usually assumed to have "autoincrementing" behavior, meaning they can generate their own primary key values upon INSERT. For use within Oracle, two options are available, which are the use of IDENTITY columns (Oracle 12 and above only) or the association of a SEQUENCE with the column.

t = Table('mytable', metadata,
    Column('id', Integer, Identity(start=3), primary_key=True),
    Column(...), ...
)

CREATE TABLE mytable (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 3),
    ...,
    PRIMARY KEY (id)
)

t = Table('mytable', metadata,
      Column('id', Integer, Sequence('id_seq'), primary_key=True),
      Column(...), ...
)

t = Table('mytable', metadata,
      Column('id', Integer, Sequence('id_seq'), primary_key=True),
      autoload_with=engine
)

Transaction Isolation Level / Autocommit

The Oracle database supports "READ COMMITTED" and "SERIALIZABLE" modes of isolation. The AUTOCOMMIT isolation level is also supported by the cx_Oracle dialect.

To set using per-connection execution options:

connection = engine.connect()
connection = connection.execution_options(
    isolation_level="AUTOCOMMIT"
)

For READ COMMITTED and SERIALIZABLE, the Oracle dialect sets the level at the session level using ALTER SESSION, which is reverted back to its default setting when the connection is returned to the connection pool.

Valid values for isolation_level include:

• READ COMMITTED
• AUTOCOMMIT
• SERIALIZABLE

Identifier Casing

In Oracle, the data dictionary represents all case insensitive identifier names using UPPERCASE text. SQLAlchemy on the other hand considers an all-lower case identifier name to be case insensitive. The Oracle dialect converts all case insensitive identifiers to and from those two formats during schema level communication, such as reflection of tables and indexes. Using an UPPERCASE name on the SQLAlchemy side indicates a case sensitive identifier, and SQLAlchemy will quote the name - this will cause mismatches against data dictionary data received from Oracle, so unless identifier names have been truly created as case sensitive (i.e. using quoted names), all lowercase names should be used on the SQLAlchemy side.

Max Identifier Lengths

Oracle has changed the default max identifier length as of Oracle Server version 12.2. Prior to this version, the length was 30, and for 12.2 and greater it is now 128. This change impacts SQLAlchemy in the area of generated SQL label names as well as the generation of constraint names, particularly in the case where the constraint naming convention feature described at :ref:`constraint_naming_conventions` is being used.

To assist with this change and others, Oracle includes the concept of a "compatibility" version, which is a version number that is independent of the actual server version in order to assist with migration of Oracle databases, and may be configured within the Oracle server itself. This compatibility version is retrieved using the query SELECT value FROM v$parameter WHERE name = 'compatible';. The SQLAlchemy Oracle dialect, when tasked with determining the default max identifier length, will attempt to use this query upon first connect in order to determine the effective compatibility version of the server, which determines what the maximum allowed identifier length is for the server. If the table is not available, the server version information is used instead.

As of SQLAlchemy 1.4, the default max identifier length for the Oracle dialect is 128 characters. Upon first connect, the compatibility version is detected and if it is less than Oracle version 12.2, the max identifier length is changed to be 30 characters. In all cases, setting the :paramref:`_sa.create_engine.max_identifier_length` parameter will bypass this change and the value given will be used as is:

engine = create_engine(
    "oracle+cx_oracle://scott:tiger@oracle122",
    max_identifier_length=30)

The maximum identifier length comes into play both when generating anonymized SQL labels in SELECT statements, but more crucially when generating constraint names from a naming convention. It is this area that has created the need for SQLAlchemy to change this default conservatively. For example, the following naming convention produces two very different constraint names based on the identifier length:

from sqlalchemy import Column
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy.dialects import oracle
from sqlalchemy.schema import CreateIndex

m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})

t = Table(
    "t",
    m,
    Column("some_column_name_1", Integer),
    Column("some_column_name_2", Integer),
    Column("some_column_name_3", Integer),
)

ix = Index(
    None,
    t.c.some_column_name_1,
    t.c.some_column_name_2,
    t.c.some_column_name_3,
)

oracle_dialect = oracle.dialect(max_identifier_length=30)
print(CreateIndex(ix).compile(dialect=oracle_dialect))

With an identifier length of 30, the above CREATE INDEX looks like:

CREATE INDEX ix_some_column_name_1s_70cd ON t
(some_column_name_1, some_column_name_2, some_column_name_3)

However with length=128, it becomes:

CREATE INDEX ix_some_column_name_1some_column_name_2some_column_name_3 ON t
(some_column_name_1, some_column_name_2, some_column_name_3)

Applications which have run versions of SQLAlchemy prior to 1.4 on an Oracle server version 12.2 or greater are therefore subject to the scenario of a database migration that wishes to "DROP CONSTRAINT" on a name that was previously generated with the shorter length. This migration will fail when the identifier length is changed without the name of the index or constraint first being adjusted. Such applications are strongly advised to make use of :paramref:`_sa.create_engine.max_identifier_length` in order to maintain control of the generation of truncated names, and to fully review and test all database migrations in a staging environment when changing this value to ensure that the impact of this change has been mitigated.

LIMIT/OFFSET Support

Oracle has no direct support for LIMIT and OFFSET until version 12c. To achieve this behavior across all widely used versions of Oracle starting with the 8 series, SQLAlchemy currently makes use of ROWNUM to achieve LIMIT/OFFSET; the exact methodology is taken from https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results .

There is currently a single option to affect its behavior:

• the "FIRST_ROWS()" optimization keyword is not used by default. To enable the usage of this optimization directive, specify optimize_limits=True to _sa.create_engine.

Support for changing the row number strategy, which would include one that makes use of the row_number() window function as well as one that makes use of the Oracle 12c "FETCH FIRST N ROW / OFFSET N ROWS" keywords may be added in a future release.

RETURNING Support

The Oracle database supports a limited form of RETURNING, in order to retrieve result sets of matched rows from INSERT, UPDATE and DELETE statements. Oracle's RETURNING..INTO syntax only supports one row being returned, as it relies upon OUT parameters in order to function. In addition, supported DBAPIs have further limitations (see :ref:`cx_oracle_returning`).

SQLAlchemy's "implicit returning" feature, which employs RETURNING within an INSERT and sometimes an UPDATE statement in order to fetch newly generated primary key values and other SQL defaults and expressions, is normally enabled on the Oracle backend. By default, "implicit returning" typically only fetches the value of a single nextval(some_seq) expression embedded into an INSERT in order to increment a sequence within an INSERT statement and get the value back at the same time. To disable this feature across the board, specify implicit_returning=False to _sa.create_engine:

engine = create_engine("oracle://scott:tiger@dsn",
                       implicit_returning=False)

Implicit returning can also be disabled on a table-by-table basis as a table option:

# Core Table
my_table = Table("my_table", metadata, ..., implicit_returning=False)


# declarative
class MyClass(Base):
    __tablename__ = 'my_table'
    __table_args__ = {"implicit_returning": False}

ON UPDATE CASCADE

Oracle doesn't have native ON UPDATE CASCADE functionality. A trigger based solution is available at https://asktom.oracle.com/tkyte/update_cascade/index.html .

When using the SQLAlchemy ORM, the ORM has limited ability to manually issue cascading updates - specify ForeignKey objects using the "deferrable=True, initially='deferred'" keyword arguments, and specify "passive_updates=False" on each relationship().

Oracle 8 Compatibility

When Oracle 8 is detected, the dialect internally configures itself to the following behaviors:

• the use_ansi flag is set to False. This has the effect of converting all JOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOIN makes use of Oracle's (+) operator.
• the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL when the ~sqlalchemy.types.Unicode is used - VARCHAR2 and CLOB are issued instead. This because these types don't seem to work correctly on Oracle 8 even though they are available. The ~sqlalchemy.types.NVARCHAR and ~sqlalchemy.dialects.oracle.NCLOB types will always generate NVARCHAR2 and NCLOB.
• the "native unicode" mode is disabled when using cx_oracle, i.e. SQLAlchemy encodes all Python unicode objects to "string" before passing in as bind parameters.

Synonym/DBLINK Reflection

When using reflection with Table objects, the dialect can optionally search for tables indicated by synonyms, either in local or remote schemas or accessed over DBLINK, by passing the flag oracle_resolve_synonyms=True as a keyword argument to the _schema.Table construct:

some_table = Table('some_table', autoload_with=some_engine,
                            oracle_resolve_synonyms=True)

When this flag is set, the given name (such as some_table above) will be searched not just in the ALL_TABLES view, but also within the ALL_SYNONYMS view to see if this name is actually a synonym to another name. If the synonym is located and refers to a DBLINK, the oracle dialect knows how to locate the table's information using DBLINK syntax(e.g. @dblink).

oracle_resolve_synonyms is accepted wherever reflection arguments are accepted, including methods such as _schema.MetaData.reflect and _reflection.Inspector.get_columns.

If synonyms are not in use, this flag should be left disabled.

Constraint Reflection

The Oracle dialect can return information about foreign key, unique, and CHECK constraints, as well as indexes on tables.

Raw information regarding these constraints can be acquired using _reflection.Inspector.get_foreign_keys, _reflection.Inspector.get_unique_constraints, _reflection.Inspector.get_check_constraints, and _reflection.Inspector.get_indexes.

When using reflection at the _schema.Table level, the _schema.Table will also include these constraints.

Note the following caveats:

• When using the _reflection.Inspector.get_check_constraints method, Oracle builds a special "IS NOT NULL" constraint for columns that specify "NOT NULL". This constraint is not returned by default; to include the "IS NOT NULL" constraints, pass the flag include_all=True:

  from sqlalchemy import create_engine, inspect
  
  engine = create_engine("oracle+cx_oracle://s:t@dsn")
  inspector = inspect(engine)
  all_check_constraints = inspector.get_check_constraints(
      "some_table", include_all=True)
  

• in most cases, when reflecting a _schema.Table, a UNIQUE constraint will not be available as a .UniqueConstraint object, as Oracle mirrors unique constraints with a UNIQUE index in most cases (the exception seems to be when two or more unique constraints represent the same columns); the _schema.Table will instead represent these using .Index with the unique=True flag set.

• Oracle creates an implicit index for the primary key of a table; this index is excluded from all index results.

• the list of columns reflected for an index will not include column names that start with SYS_NC.

Table names with SYSTEM/SYSAUX tablespaces

The _reflection.Inspector.get_table_names and _reflection.Inspector.get_temp_table_names methods each return a list of table names for the current engine. These methods are also part of the reflection which occurs within an operation such as _schema.MetaData.reflect. By default, these operations exclude the SYSTEM and SYSAUX tablespaces from the operation. In order to change this, the default list of tablespaces excluded can be changed at the engine level using the exclude_tablespaces parameter:

# exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM
e = create_engine(
  "oracle://scott:tiger@xe",
  exclude_tablespaces=["SYSAUX", "SOME_TABLESPACE"])

DateTime Compatibility

Oracle has no datatype known as DATETIME, it instead has only DATE, which can actually store a date and time value. For this reason, the Oracle dialect provides a type _oracle.DATE which is a subclass of .DateTime. This type has no special behavior, and is only present as a "marker" for this type; additionally, when a database column is reflected and the type is reported as DATE, the time-supporting _oracle.DATE type is used.

Oracle Table Options

The CREATE TABLE phrase supports the following options with Oracle in conjunction with the _schema.Table construct:

• ON COMMIT:

  Table(
      "some_table", metadata, ...,
      prefixes=['GLOBAL TEMPORARY'], oracle_on_commit='PRESERVE ROWS')
  

• COMPRESS:

   Table('mytable', metadata, Column('data', String(32)),
       oracle_compress=True)
  
   Table('mytable', metadata, Column('data', String(32)),
       oracle_compress=6)
  
  The ``oracle_compress`` parameter accepts either an integer compression
  level, or ``True`` to use the default compression level.
  

Oracle Specific Index Options

Index('my_index', my_table.c.data, oracle_bitmap=True)

Index('my_index', my_table.c.data, oracle_compress=True)

Index('my_index', my_table.c.data1, my_table.c.data2, unique=True,
       oracle_compress=1)