Support for the Oracle database.

DBAPI Support¶

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

• cx-Oracle
• zxJDBC for Jython

Connect Arguments¶

The dialect supports several create_engine() arguments which 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 used for joins.
• optimize_limits - defaults to False. see the section on LIMIT/OFFSET.
• use_binds_for_limits - defaults to True. see the section on LIMIT/OFFSET.

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. Since Oracle has no “autoincrement” feature, SQLAlchemy relies upon sequences to produce these values. With the Oracle dialect, a sequence must always be explicitly specified to enable autoincrement. This is divergent with the majority of documentation examples which assume the usage of an autoincrement-capable database. To specify sequences, use the sqlalchemy.schema.Sequence object which is passed to 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 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 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 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.

For the duration of the SQLAlchemy 1.3 series, the default max identifier length will remain at 30, even if compatibility version 12.2 or greater is in use. When the newer version is detected, a warning will be emitted upon first connect, which refers the user to make use of the create_engine.max_identifier_length parameter in order to assure forwards compatibility with SQLAlchemy 1.4, which will be changing this value to 128 when compatibility version 12.2 or greater is detected.

Using create_engine.max_identifier_length, the effective identifier length used by the SQLAlchemy dialect will be used as given, overriding the current default value of 30, so that when Oracle 12.2 or greater is used, the newer 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 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)

The implication here is that by upgrading SQLAlchemy to version 1.4 on an existing Oracle 12.2 or greater database, the generation of constraint names will change, which can impact the behavior of database migrations. A key example is a 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.

Therefore, applications are strongly advised to make use of 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 support for the LIMIT or OFFSET keywords. SQLAlchemy uses a wrapped subquery approach in conjunction with ROWNUM. The exact methodology is taken from http://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 enable the usage of this optimization directive, specify optimize_limits=True to create_engine().
• the values passed for the limit/offset are sent as bound parameters. Some users have observed that Oracle produces a poor query plan when the values are sent as binds and not rendered literally. To render the limit/offset values literally within the SQL statement, specify use_binds_for_limits=False to create_engine().

Some users have reported better performance when the entirely different approach of a window query is used, i.e. ROW_NUMBER() OVER (ORDER BY), to provide 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 at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault which installs a select compiler that overrides the generation of limit/offset with a window function.

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 RETURNING Support).

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 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 http://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 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 NVARCHAR and 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 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) 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 MetaData.reflect() and 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 Inspector.get_foreign_keys(), Inspector.get_unique_constraints(), Inspector.get_check_constraints(), and Inspector.get_indexes().

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

Note the following caveats:

• When using the 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 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 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 Inspector.get_table_names() and 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 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 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)

Oracle Data Types¶

As with all SQLAlchemy dialects, all UPPERCASE types that are known to be valid with Oracle are importable from the top level dialect, whether they 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

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

class sqlalchemy.dialects.oracle.BFILE(length=None)¶

Bases: sqlalchemy.types.LargeBinary

__init__(length=None)¶

inherited from the __init__() method of LargeBinary

Construct a LargeBinary type.

Parameters:	length¶ – optional, a length for the column for use in DDL 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 subclasses types.DateTime; this is to suit the fact that the Oracle DATE type supports a time value.

New in version 0.9.4.

__init__(timezone=False)¶

inherited from the __init__() method of DateTime

Construct a new DateTime.

Parameters:	timezone¶ – boolean. Indicates that the datetime type should enable timezone support, if available on the base date/time-holding type only. It is recommended to make use of the TIMESTAMP datatype directly when using this flag, as some databases include separate generic date/time-holding types distinct from the timezone-capable TIMESTAMP datatype, such as Oracle.

class sqlalchemy.dialects.oracle.DOUBLE_PRECISION(precision=None, asdecimal=False, decimal_return_scale=None)¶

Bases: sqlalchemy.types.Float

__init__(precision=None, asdecimal=False, decimal_return_scale=None)¶

inherited from the __init__() method of Float

Construct a Float.

Parameters:

precision¶ – the numeric precision for use in DDL CREATE TABLE. asdecimal¶ – the same flag as that of Numeric, but defaults to False. Note that setting this flag to True results in floating point conversion. decimal_return_scale¶ – Default scale to use when converting from floats to Python decimals. Floating point values will typically be much longer due to decimal inaccuracy, and most floating point database types don’t have a notion of “scale”, so by default the float type looks for the first ten decimal places when converting. Specifying this value will override that length. Note that the MySQL 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 intervals within available DBAPIs (cx_oracle and zxjdbc).

Parameters:	day_precision¶ – the day precision value. this is the number of digits to store for the day field. Defaults to “2” second_precision¶ – the second precision value. this is the number 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, _warn_on_bytestring=False, _expect_unicode=False)¶

Bases: sqlalchemy.types.Text

__init__(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False, _expect_unicode=False)¶

inherited from the __init__() method of String

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific. collation¶ – Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE 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, the String type will assume that input 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 support Python unicode under Python 2, SQLAlchemy will use its own encoder/decoder functionality on strings, referring to the value of the create_engine.encoding parameter parameter 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 Unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python Unicode, the string value "force" can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally. Note SQLAlchemy’s unicode-conversion flags and features only apply to Python 2; in Python 3, all string objects are Unicode objects. For this reason, as well as the fact that virtually all modern DBAPIs now support Unicode natively even under Python 2, the String.convert_unicode flag is inherently a legacy feature. Note In the vast majority of cases, the Unicode or UnicodeText datatypes should be used for a Column that expects to store non-ascii data. These datatypes will ensure that the correct types are used on the database side as well as set up the correct Unicode behaviors under Python 2. See also create_engine.convert_unicode - Engine-wide parameter unicode_error¶ – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions, requires that String.convert_unicode is set to "force" Deprecated since version 1.3: The String.unicode_errors parameter is deprecated and will be removed in a future release. This parameter is unnecessary for modern Python DBAPIs and degrades performance significantly.

class sqlalchemy.dialects.oracle.NUMBER(precision=None, scale=None, asdecimal=None)¶

Bases: sqlalchemy.types.Numeric, sqlalchemy.types.Integer

__init__(precision=None, scale=None, asdecimal=None)¶

Construct a Numeric.

Parameters:

precision¶ – the numeric precision for use in DDL CREATE TABLE. scale¶ – the numeric scale for use in DDL CREATE TABLE. asdecimal¶ – default True. Return whether or not values should be sent as Python Decimal objects, or as floats. Different DBAPIs send one or the other based on datatypes - the Numeric type will ensure that return values are one or the other across DBAPIs consistently. decimal_return_scale¶ – Default scale to use when converting from floats to Python decimals. Floating point values will typically be much longer due to decimal inaccuracy, and most floating point database types don’t have a notion of “scale”, so by default the float type looks for the first ten decimal places when converting. Specifying this value will override that length. Types which do include an explicit “.scale” value, such as the base Numeric as well as the MySQL float types, will use the value of “.scale” as the default for decimal_return_scale, if not otherwise specified. New in version 0.9.0.

When using the Numeric type, care should be taken to ensure that the asdecimal setting is appropriate for the DBAPI in use - when Numeric applies a conversion from Decimal->float or float-> Decimal, this conversion incurs an additional performance overhead for all result columns received.

DBAPIs that return Decimal natively (e.g. psycopg2) will have better accuracy and higher performance with a setting of True, as the native translation to Decimal reduces the amount of floating- point issues at play, and the Numeric type itself doesn’t need to apply any further conversions. However, another DBAPI which returns floats natively will incur an additional conversion overhead, and is still subject to floating point data loss - in which case asdecimal=False will at least remove the extra conversion overhead.

class sqlalchemy.dialects.oracle.LONG(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False, _expect_unicode=False)¶

Bases: sqlalchemy.types.Text

__init__(length=None, collation=None, convert_unicode=False, unicode_error=None, _warn_on_bytestring=False, _expect_unicode=False)¶

inherited from the __init__() method of String

Create a string-holding type.

Parameters:

length¶ – optional, a length for the column for use in DDL and CAST expressions. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued if a VARCHAR with no length is included. Whether the value is interpreted as bytes or characters is database specific. collation¶ – Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE 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, the String type will assume that input 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 support Python unicode under Python 2, SQLAlchemy will use its own encoder/decoder functionality on strings, referring to the value of the create_engine.encoding parameter parameter 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 Unicode is to be encoded/decoded by SQLAlchemy on a backend that does natively support Python Unicode, the string value "force" can be passed here which will cause SQLAlchemy’s encode/decode services to be used unconditionally. Note SQLAlchemy’s unicode-conversion flags and features only apply to Python 2; in Python 3, all string objects are Unicode objects. For this reason, as well as the fact that virtually all modern DBAPIs now support Unicode natively even under Python 2, the String.convert_unicode flag is inherently a legacy feature. Note In the vast majority of cases, the Unicode or UnicodeText datatypes should be used for a Column that expects to store non-ascii data. These datatypes will ensure that the correct types are used on the database side as well as set up the correct Unicode behaviors under Python 2. See also create_engine.convert_unicode - Engine-wide parameter unicode_error¶ – Optional, a method to use to handle Unicode conversion errors. Behaves like the errors keyword argument to the standard library’s string.decode() functions, requires that String.convert_unicode is set to "force" Deprecated since version 1.3: The String.unicode_errors parameter is deprecated and will be removed in a future release. This parameter is unnecessary for modern Python DBAPIs and degrades performance significantly.

class sqlalchemy.dialects.oracle.RAW(length=None)¶

Bases: sqlalchemy.types._Binary

__init__(length=None)¶

inherited from the __init__() method of _Binary

Initialize self. See help(type(self)) for accurate signature.

cx_Oracle¶

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

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

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

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

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

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

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

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)

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]

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

zxjdbc¶

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

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