Oracle

Oracle

Support for the Oracle database.

DBAPI Support

The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.

Connect Arguments

The dialect supports several create_engine() argumentswhich affect the behavior of the dialect regardless of driver in use.

use_ansi - Use ANSI JOIN constructs (see the section on Oracle 8).Defaults to True. If False, Oracle-8 compatible constructs are usedfor joins.
optimize_limits - defaults to False. see the section onLIMIT/OFFSET.
use_binds_for_limits - defaults to True. see the section onLIMIT/OFFSET.

Auto Increment Behavior

SQLAlchemy Table objects which include integer primary keys are usuallyassumed to have “autoincrementing” behavior, meaning they can generate theirown primary key values upon INSERT. Since Oracle has no “autoincrement”feature, SQLAlchemy relies upon sequences to produce these values. With theOracle dialect, a sequence must always be explicitly specified to enableautoincrement. This is divergent with the majority of documentationexamples which assume the usage of an autoincrement-capable database. Tospecify sequences, use the sqlalchemy.schema.Sequence object which is passedto a Column construct:

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

This step is also required when using table reflection, i.e. autoload=True:

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

Identifier Casing

In Oracle, the data dictionary represents all case insensitive identifiernames using UPPERCASE text. SQLAlchemy on the other hand considers anall-lower case identifier name to be case insensitive. The Oracle dialectconverts all case insensitive identifiers to and from those two formats duringschema level communication, such as reflection of tables and indexes. Usingan UPPERCASE name on the SQLAlchemy side indicates a case sensitiveidentifier, and SQLAlchemy will quote the name - this will cause mismatchesagainst data dictionary data received from Oracle, so unless identifier nameshave been truly created as case sensitive (i.e. using quoted names), alllowercase names should be used on the SQLAlchemy side.

Max Identifier Lengths

Oracle has changed the default max identifier length as of Oracle Serverversion 12.2. Prior to this version, the length was 30, and for 12.2 andgreater it is now 128. This change impacts SQLAlchemy in the area ofgenerated SQL label names as well as the generation of constraint names,particularly in the case where the constraint naming convention featuredescribed at Configuring 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 theactual server version in order to assist with migration of Oracle databases,and may be configured within the Oracle server itself. This compatibilityversion is retrieved using the query SELECT value FROM v$parameter WHEREname = 'compatible';. The SQLAlchemy Oracle dialect, when tasked withdetermining the default max identifier length, will attempt to use this queryupon first connect in order to determine the effective compatibility version ofthe server, which determines what the maximum allowed identifier length is forthe server. If the table is not available, the server version information isused instead.

For the duration of the SQLAlchemy 1.3 series, the default max identifierlength will remain at 30, even if compatibility version 12.2 or greater is inuse. When the newer version is detected, a warning will be emitted upon firstconnect, which refers the user to make use of thecreate_engine.max_identifier_length parameter in order to assureforwards compatibility with SQLAlchemy 1.4, which will be changing this valueto 128 when compatibility version 12.2 or greater is detected.

Using create_engine.max_identifier_length, the effective identifierlength used by the SQLAlchemy dialect will be used as given, overriding thecurrent default value of 30, so that when Oracle 12.2 or greater is used, thenewer identifier length may be taken advantage of:

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

The maximum identifier length comes into play both when generating anonymizedSQL labels in SELECT statements, but more crucially when generating constraintnames from a naming convention. It is this area that has created the need forSQLAlchemy to change this default conservatively. For example, the followingnaming convention produces two very different constraint names based on theidentifier 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)

The implication here is that by upgrading SQLAlchemy to version 1.4 onan existing Oracle 12.2 or greater database, the generation of constraintnames will change, which can impact the behavior of database migrations.A key example is a migration that wishes to “DROP CONSTRAINT” on a name thatwas previously generated with the shorter length. This migration will failwhen the identifier length is changed without the name of the index orconstraint first being adjusted.

Therefore, applications are strongly advised to make use ofcreate_engine.max_identifier_length in order to maintain controlof the generation of truncated names, and to fully review and test all databasemigrations in a staging environment when changing this value to ensure that theimpact of this change has been mitigated.

New in version 1.3.9: Added thecreate_engine.max_identifier_length parameter; the Oracledialect now detects compatibility version 12.2 or greater and warnsabout upcoming max identitifier length changes in SQLAlchemy 1.4.

LIMIT/OFFSET Support

Oracle has no support for the LIMIT or OFFSET keywords. SQLAlchemy usesa wrapped subquery approach in conjunction with ROWNUM. The exact methodologyis taken fromhttp://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html .

There are two options which affect its behavior:

the “FIRST ROWS()” optimization keyword is not used by default. To enablethe usage of this optimization directive, specify optimize_limits=Trueto create_engine().
the values passed for the limit/offset are sent as bound parameters. Someusers have observed that Oracle produces a poor query plan when the valuesare sent as binds and not rendered literally. To render the limit/offsetvalues literally within the SQL statement, specifyuse_binds_for_limits=False to create_engine().

Some users have reported better performance when the entirely differentapproach of a window query is used, i.e. ROW_NUMBER() OVER (ORDER BY), toprovide LIMIT/OFFSET (note that the majority of users don’t observe this).To suit this case the method used for LIMIT/OFFSET can be replaced entirely.See the recipe athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefaultwhich installs a select compiler that overrides the generation of limit/offsetwith a window function.

RETURNING Support

The Oracle database supports a limited form of RETURNING, in order to retrieveresult sets of matched rows from INSERT, UPDATE and DELETE statements.Oracle’s RETURNING..INTO syntax only supports one row being returned, as itrelies upon OUT parameters in order to function. In addition, supportedDBAPIs have further limitations (see RETURNING Support).

SQLAlchemy’s “implicit returning” feature, which employs RETURNING within anINSERT and sometimes an UPDATE statement in order to fetch newly generatedprimary key values and other SQL defaults and expressions, is normally enabledon the Oracle backend. By default, “implicit returning” typically onlyfetches the value of a single nextval(some_seq) expression embedded intoan INSERT in order to increment a sequence within an INSERT statement and getthe value back at the same time. To disable this feature across the board,specify implicit_returning=False to 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 tableoption:

# 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 basedsolution is available athttp://asktom.oracle.com/tkyte/update_cascade/index.html .

When using the SQLAlchemy ORM, the ORM has limited ability to manually issuecascading 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 thefollowing behaviors:

the use_ansi flag is set to False. This has the effect of converting allJOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOINmakes use of Oracle’s (+) operator.
the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL whenthe Unicode is used - VARCHAR2 and CLOB areissued instead. This because these types don’t seem to work correctly onOracle 8 even though they are available. TheNVARCHAR andNCLOB types will always generateNVARCHAR2 and NCLOB.
the “native unicode” mode is disabled when using cx_oracle, i.e. SQLAlchemyencodes all Python unicode objects to “string” before passing in as bindparameters.

Synonym/DBLINK Reflection

When using reflection with Table objects, the dialect can optionally searchfor tables indicated by synonyms, either in local or remote schemas oraccessed over DBLINK, by passing the flag oracle_resolve_synonyms=True asa keyword argument to the Table construct:

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

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

oracle_resolve_synonyms is accepted wherever reflection arguments areaccepted, including methods such as MetaData.reflect() andInspector.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, andCHECK constraints, as well as indexes on tables.

Raw information regarding these constraints can be acquired usingInspector.get_foreign_keys(), Inspector.get_unique_constraints(),Inspector.get_check_constraints(), and Inspector.get_indexes().

Changed in version 1.2: The Oracle dialect can now reflect UNIQUE andCHECK constraints.

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

Note the following caveats:

When using the Inspector.get_check_constraints() method, Oraclebuilds a special “IS NOT NULL” constraint for columns that specify“NOT NULL”. This constraint is not returned by default; to includethe “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 Table, a UNIQUE constraint willnot be available as a UniqueConstraint object, as Oraclemirrors unique constraints with a UNIQUE index in most cases (the exceptionseems to be when two or more unique constraints represent the same columns);the Table will instead represent these using Indexwith the unique=True flag set.
Oracle creates an implicit index for the primary key of a table; this indexis excluded from all index results.
the list of columns reflected for an index will not include column namesthat start with SYS_NC.

Table names with SYSTEM/SYSAUX tablespaces

The Inspector.get_table_names() andInspector.get_temp_table_names()methods each return a list of table names for the current engine. These methodsare also part of the reflection which occurs within an operation such asMetaData.reflect(). By default, these operations exclude the SYSTEMand SYSAUX tablespaces from the operation. In order to change this, thedefault list of tablespaces excluded can be changed at the engine level usingthe exclude_tablespaces parameter:

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

New in version 1.1.

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 Oracledialect provides a type oracle.DATE which is a subclass ofDateTime. This type has no special behavior, and is onlypresent as a “marker” for this type; additionally, when a database columnis reflected and the type is reported as DATE, the time-supportingoracle.DATE type is used.

Changed in version 0.9.4: Added oracle.DATE to subclassDateTime. This is a change as previous versionswould reflect a DATE column as types.DATE, which subclassesDate. The only significance here is for schemes that areexamining the type of column for use in special Python translations orfor migrating schemas to other database backends.

Oracle Table Options

The CREATE TABLE phrase supports the following options with Oraclein conjunction with the Table construct:

ON COMMIT:

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

New in version 1.0.0.

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.

New in version 1.0.0.

Oracle Specific Index Options

Bitmap Indexes

You can specify the oracle_bitmap parameter to create a bitmap indexinstead of a B-tree index:

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

Bitmap indexes cannot be unique and cannot be compressed. SQLAlchemy will notcheck for such limitations, only the database will.

New in version 1.0.0.

Index compression

Oracle has a more efficient storage mode for indexes containing lots ofrepeated values. Use the oracle_compress parameter to turn on keycompression:

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)

The oracle_compress parameter accepts either an integer specifying thenumber of prefix columns to compress, or True to use the default (allcolumns for non-unique indexes, all but the last column for unique indexes).

New in version 1.0.0.

Oracle Data Types

As with all SQLAlchemy dialects, all UPPERCASE types that are known to bevalid with Oracle are importable from the top level dialect, whetherthey originate from sqlalchemy.types or from the local dialect:

from sqlalchemy.dialects.oracle import \
            BFILE, BLOB, CHAR, CLOB, DATE, \
            DOUBLE_PRECISION, FLOAT, INTERVAL, LONG, NCLOB, NCHAR, \
            NUMBER, NVARCHAR, NVARCHAR2, RAW, TIMESTAMP, VARCHAR, \
            VARCHAR2

New in version 1.2.19: Added NCHAR to the list of datatypesexported by the Oracle dialect.

Types which are specific to Oracle, or have Oracle-specificconstruction arguments, are as follows:

class sqlalchemy.dialects.oracle.BFILE(length=None)
Bases: sqlalchemy.types.LargeBinary
- init(length=None)

inherited from theinit()method ofLargeBinary

Construct a LargeBinary type.

- Parameters
-

length – optional, a length for the column for use inDDL statements, for those binary types that accept a length,such as the MySQL BLOB type.

class sqlalchemy.dialects.oracle.DATE(timezone=False)
Bases: sqlalchemy.types.DateTime

Provide the oracle DATE type.

This type has no special Python behavior, except that it subclassestypes.DateTime; this is to suit the fact that the OracleDATE type supports a time value.

New in version 0.9.4.

init(timezone=False)

inherited from theinit()method ofDateTime

Construct a new DateTime.

- Parameters
-

timezone – boolean. Indicates that the datetime type shouldenable timezone support, if available on thebase date/time-holding type only. It is recommendedto make use of the TIMESTAMP datatype directly whenusing this flag, as some databases include separate genericdate/time-holding types distinct from the timezone-capableTIMESTAMP datatype, such as Oracle.

class sqlalchemy.dialects.oracle.DOUBLEPRECISION(_precision=None, asdecimal=False, decimal_return_scale=None)
Bases: sqlalchemy.types.Float
- init(precision=None, asdecimal=False, decimal_return_scale=None)

inherited from theinit()method ofFloat

Construct a Float.

- Parameters
- 
  -

precision – the numeric precision for use in DDL CREATETABLE.

asdecimal – the same flag as that of Numeric, butdefaults to False. Note that setting this flag to Trueresults in floating point conversion.

decimal_return_scale –

Default scale to use when convertingfrom floats to Python decimals. Floating point values will typicallybe much longer due to decimal inaccuracy, and most floating pointdatabase types don’t have a notion of “scale”, so by default thefloat type looks for the first ten decimal places when converting.Specifying this value will override that length. Note that theMySQL float types, which do include “scale”, will use “scale”as the default for decimal_return_scale, if not otherwise specified.

New in version 0.9.0.

class sqlalchemy.dialects.oracle.INTERVAL(day_precision=None, second_precision=None)
Bases: sqlalchemy.types.TypeEngine
- init(day_precision=None, second_precision=None)
- Construct an INTERVAL.

Note that only DAY TO SECOND intervals are currently supported.This is due to a lack of support for YEAR TO MONTH intervalswithin available DBAPIs (cx_oracle and zxjdbc).

- Parameters
- 
  -

day_precision – the day precision value. this is the number ofdigits to store for the day field. Defaults to “2”

second_precision – the second precision value. this is thenumber of digits to store for the fractional seconds field.Defaults to “6”.

class sqlalchemy.dialects.oracle.NCLOB(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)
Bases: sqlalchemy.types.Text
- init(length=None, collation=None, convert_unicode=False, unicode_error=None, warnon_bytestring=False, expectunicode=False)

inherited from theinit()method ofString

Create a string-holding type.

- Parameters
- 
  -

length – optional, a length for the column for use inDDL and CAST expressions. May be safely omitted if no CREATETABLE will be issued. Certain databases may require alength for use in DDL, and will raise an exception whenthe CREATE TABLE DDL is issued if a VARCHARwith no length is included. Whether the value isinterpreted as bytes or characters is database specific.

collation –

Optional, a column-level collation foruse in DDL and CAST expressions. Renders using theCOLLATE keyword supported by SQLite, MySQL, and PostgreSQL.E.g.:

>>> from sqlalchemy import cast, select, String
>>> print select([cast('some string', String(collation='utf8'))])
SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1

convert_unicode –

When set to True, theString type will assume thatinput is to be passed as Python Unicode objects under Python 2,and results returned as Python Unicode objects.In the rare circumstance that the DBAPI does not supportPython unicode under Python 2, SQLAlchemy will use its ownencoder/decoder functionality on strings, referring to thevalue of the create_engine.encoding parameterparameter passed to create_engine() as the encoding.

Deprecated since version 1.3: The String.convert_unicode parameter is deprecated and will be removed in a future release. All modern DBAPIs now support Python Unicode directly and this parameter is unnecessary.

For the extremely rare case that Python Unicodeis to be encoded/decoded by SQLAlchemy on a backendthat does natively support Python Unicode,the string value "force" can be passed here which willcause SQLAlchemy’s encode/decode services to beused unconditionally.

Note

SQLAlchemy’s unicode-conversion flags and features only applyto Python 2; in Python 3, all string objects are Unicode objects.For this reason, as well as the fact that virtually all modernDBAPIs now support Unicode natively even under Python 2,the String.convert_unicode flag is inherently alegacy feature.

Note

In the vast majority of cases, the Unicode orUnicodeText datatypes should be used for aColumn that expects to store non-ascii data. Thesedatatypes will ensure that the correct types are used on thedatabase side as well as set up the correct Unicode behaviorsunder Python 2.

cx_Oracle

Support for the Oracle database via the cx-Oracle driver.

DBAPI

Documentation and download information (if applicable) for cx-Oracle is available at:https://oracle.github.io/python-cx_Oracle/

Connecting

Connect String:

oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

Additional Connect Arguments

When connecting with the dbname URL token present, the hostname,port, and dbname tokens are converted to a TNS name using thecx_Oracle.makedsn() function. The URL below:

e = create_engine("oracle+cx_oracle://user:pass@hostname/dbname")

Will be used to create the DSN as follows:

>>> import cx_Oracle
>>> cx_Oracle.makedsn("hostname", 1521, sid="dbname")
'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=dbname)))'

The service_name parameter, also consumed by cx_Oracle.makedsn(), maybe specified in the URL query string, e.g. ?service_name=my_service.

If dbname is not present, then the value of hostname in theURL is used directly as the DSN passed to cx_Oracle.connect().

Additional connection arguments may be sent to the cx_Oracle.connect()function using the create_engine.connect_args dictionary.Any cx_Oracle parameter value and/or constant may be passed, such as:

import cx_Oracle
e = create_engine(
    "oracle+cx_oracle://user:pass@dsn",
    connect_args={
        "mode": cx_Oracle.SYSDBA,
        "events": True
    }
)

Alternatively, most cx_Oracle DBAPI arguments can also be encoded as stringswithin the URL, which includes parameters such as mode, purity,events, threaded, and others:

e = create_engine(
    "oracle+cx_oracle://user:pass@dsn?mode=SYSDBA&events=true")

Changed in version 1.3: the cx_oracle dialect now accepts all argument nameswithin the URL string itself, to be passed to the cx_Oracle DBAPI. Aswas the case earlier but not correctly documented, thecreate_engine.connect_args parameter also accepts allcx_Oracle DBAPI connect arguments.

There are also options that are consumed by the SQLAlchemy cx_oracle dialectitself. These options are always passed directly to create_engine(),such as:

e = create_engine(
    "oracle+cx_oracle://user:pass@dsn", coerce_to_unicode=False)

The parameters accepted by the cx_oracle dialect are as follows:

arraysize - set the cx_oracle.arraysize value on cursors, defaultedto 50. This setting is significant with cx_Oracle as the contents of LOBobjects are only readable within a “live” row (e.g. within a batch of50 rows).
auto_convert_lobs - defaults to True; See LOB Objects.
coerce_to_unicode - see Unicode for detail.
coerce_to_decimal - see Precision Numerics for detail.

Unicode

The cx_Oracle DBAPI as of version 5 fully supports Unicode, and has theability to return string results as Python Unicode objects natively.

Explicit Unicode support is available by using the Unicode datatypewith SQLAlchemy Core expression language, as well as the UnicodeTextdatatype. These types correspond to the VARCHAR2 and CLOB Oracle datatypes bydefault. When using these datatypes with Unicode data, it is expected thatthe Oracle database is configured with a Unicode-aware character set, as wellas that the NLS_LANG environment variable is set appropriately, so thatthe VARCHAR2 and CLOB datatypes can accommodate the data.

In the case that the Oracle database is not configured with a Unicode characterset, the two options are to use the oracle.NCHAR andoracle.NCLOB datatypes explicitly, or to pass the flaguse_nchar_for_unicode=True to create_engine(), which will cause theSQLAlchemy dialect to use NCHAR/NCLOB for the Unicode /UnicodeText datatypes instead of VARCHAR/CLOB.

Changed in version 1.3: The Unicode and UnicodeTextdatatypes now correspond to the VARCHAR2 and CLOB Oracle datatypesunless the use_nchar_for_unicode=True is passed to the dialectwhen create_engine() is called.

When result sets are fetched that include strings, under Python 3 the cx_OracleDBAPI returns all strings as Python Unicode objects, since Python 3 only has aUnicode string type. This occurs for data fetched from datatypes such asVARCHAR2, CHAR, CLOB, NCHAR, NCLOB, etc. In order to provide cross-compatibility under Python 2, the SQLAlchemy cx_Oracle dialect will addUnicode-conversion to string data under Python 2 as well. Historically, thismade use of converters that were supplied by cx_Oracle but were found to benon-performant; SQLAlchemy’s own converters are used for the string to Unicodeconversion under Python 2. To disable the Python 2 Unicode conversion forVARCHAR2, CHAR, and CLOB, the flag coerce_to_unicode=False can be passed tocreate_engine().

Changed in version 1.3: Unicode conversion is applied to all string valuesby default under python 2. The coerce_to_unicode now defaults to Trueand can be set to False to disable the Unicode coercion of strings that aredelivered as VARCHAR2/CHAR/CLOB data.

Fine grained control over cx_Oracle data binding performance with setinputsizes

The cx_Oracle DBAPI has a deep and fundamental reliance upon the usage of theDBAPI setinputsizes() call. The purpose of this call is to establish thedatatypes that are bound to a SQL statement for Python values being passed asparameters. While virtually no other DBAPI assigns any use to thesetinputsizes() call, the cx_Oracle DBAPI relies upon it heavily in itsinteractions with the Oracle client interface, and in some scenarios it is notpossible for SQLAlchemy to know exactly how data should be bound, as somesettings can cause profoundly different performance characteristics, whilealtering the type coercion behavior at the same time.

Users of the cx_Oracle dialect are strongly encouraged to read throughcx_Oracle’s list of built-in datatype symbols athttp://cx-oracle.readthedocs.io/en/latest/module.html#types.Note that in some cases, significant performance degradation can occur whenusing these types vs. not, in particular when specifying cx_Oracle.CLOB.

On the SQLAlchemy side, the DialectEvents.do_setinputsizes() event canbe used both for runtime visibility (e.g. logging) of the setinputsizes step aswell as to fully control how setinputsizes() is used on a per-statementbasis.

New in version 1.2.9: Added DialectEvents.setinputsizes()

Example 1 - logging all setinputsizes calls

The following example illustrates how to log the intermediary values from aSQLAlchemy perspective before they are converted to the raw setinputsizes()parameter dictionary. The keys of the dictionary are BindParameterobjects which have a .key and a .type attribute:

from sqlalchemy import create_engine, event
 
engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
 
@event.listens_for(engine, "do_setinputsizes")
def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):
    for bindparam, dbapitype in inputsizes.items():
            log.info(
                "Bound parameter name: %s  SQLAlchemy type: %r  "
                "DBAPI object: %s",
                bindparam.key, bindparam.type, dbapitype)

Example 2 - remove all bindings to CLOB

The CLOB datatype in cx_Oracle incurs a significant performance overhead,however is set by default for the Text type within the SQLAlchemy 1.2series. This setting can be modified as follows:

from sqlalchemy import create_engine, event
from cx_Oracle import CLOB
 
engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
 
@event.listens_for(engine, "do_setinputsizes")
def _remove_clob(inputsizes, cursor, statement, parameters, context):
    for bindparam, dbapitype in list(inputsizes.items()):
        if dbapitype is CLOB:
            del inputsizes[bindparam]

RETURNING Support

The cx_Oracle dialect implements RETURNING using OUT parameters.The dialect supports RETURNING fully, however cx_Oracle 6 is recommendedfor complete support.

LOB Objects

cx_oracle returns oracle LOBs using the cx_oracle.LOB object. SQLAlchemyconverts these to strings so that the interface of the Binary type isconsistent with that of other backends, which takes place within a cx_Oracleoutputtypehandler.

cx_Oracle prior to version 6 would require that LOB objects be read beforea new batch of rows would be read, as determined by the cursor.arraysize.As of the 6 series, this limitation has been lifted. Nevertheless, becauseSQLAlchemy pre-reads these LOBs up front, this issue is avoided in any case.

To disable the auto “read()” feature of the dialect, the flagauto_convert_lobs=False may be passed to create_engine(). Underthe cx_Oracle 5 series, having this flag turned off means there is the chanceof reading from a stale LOB object if not read as it is fetched. Withcx_Oracle 6, this issue is resolved.

Changed in version 1.2: the LOB handling system has been greatly simplifiedinternally to make use of outputtypehandlers, and no longer makes useof alternate “buffered” result set objects.

Two Phase Transactions Not Supported

Two phase transactions are not supported under cx_Oracle due to poordriver support. As of cx_Oracle 6.0b1, the interface fortwo phase transactions has been changed to be more of a direct pass-throughto the underlying OCI layer with less automation. The additional logicto support this system is not implemented in SQLAlchemy.

Precision Numerics

SQLAlchemy’s numeric types can handle receiving and returning values as PythonDecimal objects or float objects. When a Numeric object, or asubclass such as Float, oracle.DOUBLE_PRECISION etc. is inuse, the Numeric.asdecimal flag determines if values should becoerced to Decimal upon return, or returned as float objects. To makematters more complicated under Oracle, Oracle’s NUMBER type can alsorepresent integer values if the “scale” is zero, so the Oracle-specificoracle.NUMBER type takes this into account as well.

The cx_Oracle dialect makes extensive use of connection- and cursor-level“outputtypehandler” callables in order to coerce numeric values as requested.These callables are specific to the specific flavor of Numeric inuse, as well as if no SQLAlchemy typing objects are present. There areobserved scenarios where Oracle may sends incomplete or ambiguous informationabout the numeric types being returned, such as a query where the numeric typesare buried under multiple levels of subquery. The type handlers do their bestto make the right decision in all cases, deferring to the underlying cx_OracleDBAPI for all those cases where the driver can make the best decision.

When no typing objects are present, as when executing plain SQL strings, adefault “outputtypehandler” is present which will generally return numericvalues which specify precision and scale as Python Decimal objects. Todisable this coercion to decimal for performance reasons, pass the flagcoerce_to_decimal=False to create_engine():

engine = create_engine("oracle+cx_oracle://dsn", coerce_to_decimal=False)

The coerce_to_decimal flag only impacts the results of plain stringSQL staements that are not otherwise associated with a NumericSQLAlchemy type (or a subclass of such).

Changed in version 1.2: The numeric handling system for cx_Oracle has beenreworked to take advantage of newer cx_Oracle features as wellas better integration of outputtypehandlers.

zxjdbc

Support for the Oracle database via the zxJDBC for Jython driver.

Note

Jython is not supported by current versions of SQLAlchemy. Thezxjdbc dialect should be considered as experimental.

DBAPI

Drivers for this database are available at:http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Connecting

Connect String:

oracle+zxjdbc://user:pass@host/dbname