<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Oracle
—
SQLAlchemy 1.2 Documentation
</title>
<!-- begin iterate through site-imported + sphinx environment css_files -->
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<link rel="stylesheet" href="../_static/changelog.css" type="text/css" />
<link rel="stylesheet" href="../_static/sphinx_paramlinks.css" type="text/css" />
<!-- end iterate through site-imported + sphinx environment css_files -->
<!-- begin layout.mako headers -->
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 1.2 Documentation" href="../index.html" />
<link rel="up" title="Dialects" href="index.html" />
<link rel="next" title="Microsoft SQL Server" href="mssql.html" />
<link rel="prev" title="SQLite" href="sqlite.html" />
<!-- end layout.mako headers -->
</head>
<body>
<div id="docs-container">
<div id="docs-top-navigation-container" class="body-background">
<div id="docs-header">
<div id="docs-version-header">
Release: <span class="version-num">1.2.19</span>
| Release Date: April 15, 2019
</div>
<h1>SQLAlchemy 1.2 Documentation</h1>
</div>
</div>
<div id="docs-body-container">
<div id="fixed-sidebar" class="withsidebar">
<div id="docs-sidebar-popout">
<h3><a href="../index.html">SQLAlchemy 1.2 Documentation</a></h3>
<p id="sidebar-topnav">
<a href="../contents.html">Contents</a> |
<a href="../genindex.html">Index</a>
</p>
<div id="sidebar-search">
<form class="search" action="../search.html" method="get">
<label>
Search terms:
<input type="text" placeholder="search..." name="q" size="12" />
</label>
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
</div>
<div id="docs-sidebar">
<div id="sidebar-banner">
</div>
<div id="docs-sidebar-inner">
<h3>
<a href="index.html" title="Dialects">Dialects</a>
</h3>
<ul>
<li><span class="link-container"><a class="reference external" href="postgresql.html">PostgreSQL</a></span></li>
<li><span class="link-container"><a class="reference external" href="mysql.html">MySQL</a></span></li>
<li><span class="link-container"><a class="reference external" href="sqlite.html">SQLite</a></span></li>
<li class="selected"><span class="link-container"><strong>Oracle</strong><a class="paramlink headerlink reference internal" href="#">¶</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-oracle">Support for the Oracle database.</a></span></li>
<li><span class="link-container"><a class="reference external" href="#connect-arguments">Connect Arguments</a></span></li>
<li><span class="link-container"><a class="reference external" href="#auto-increment-behavior">Auto Increment Behavior</a></span></li>
<li><span class="link-container"><a class="reference external" href="#identifier-casing">Identifier Casing</a></span></li>
<li><span class="link-container"><a class="reference external" href="#limit-offset-support">LIMIT/OFFSET Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#returning-support">RETURNING Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#on-update-cascade">ON UPDATE CASCADE</a></span></li>
<li><span class="link-container"><a class="reference external" href="#oracle-8-compatibility">Oracle 8 Compatibility</a></span></li>
<li><span class="link-container"><a class="reference external" href="#synonym-dblink-reflection">Synonym/DBLINK Reflection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#constraint-reflection">Constraint Reflection</a></span></li>
<li><span class="link-container"><a class="reference external" href="#table-names-with-system-sysaux-tablespaces">Table names with SYSTEM/SYSAUX tablespaces</a></span></li>
<li><span class="link-container"><a class="reference external" href="#datetime-compatibility">DateTime Compatibility</a></span></li>
<li><span class="link-container"><a class="reference external" href="#oracle-table-options">Oracle Table Options</a></span></li>
<li><span class="link-container"><a class="reference external" href="#oracle-specific-index-options">Oracle Specific Index Options</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#bitmap-indexes">Bitmap Indexes</a></span></li>
<li><span class="link-container"><a class="reference external" href="#index-compression">Index compression</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#oracle-data-types">Oracle Data Types</a></span></li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.oracle.cx_oracle">cx_Oracle</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-oracle-cx_oracle-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-oracle-cx_oracle-connect">Connecting</a></span></li>
<li><span class="link-container"><a class="reference external" href="#additional-connect-arguments">Additional Connect Arguments</a></span></li>
<li><span class="link-container"><a class="reference external" href="#unicode">Unicode</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#sending-string-values-as-unicode-or-non-unicode">Sending String Values as Unicode or Non-Unicode</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#fine-grained-control-over-cx-oracle-data-binding-performance-with-setinputsizes">Fine grained control over cx_Oracle data binding performance with setinputsizes</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#example-1-logging-all-setinputsizes-calls">Example 1 - logging all setinputsizes calls</a></span></li>
<li><span class="link-container"><a class="reference external" href="#example-2-remove-all-bindings-to-clob">Example 2 - remove all bindings to CLOB</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#cx-oracle-returning">RETURNING Support</a></span></li>
<li><span class="link-container"><a class="reference external" href="#lob-objects">LOB Objects</a></span></li>
<li><span class="link-container"><a class="reference external" href="#two-phase-transactions-not-supported">Two Phase Transactions Not Supported</a></span></li>
<li><span class="link-container"><a class="reference external" href="#precision-numerics">Precision Numerics</a></span></li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="#module-sqlalchemy.dialects.oracle.zxjdbc">zxjdbc</a></span><ul>
<li><span class="link-container"><a class="reference external" href="#dialect-oracle-zxjdbc-url">DBAPI</a></span></li>
<li><span class="link-container"><a class="reference external" href="#dialect-oracle-zxjdbc-connect">Connecting</a></span></li>
</ul>
</li>
</ul>
</li>
<li><span class="link-container"><a class="reference external" href="mssql.html">Microsoft SQL Server</a></span></li>
<li><span class="link-container"><a class="reference external" href="firebird.html">Firebird</a></span></li>
<li><span class="link-container"><a class="reference external" href="sybase.html">Sybase</a></span></li>
</ul>
</div>
</div>
</div>
<div id="docs-body" class="withsidebar" >
<div class="section" id="module-sqlalchemy.dialects.oracle.base">
<span id="oracle"></span><span id="oracle-toplevel"></span><h1>Oracle<a class="headerlink" href="#module-sqlalchemy.dialects.oracle.base" title="Permalink to this headline">¶</a></h1>
<div class="section" id="dialect-oracle">
<p>Support for the Oracle database.</p>
<h2>DBAPI Support<a class="headerlink" href="#dialect-oracle" title="Permalink to this headline">¶</a></h2>
<p>The following dialect/DBAPI options are available. Please refer to individual DBAPI sections for connect information.<ul class="simple">
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.oracle.cx_oracle">cx-Oracle</a></p></li>
<li><p><a class="reference external" href="#module-sqlalchemy.dialects.oracle.zxjdbc">zxJDBC for Jython</a></p></li>
</ul>
</p>
</div>
<div class="section" id="connect-arguments">
<h2>Connect Arguments<a class="headerlink" href="#connect-arguments" title="Permalink to this headline">¶</a></h2>
<p>The dialect supports several <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> arguments
which affect the behavior of the dialect regardless of driver in use.</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">use_ansi</span></code> - Use ANSI JOIN constructs (see the section on Oracle 8).
Defaults to <code class="docutils literal notranslate"><span class="pre">True</span></code>. If <code class="docutils literal notranslate"><span class="pre">False</span></code>, Oracle-8 compatible constructs are used
for joins.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">optimize_limits</span></code> - defaults to <code class="docutils literal notranslate"><span class="pre">False</span></code>. see the section on
LIMIT/OFFSET.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">use_binds_for_limits</span></code> - defaults to <code class="docutils literal notranslate"><span class="pre">True</span></code>. see the section on
LIMIT/OFFSET.</p></li>
</ul>
</div>
<div class="section" id="auto-increment-behavior">
<h2>Auto Increment Behavior<a class="headerlink" href="#auto-increment-behavior" title="Permalink to this headline">¶</a></h2>
<p>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, <em>a sequence must always be explicitly specified to enable
autoincrement</em>. 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:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s1">'id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="o">...</span><span class="p">),</span> <span class="o">...</span>
<span class="p">)</span></pre></div>
</div>
<p>This step is also required when using table reflection, i.e. autoload=True:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">t</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'mytable'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s1">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">Sequence</span><span class="p">(</span><span class="s1">'id_seq'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="kc">True</span><span class="p">),</span>
<span class="n">autoload</span><span class="o">=</span><span class="kc">True</span>
<span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="identifier-casing">
<h2>Identifier Casing<a class="headerlink" href="#identifier-casing" title="Permalink to this headline">¶</a></h2>
<p>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.</p>
</div>
<div class="section" id="limit-offset-support">
<h2>LIMIT/OFFSET Support<a class="headerlink" href="#limit-offset-support" title="Permalink to this headline">¶</a></h2>
<p>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
<a class="reference external" href="http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html">http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html</a> .</p>
<p>There are two options which affect its behavior:</p>
<ul class="simple">
<li><p>the “FIRST ROWS()” optimization keyword is not used by default. To enable
the usage of this optimization directive, specify <code class="docutils literal notranslate"><span class="pre">optimize_limits=True</span></code>
to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>.</p></li>
<li><p>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
<code class="docutils literal notranslate"><span class="pre">use_binds_for_limits=False</span></code> to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>.</p></li>
</ul>
<p>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
<a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault">http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault</a>
which installs a select compiler that overrides the generation of limit/offset
with a window function.</p>
</div>
<div class="section" id="returning-support">
<span id="oracle-returning"></span><h2>RETURNING Support<a class="headerlink" href="#returning-support" title="Permalink to this headline">¶</a></h2>
<p>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 <a class="reference internal" href="#cx-oracle-returning"><span class="std std-ref">RETURNING Support</span></a>).</p>
<p>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 <code class="docutils literal notranslate"><span class="pre">nextval(some_seq)</span></code> 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 <code class="docutils literal notranslate"><span class="pre">implicit_returning=False</span></code> to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle://scott:tiger@dsn"</span><span class="p">,</span>
<span class="n">implicit_returning</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span></pre></div>
</div>
<p>Implicit returning can also be disabled on a table-by-table basis as a table
option:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># Core Table</span>
<span class="n">my_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s2">"my_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span> <span class="n">implicit_returning</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span>
<span class="c1"># declarative</span>
<span class="k">class</span> <span class="nc">MyClass</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s1">'my_table'</span>
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">{</span><span class="s2">"implicit_returning"</span><span class="p">:</span> <span class="kc">False</span><span class="p">}</span></pre></div>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="#cx-oracle-returning"><span class="std std-ref">RETURNING Support</span></a> - additional cx_oracle-specific restrictions on
implicit returning.</p>
</div>
</div>
<div class="section" id="on-update-cascade">
<h2>ON UPDATE CASCADE<a class="headerlink" href="#on-update-cascade" title="Permalink to this headline">¶</a></h2>
<p>Oracle doesn’t have native ON UPDATE CASCADE functionality. A trigger based
solution is available at
<a class="reference external" href="http://asktom.oracle.com/tkyte/update_cascade/index.html">http://asktom.oracle.com/tkyte/update_cascade/index.html</a> .</p>
<p>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().</p>
</div>
<div class="section" id="oracle-8-compatibility">
<h2>Oracle 8 Compatibility<a class="headerlink" href="#oracle-8-compatibility" title="Permalink to this headline">¶</a></h2>
<p>When Oracle 8 is detected, the dialect internally configures itself to the
following behaviors:</p>
<ul class="simple">
<li><p>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.</p></li>
<li><p>the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL when
the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> 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
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NVARCHAR" title="sqlalchemy.types.NVARCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">NVARCHAR</span></code></a> and
<a class="reference internal" href="#sqlalchemy.dialects.oracle.NCLOB" title="sqlalchemy.dialects.oracle.NCLOB"><code class="xref py py-class docutils literal notranslate"><span class="pre">NCLOB</span></code></a> types will always generate
NVARCHAR2 and NCLOB.</p></li>
<li><p>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.</p></li>
</ul>
</div>
<div class="section" id="synonym-dblink-reflection">
<h2>Synonym/DBLINK Reflection<a class="headerlink" href="#synonym-dblink-reflection" title="Permalink to this headline">¶</a></h2>
<p>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 <code class="docutils literal notranslate"><span class="pre">oracle_resolve_synonyms=True</span></code> as
a keyword argument to the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">some_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s1">'some_table'</span><span class="p">,</span> <span class="n">autoload</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span>
<span class="n">autoload_with</span><span class="o">=</span><span class="n">some_engine</span><span class="p">,</span>
<span class="n">oracle_resolve_synonyms</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>When this flag is set, the given name (such as <code class="docutils literal notranslate"><span class="pre">some_table</span></code> above) will
be searched not just in the <code class="docutils literal notranslate"><span class="pre">ALL_TABLES</span></code> view, but also within the
<code class="docutils literal notranslate"><span class="pre">ALL_SYNONYMS</span></code> 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.
<code class="docutils literal notranslate"><span class="pre">@dblink</span></code>).</p>
<p><code class="docutils literal notranslate"><span class="pre">oracle_resolve_synonyms</span></code> is accepted wherever reflection arguments are
accepted, including methods such as <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.reflect()</span></code></a> and
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_columns" title="sqlalchemy.engine.reflection.Inspector.get_columns"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_columns()</span></code></a>.</p>
<p>If synonyms are not in use, this flag should be left disabled.</p>
</div>
<div class="section" id="constraint-reflection">
<span id="oracle-constraint-reflection"></span><h2>Constraint Reflection<a class="headerlink" href="#constraint-reflection" title="Permalink to this headline">¶</a></h2>
<p>The Oracle dialect can return information about foreign key, unique, and
CHECK constraints, as well as indexes on tables.</p>
<p>Raw information regarding these constraints can be acquired using
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_foreign_keys" title="sqlalchemy.engine.reflection.Inspector.get_foreign_keys"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_foreign_keys()</span></code></a>, <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_unique_constraints" title="sqlalchemy.engine.reflection.Inspector.get_unique_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_unique_constraints()</span></code></a>,
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_check_constraints" title="sqlalchemy.engine.reflection.Inspector.get_check_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_check_constraints()</span></code></a>, and <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_indexes" title="sqlalchemy.engine.reflection.Inspector.get_indexes"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_indexes()</span></code></a>.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>The Oracle dialect can now reflect UNIQUE and
CHECK constraints.</p>
</div>
<p>When using reflection at the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> level, the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>
will also include these constraints.</p>
<p>Note the following caveats:</p>
<ul>
<li><p>When using the <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_check_constraints" title="sqlalchemy.engine.reflection.Inspector.get_check_constraints"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_check_constraints()</span></code></a> method, Oracle
builds a special “IS NOT NULL” constraint for columns that specify
“NOT NULL”. This constraint is <strong>not</strong> returned by default; to include
the “IS NOT NULL” constraints, pass the flag <code class="docutils literal notranslate"><span class="pre">include_all=True</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">inspect</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://s:t@dsn"</span><span class="p">)</span>
<span class="n">inspector</span> <span class="o">=</span> <span class="n">inspect</span><span class="p">(</span><span class="n">engine</span><span class="p">)</span>
<span class="n">all_check_constraints</span> <span class="o">=</span> <span class="n">inspector</span><span class="o">.</span><span class="n">get_check_constraints</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">include_all</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
</li>
<li><p>in most cases, when reflecting a <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a>, a UNIQUE constraint will
<strong>not</strong> be available as a <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.UniqueConstraint" title="sqlalchemy.schema.UniqueConstraint"><code class="xref py py-class docutils literal notranslate"><span class="pre">UniqueConstraint</span></code></a> 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 <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> will instead represent these using <a class="reference internal" href="../core/constraints.html#sqlalchemy.schema.Index" title="sqlalchemy.schema.Index"><code class="xref py py-class docutils literal notranslate"><span class="pre">Index</span></code></a>
with the <code class="docutils literal notranslate"><span class="pre">unique=True</span></code> flag set.</p></li>
<li><p>Oracle creates an implicit index for the primary key of a table; this index
is <strong>excluded</strong> from all index results.</p></li>
<li><p>the list of columns reflected for an index will not include column names
that start with SYS_NC.</p></li>
</ul>
</div>
<div class="section" id="table-names-with-system-sysaux-tablespaces">
<h2>Table names with SYSTEM/SYSAUX tablespaces<a class="headerlink" href="#table-names-with-system-sysaux-tablespaces" title="Permalink to this headline">¶</a></h2>
<p>The <a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_table_names" title="sqlalchemy.engine.reflection.Inspector.get_table_names"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_table_names()</span></code></a> and
<a class="reference internal" href="../core/reflection.html#sqlalchemy.engine.reflection.Inspector.get_temp_table_names" title="sqlalchemy.engine.reflection.Inspector.get_temp_table_names"><code class="xref py py-meth docutils literal notranslate"><span class="pre">Inspector.get_temp_table_names()</span></code></a>
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
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.MetaData.reflect" title="sqlalchemy.schema.MetaData.reflect"><code class="xref py py-meth docutils literal notranslate"><span class="pre">MetaData.reflect()</span></code></a>. By default, these operations exclude the <code class="docutils literal notranslate"><span class="pre">SYSTEM</span></code>
and <code class="docutils literal notranslate"><span class="pre">SYSAUX</span></code> tablespaces from the operation. In order to change this, the
default list of tablespaces excluded can be changed at the engine level using
the <code class="docutils literal notranslate"><span class="pre">exclude_tablespaces</span></code> parameter:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="c1"># exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"oracle://scott:tiger@xe"</span><span class="p">,</span>
<span class="n">exclude_tablespaces</span><span class="o">=</span><span class="p">[</span><span class="s2">"SYSAUX"</span><span class="p">,</span> <span class="s2">"SOME_TABLESPACE"</span><span class="p">])</span></pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.1.</span></p>
</div>
</div>
<div class="section" id="datetime-compatibility">
<h2>DateTime Compatibility<a class="headerlink" href="#datetime-compatibility" title="Permalink to this headline">¶</a></h2>
<p>Oracle has no datatype known as <code class="docutils literal notranslate"><span class="pre">DATETIME</span></code>, it instead has only <code class="docutils literal notranslate"><span class="pre">DATE</span></code>,
which can actually store a date and time value. For this reason, the Oracle
dialect provides a type <a class="reference internal" href="#sqlalchemy.dialects.oracle.DATE" title="sqlalchemy.dialects.oracle.DATE"><code class="xref py py-class docutils literal notranslate"><span class="pre">oracle.DATE</span></code></a> which is a subclass of
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a>. 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 <code class="docutils literal notranslate"><span class="pre">DATE</span></code>, the time-supporting
<a class="reference internal" href="#sqlalchemy.dialects.oracle.DATE" title="sqlalchemy.dialects.oracle.DATE"><code class="xref py py-class docutils literal notranslate"><span class="pre">oracle.DATE</span></code></a> type is used.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 0.9.4: </span>Added <a class="reference internal" href="#sqlalchemy.dialects.oracle.DATE" title="sqlalchemy.dialects.oracle.DATE"><code class="xref py py-class docutils literal notranslate"><span class="pre">oracle.DATE</span></code></a> to subclass
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a>. This is a change as previous versions
would reflect a <code class="docutils literal notranslate"><span class="pre">DATE</span></code> column as <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DATE" title="sqlalchemy.types.DATE"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.DATE</span></code></a>, which subclasses
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Date" title="sqlalchemy.types.Date"><code class="xref py py-class docutils literal notranslate"><span class="pre">Date</span></code></a>. The only significance here is for schemes that are
examining the type of column for use in special Python translations or
for migrating schemas to other database backends.</p>
</div>
</div>
<div class="section" id="oracle-table-options">
<span id="id1"></span><h2>Oracle Table Options<a class="headerlink" href="#oracle-table-options" title="Permalink to this headline">¶</a></h2>
<p>The CREATE TABLE phrase supports the following options with Oracle
in conjunction with the <a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><code class="xref py py-class docutils literal notranslate"><span class="pre">Table</span></code></a> construct:</p>
<ul>
<li><p><code class="docutils literal notranslate"><span class="pre">ON</span> <span class="pre">COMMIT</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Table</span><span class="p">(</span>
<span class="s2">"some_table"</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span> <span class="o">...</span><span class="p">,</span>
<span class="n">prefixes</span><span class="o">=</span><span class="p">[</span><span class="s1">'GLOBAL TEMPORARY'</span><span class="p">],</span> <span class="n">oracle_on_commit</span><span class="o">=</span><span class="s1">'PRESERVE ROWS'</span><span class="p">)</span></pre></div>
</div>
</li>
</ul>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
<ul>
<li><p><code class="docutils literal notranslate"><span class="pre">COMPRESS</span></code>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span> 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.</pre></div>
</div>
</li>
</ul>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</div>
<div class="section" id="oracle-specific-index-options">
<span id="oracle-index-options"></span><h2>Oracle Specific Index Options<a class="headerlink" href="#oracle-specific-index-options" title="Permalink to this headline">¶</a></h2>
<div class="section" id="bitmap-indexes">
<h3>Bitmap Indexes<a class="headerlink" href="#bitmap-indexes" title="Permalink to this headline">¶</a></h3>
<p>You can specify the <code class="docutils literal notranslate"><span class="pre">oracle_bitmap</span></code> parameter to create a bitmap index
instead of a B-tree index:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">oracle_bitmap</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>Bitmap indexes cannot be unique and cannot be compressed. SQLAlchemy will not
check for such limitations, only the database will.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</div>
<div class="section" id="index-compression">
<h3>Index compression<a class="headerlink" href="#index-compression" title="Permalink to this headline">¶</a></h3>
<p>Oracle has a more efficient storage mode for indexes containing lots of
repeated values. Use the <code class="docutils literal notranslate"><span class="pre">oracle_compress</span></code> parameter to turn on key
compression:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data</span><span class="p">,</span> <span class="n">oracle_compress</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span>
<span class="n">Index</span><span class="p">(</span><span class="s1">'my_index'</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data1</span><span class="p">,</span> <span class="n">my_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">data2</span><span class="p">,</span> <span class="n">unique</span><span class="o">=</span><span class="kc">True</span><span class="p">,</span>
<span class="n">oracle_compress</span><span class="o">=</span><span class="mi">1</span><span class="p">)</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">oracle_compress</span></code> parameter accepts either an integer specifying the
number of prefix columns to compress, or <code class="docutils literal notranslate"><span class="pre">True</span></code> to use the default (all
columns for non-unique indexes, all but the last column for unique indexes).</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.0.0.</span></p>
</div>
</div>
</div>
<div class="section" id="oracle-data-types">
<h2>Oracle Data Types<a class="headerlink" href="#oracle-data-types" title="Permalink to this headline">¶</a></h2>
<p>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 <a class="reference internal" href="../core/type_basics.html#module-sqlalchemy.types" title="sqlalchemy.types"><code class="xref py py-mod docutils literal notranslate"><span class="pre">sqlalchemy.types</span></code></a> or from the local dialect:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy.dialects.oracle</span> <span class="k">import</span> \
<span class="n">BFILE</span><span class="p">,</span> <span class="n">BLOB</span><span class="p">,</span> <span class="n">CHAR</span><span class="p">,</span> <span class="n">CLOB</span><span class="p">,</span> <span class="n">DATE</span><span class="p">,</span> \
<span class="n">DOUBLE_PRECISION</span><span class="p">,</span> <span class="n">FLOAT</span><span class="p">,</span> <span class="n">INTERVAL</span><span class="p">,</span> <span class="n">LONG</span><span class="p">,</span> <span class="n">NCLOB</span><span class="p">,</span> <span class="n">NCHAR</span><span class="p">,</span> \
<span class="n">NUMBER</span><span class="p">,</span> <span class="n">NVARCHAR</span><span class="p">,</span> <span class="n">NVARCHAR2</span><span class="p">,</span> <span class="n">RAW</span><span class="p">,</span> <span class="n">TIMESTAMP</span><span class="p">,</span> <span class="n">VARCHAR</span><span class="p">,</span> \
<span class="n">VARCHAR2</span></pre></div>
</div>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.19: </span>Added <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.NCHAR" title="sqlalchemy.types.NCHAR"><code class="xref py py-class docutils literal notranslate"><span class="pre">NCHAR</span></code></a> to the list of datatypes
exported by the Oracle dialect.</p>
</div>
<p>Types which are specific to Oracle, or have Oracle-specific
construction arguments, are as follows:</p>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.BFILE">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">BFILE</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.BFILE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.LargeBinary</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.BFILE.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.BFILE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary.__init__" title="sqlalchemy.types.LargeBinary.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.LargeBinary" title="sqlalchemy.types.LargeBinary"><code class="xref py py-class docutils literal notranslate"><span class="pre">LargeBinary</span></code></a></p>
</div>
<p>Construct a LargeBinary type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.oracle.BFILE.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.BFILE.params.length">¶</a> – 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.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.DATE">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">DATE</code><span class="sig-paren">(</span><em>timezone=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.DATE" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.DateTime</span></code></a></p>
<p>Provide the oracle DATE type.</p>
<p>This type has no special Python behavior, except that it subclasses
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">types.DateTime</span></code></a>; this is to suit the fact that the Oracle
<code class="docutils literal notranslate"><span class="pre">DATE</span></code> type supports a time value.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.4.</span></p>
</div>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.DATE.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>timezone=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.DATE.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime.__init__" title="sqlalchemy.types.DateTime.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a></p>
</div>
<p>Construct a new <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.DateTime" title="sqlalchemy.types.DateTime"><code class="xref py py-class docutils literal notranslate"><span class="pre">DateTime</span></code></a>.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><p><span class="target" id="sqlalchemy.dialects.oracle.DATE.params.timezone"></span><strong>timezone</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.DATE.params.timezone">¶</a> – boolean. Indicates that the datetime type should
enable timezone support, if available on the
<strong>base date/time-holding type only</strong>. It is recommended
to make use of the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.TIMESTAMP" title="sqlalchemy.types.TIMESTAMP"><code class="xref py py-class docutils literal notranslate"><span class="pre">TIMESTAMP</span></code></a> 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.</p>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">DOUBLE_PRECISION</code><span class="sig-paren">(</span><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Float</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>asdecimal=False</em>, <em>decimal_return_scale=None</em>, <em>**kwargs</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float.__init__" title="sqlalchemy.types.Float.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">Float</span></code></a></p>
</div>
<p>Construct a Float.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.precision">¶</a> – the numeric precision for use in DDL <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.asdecimal">¶</a> – the same flag as that of <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a>, but
defaults to <code class="docutils literal notranslate"><span class="pre">False</span></code>. Note that setting this flag to <code class="docutils literal notranslate"><span class="pre">True</span></code>
results in floating point conversion.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.decimal_return_scale">¶</a> – <p>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.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.**kwargs"></span><strong>**kwargs</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION.params.**kwargs">¶</a> – <div class="deprecated">
<p><span class="versionmodified deprecated">Deprecated since version 0.9: </span>Additional keyword arguments are ignored by the base
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">Float</span></code></a> type, and keyword arguments will no longer
be accepted in a future release. For database specific floats
that support additional arguments, see that dialect’s
documentation for details, such as
<a class="reference internal" href="mysql.html#sqlalchemy.dialects.mysql.FLOAT" title="sqlalchemy.dialects.mysql.FLOAT"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.dialects.mysql.FLOAT</span></code></a>.</p>
</div>
</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.INTERVAL">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">INTERVAL</code><span class="sig-paren">(</span><em>day_precision=None</em>, <em>second_precision=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.INTERVAL" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_api.html#sqlalchemy.types.TypeEngine" title="sqlalchemy.types.TypeEngine"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.TypeEngine</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.INTERVAL.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>day_precision=None</em>, <em>second_precision=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.INTERVAL.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct an INTERVAL.</p>
<p>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).</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.oracle.INTERVAL.params.day_precision"></span><strong>day_precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.INTERVAL.params.day_precision">¶</a> – the day precision value. this is the number of
digits to store for the day field. Defaults to “2”</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.INTERVAL.params.second_precision"></span><strong>second_precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.INTERVAL.params.second_precision">¶</a> – the second precision value. this is the
number of digits to store for the fractional seconds field.
Defaults to “6”.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.NCLOB">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">NCLOB</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.NCLOB" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Text</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.NCLOB.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.NCLOB.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NCLOB.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NCLOB.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NCLOB.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NCLOB.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NCLOB.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NCLOB.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> 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 <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>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 <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> 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.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NCLOB.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NCLOB.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - otherwise,
SQLAlchemy is not guaranteed to handle the task of unicode
conversion. Note that this flag adds significant performance
overhead to row-fetching operations for backends that already
return unicode objects natively (which most DBAPIs do). This
flag should only be used as a last resort for reading
strings from a column with varied or corrupted encodings.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.NUMBER">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">NUMBER</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.NUMBER" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Numeric</span></code></a>, <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Integer" title="sqlalchemy.types.Integer"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Integer</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.NUMBER.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>precision=None</em>, <em>scale=None</em>, <em>asdecimal=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.NUMBER.__init__" title="Permalink to this definition">¶</a></dt>
<dd><p>Construct a Numeric.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NUMBER.params.precision"></span><strong>precision</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NUMBER.params.precision">¶</a> – the numeric precision for use in DDL <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NUMBER.params.scale"></span><strong>scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NUMBER.params.scale">¶</a> – the numeric scale for use in DDL <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code>.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NUMBER.params.asdecimal"></span><strong>asdecimal</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NUMBER.params.asdecimal">¶</a> – 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.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.NUMBER.params.decimal_return_scale"></span><strong>decimal_return_scale</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.NUMBER.params.decimal_return_scale">¶</a> – <p>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
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a> as well as the MySQL float types, will use the
value of “.scale” as the default for decimal_return_scale, if not
otherwise specified.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 0.9.0.</span></p>
</div>
</p></li>
</ul>
</dd>
</dl>
<p>When using the <code class="docutils literal notranslate"><span class="pre">Numeric</span></code> 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.</p>
<p>DBAPIs that return Decimal natively (e.g. psycopg2) will have
better accuracy and higher performance with a setting of <code class="docutils literal notranslate"><span class="pre">True</span></code>,
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 <em>will</em> incur an additional conversion
overhead, and is still subject to floating point data loss - in
which case <code class="docutils literal notranslate"><span class="pre">asdecimal=False</span></code> will at least remove the extra
conversion overhead.</p>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.LONG">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">LONG</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.LONG" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Text" title="sqlalchemy.types.Text"><code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types.Text</span></code></a></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.LONG.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em>, <em>collation=None</em>, <em>convert_unicode=False</em>, <em>unicode_error=None</em>, <em>_warn_on_bytestring=False</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.LONG.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.__init__" title="sqlalchemy.types.String.__init__"><code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code></a> <em>method of</em> <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a></p>
</div>
<p>Create a string-holding type.</p>
<dl class="field-list simple">
<dt class="field-odd">Parameters</dt>
<dd class="field-odd"><ul class="simple">
<li><p><span class="target" id="sqlalchemy.dialects.oracle.LONG.params.length"></span><strong>length</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.LONG.params.length">¶</a> – optional, a length for the column for use in
DDL and CAST expressions. May be safely omitted if no <code class="docutils literal notranslate"><span class="pre">CREATE</span>
<span class="pre">TABLE</span></code> will be issued. Certain databases may require a
<code class="docutils literal notranslate"><span class="pre">length</span></code> for use in DDL, and will raise an exception when
the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> DDL is issued if a <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code>
with no length is included. Whether the value is
interpreted as bytes or characters is database specific.</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.LONG.params.collation"></span><strong>collation</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.LONG.params.collation">¶</a> – <p>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.:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">cast</span><span class="p">,</span> <span class="n">select</span><span class="p">,</span> <span class="n">String</span>
<span class="gp">>>> </span><span class="nb">print</span> <span class="n">select</span><span class="p">([</span><span class="n">cast</span><span class="p">(</span><span class="s1">'some string'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="n">collation</span><span class="o">=</span><span class="s1">'utf8'</span><span class="p">))])</span>
<span class="go">SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1</span></pre></div>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.LONG.params.convert_unicode"></span><strong>convert_unicode</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.LONG.params.convert_unicode">¶</a> – <p>When set to <code class="docutils literal notranslate"><span class="pre">True</span></code>, the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> 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 <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.encoding" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.encoding</span></code></a> parameter
parameter passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a> as the encoding.</p>
<p>For the extremely rare case that Python Unicode
is to be encoded/decoded by SQLAlchemy on a backend
that <em>does</em> natively support Python Unicode,
the string value <code class="docutils literal notranslate"><span class="pre">"force"</span></code> can be passed here which will
cause SQLAlchemy’s encode/decode services to be
used unconditionally.</p>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>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 <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> flag is inherently a
legacy feature.</p>
</div>
<div class="admonition note">
<p class="admonition-title">Note</p>
<p>In the vast majority of cases, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> or
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.UnicodeText" title="sqlalchemy.types.UnicodeText"><code class="xref py py-class docutils literal notranslate"><span class="pre">UnicodeText</span></code></a> datatypes should be used for a
<a class="reference internal" href="../core/metadata.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><code class="xref py py-class docutils literal notranslate"><span class="pre">Column</span></code></a> 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.</p>
</div>
<div class="admonition seealso">
<p class="admonition-title">See also</p>
<p><a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.convert_unicode" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.convert_unicode</span></code></a> -
<a class="reference internal" href="../core/connections.html#sqlalchemy.engine.Engine" title="sqlalchemy.engine.Engine"><code class="xref py py-class docutils literal notranslate"><span class="pre">Engine</span></code></a>-wide parameter</p>
</div>
</p></li>
<li><p><span class="target" id="sqlalchemy.dialects.oracle.LONG.params.unicode_error"></span><strong>unicode_error</strong><a class="paramlink headerlink reference internal" href="#sqlalchemy.dialects.oracle.LONG.params.unicode_error">¶</a> – Optional, a method to use to handle Unicode
conversion errors. Behaves like the <code class="docutils literal notranslate"><span class="pre">errors</span></code> keyword argument to
the standard library’s <code class="docutils literal notranslate"><span class="pre">string.decode()</span></code> functions. This flag
requires that <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> is set to
<code class="docutils literal notranslate"><span class="pre">"force"</span></code> - otherwise,
SQLAlchemy is not guaranteed to handle the task of unicode
conversion. Note that this flag adds significant performance
overhead to row-fetching operations for backends that already
return unicode objects natively (which most DBAPIs do). This
flag should only be used as a last resort for reading
strings from a column with varied or corrupted encodings.</p></li>
</ul>
</dd>
</dl>
</dd></dl>
</dd></dl>
<dl class="class">
<dt id="sqlalchemy.dialects.oracle.RAW">
<em class="property">class </em><code class="descclassname">sqlalchemy.dialects.oracle.</code><code class="descname">RAW</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.RAW" title="Permalink to this definition">¶</a></dt>
<dd><p>Bases: <code class="xref py py-class docutils literal notranslate"><span class="pre">sqlalchemy.types._Binary</span></code></p>
<dl class="method">
<dt id="sqlalchemy.dialects.oracle.RAW.__init__">
<code class="descname">__init__</code><span class="sig-paren">(</span><em>length=None</em><span class="sig-paren">)</span><a class="headerlink" href="#sqlalchemy.dialects.oracle.RAW.__init__" title="Permalink to this definition">¶</a></dt>
<dd><div class="inherited-member docutils container">
<p><em>inherited from the</em> <code class="xref py py-meth docutils literal notranslate"><span class="pre">__init__()</span></code> <em>method of</em> <code class="xref py py-class docutils literal notranslate"><span class="pre">_Binary</span></code></p>
</div>
<p>Initialize self. See help(type(self)) for accurate signature.</p>
</dd></dl>
</dd></dl>
</div>
<div class="section" id="module-sqlalchemy.dialects.oracle.cx_oracle">
<span id="cx-oracle"></span><h2>cx_Oracle<a class="headerlink" href="#module-sqlalchemy.dialects.oracle.cx_oracle" title="Permalink to this headline">¶</a></h2>
<p>Support for the Oracle database via the cx-Oracle driver.</p>
<div class="section" id="dialect-oracle-cx_oracle-url">
<h3>DBAPI<a class="headerlink" href="#dialect-oracle-cx_oracle-url" title="Permalink to this headline">¶</a></h3>
<p>Documentation and download information (if applicable) for cx-Oracle is available at:
<a class="reference external" href="https://oracle.github.io/python-cx_Oracle/">https://oracle.github.io/python-cx_Oracle/</a></p>
</div>
<div class="section" id="dialect-oracle-cx_oracle-connect">
<h3>Connecting<a class="headerlink" href="#dialect-oracle-cx_oracle-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span>oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]</pre></div>
</div>
</p>
</div>
<div class="section" id="additional-connect-arguments">
<h3>Additional Connect Arguments<a class="headerlink" href="#additional-connect-arguments" title="Permalink to this headline">¶</a></h3>
<p>When connecting with the <code class="docutils literal notranslate"><span class="pre">dbname</span></code> URL token present, the <code class="docutils literal notranslate"><span class="pre">hostname</span></code>,
<code class="docutils literal notranslate"><span class="pre">port</span></code>, and <code class="docutils literal notranslate"><span class="pre">dbname</span></code> tokens are converted to a TNS name using the
<code class="docutils literal notranslate"><span class="pre">cx_Oracle.makedsn()</span></code> function. The URL below:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://user:pass@hostname/dbname"</span><span class="p">)</span></pre></div>
</div>
<p>Will be used to create the DSN as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="gp">>>> </span><span class="kn">import</span> <span class="nn">cx_Oracle</span>
<span class="gp">>>> </span><span class="n">cx_Oracle</span><span class="o">.</span><span class="n">makedsn</span><span class="p">(</span><span class="s2">"hostname"</span><span class="p">,</span> <span class="mi">1521</span><span class="p">,</span> <span class="n">sid</span><span class="o">=</span><span class="s2">"dbname"</span><span class="p">)</span>
<span class="go">'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=dbname)))'</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">service_name</span></code> parameter, also consumed by <code class="docutils literal notranslate"><span class="pre">cx_Oracle.makedsn()</span></code>, may
be specified in the URL query string, e.g. <code class="docutils literal notranslate"><span class="pre">?service_name=my_service</span></code>.</p>
<p>If <code class="docutils literal notranslate"><span class="pre">dbname</span></code> is not present, then the value of <code class="docutils literal notranslate"><span class="pre">hostname</span></code> in the
URL is used directly as the DSN passed to <code class="docutils literal notranslate"><span class="pre">cx_Oracle.connect()</span></code>.</p>
<p>Additional connection arguments may be sent to the <code class="docutils literal notranslate"><span class="pre">cx_Oracle.connect()</span></code>
function using the <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine.params.connect_args" title="sqlalchemy.create_engine"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">create_engine.connect_args</span></code></a> dictionary.
Any cx_Oracle parameter value and/or constant may be passed, such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">import</span> <span class="nn">cx_Oracle</span>
<span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"oracle+cx_oracle://user:pass@dsn"</span><span class="p">,</span>
<span class="n">connect_args</span><span class="o">=</span><span class="p">{</span>
<span class="s2">"mode"</span><span class="p">:</span> <span class="n">cx_Oracle</span><span class="o">.</span><span class="n">SYSDBA</span><span class="p">,</span>
<span class="s2">"events"</span><span class="p">:</span> <span class="kc">True</span>
<span class="p">}</span>
<span class="p">)</span></pre></div>
</div>
<p>There are also options that are consumed by the SQLAlchemy cx_oracle dialect
itself. These options are always passed directly to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>,
such as:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">e</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span>
<span class="s2">"oracle+cx_oracle://user:pass@dsn"</span><span class="p">,</span> <span class="n">coerce_to_unicode</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span></pre></div>
</div>
<p>The parameters accepted by the cx_oracle dialect are as follows:</p>
<ul class="simple">
<li><p><code class="docutils literal notranslate"><span class="pre">arraysize</span></code> - set the cx_oracle.arraysize value on cursors, defaulted
to 50. This setting is significant with cx_Oracle as the contents of LOB
objects are only readable within a “live” row (e.g. within a batch of
50 rows).</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">auto_convert_lobs</span></code> - defaults to True; See <a class="reference internal" href="#cx-oracle-lob"><span class="std std-ref">LOB Objects</span></a>.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">coerce_to_unicode</span></code> - see <a class="reference internal" href="#cx-oracle-unicode"><span class="std std-ref">Unicode</span></a> for detail.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">coerce_to_decimal</span></code> - see <a class="reference internal" href="#cx-oracle-numeric"><span class="std std-ref">Precision Numerics</span></a> for detail.</p></li>
<li><p><code class="docutils literal notranslate"><span class="pre">threaded</span></code> - this parameter is passed as the value of “threaded” to
<code class="docutils literal notranslate"><span class="pre">cx_Oracle.connect()</span></code> and defaults to True, which is the opposite of
cx_Oracle’s default. This parameter is deprecated and will default to
<code class="docutils literal notranslate"><span class="pre">False</span></code> in version 1.3 of SQLAlchemy.</p></li>
</ul>
</div>
<div class="section" id="unicode">
<span id="cx-oracle-unicode"></span><h3>Unicode<a class="headerlink" href="#unicode" title="Permalink to this headline">¶</a></h3>
<p>The cx_Oracle DBAPI as of version 5 fully supports unicode, and has the
ability to return string results as Python unicode objects natively.</p>
<p>When used in Python 3, cx_Oracle returns all strings as Python unicode objects
(that is, plain <code class="docutils literal notranslate"><span class="pre">str</span></code> in Python 3). In Python 2, it will return as Python
unicode those column values that are of type <code class="docutils literal notranslate"><span class="pre">NVARCHAR</span></code> or <code class="docutils literal notranslate"><span class="pre">NCLOB</span></code>. For
column values that are of type <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code> or other non-unicode string types,
it will return values as Python strings (e.g. bytestrings).</p>
<p>The cx_Oracle SQLAlchemy dialect presents several different options for the use
case of receiving <code class="docutils literal notranslate"><span class="pre">VARCHAR</span></code> column values as Python unicode objects under
Python 2:</p>
<ul>
<li><p>When using Core expression objects as well as the ORM, SQLAlchemy’s
unicode-decoding services are available, which are established by
using either the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Unicode" title="sqlalchemy.types.Unicode"><code class="xref py py-class docutils literal notranslate"><span class="pre">Unicode</span></code></a> datatype or by using the
<a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String" title="sqlalchemy.types.String"><code class="xref py py-class docutils literal notranslate"><span class="pre">String</span></code></a> datatype with <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.String.params.convert_unicode" title="sqlalchemy.types.String"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">String.convert_unicode</span></code></a> set
to True.</p></li>
<li><p>When using raw SQL strings, typing behavior can be added for unicode
conversion using the <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.text" title="sqlalchemy.sql.expression.text"><code class="xref py py-func docutils literal notranslate"><span class="pre">text()</span></code></a> construct:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">text</span><span class="p">,</span> <span class="n">Unicode</span>
<span class="n">result</span> <span class="o">=</span> <span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">text</span><span class="p">(</span><span class="s2">"select username from user"</span><span class="p">)</span><span class="o">.</span><span class="n">columns</span><span class="p">(</span><span class="n">username</span><span class="o">=</span><span class="n">Unicode</span><span class="p">))</span></pre></div>
</div>
</li>
<li><p>Otherwise, when using raw SQL strings sent directly to an <code class="docutils literal notranslate"><span class="pre">.execute()</span></code>
method without any Core typing behavior added, the flag
<code class="docutils literal notranslate"><span class="pre">coerce_to_unicode=True</span></code> flag can be passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>
which will add an unconditional unicode processor to cx_Oracle for all
string values:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://dsn"</span><span class="p">,</span> <span class="n">coerce_to_unicode</span><span class="o">=</span><span class="kc">True</span><span class="p">)</span></pre></div>
</div>
<p>The above approach will add significant latency to result-set fetches
of plain string values.</p>
</li>
</ul>
<div class="section" id="sending-string-values-as-unicode-or-non-unicode">
<h4>Sending String Values as Unicode or Non-Unicode<a class="headerlink" href="#sending-string-values-as-unicode-or-non-unicode" title="Permalink to this headline">¶</a></h4>
<p>As of SQLAlchemy 1.2.2, the cx_Oracle dialect unconditionally calls
<code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> for bound values that are passed as Python unicode objects.
In Python 3, all string values are Unicode; for cx_Oracle, this corresponds to
<code class="docutils literal notranslate"><span class="pre">cx_Oracle.NCHAR</span></code> being passed to <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> for that parameter.
In some edge cases, such as passing format specifiers for
the <code class="docutils literal notranslate"><span class="pre">trunc()</span></code> function, Oracle does not accept these as NCHAR:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">func</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">func</span><span class="o">.</span><span class="n">trunc</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">sysdate</span><span class="p">(),</span> <span class="s1">'dd'</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>In these cases, an error as follows may be raised:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">ORA</span><span class="o">-</span><span class="mi">01899</span><span class="p">:</span> <span class="n">bad</span> <span class="n">precision</span> <span class="n">specifier</span></pre></div>
</div>
<p>When this error is encountered, it may be necessary to pass the string value
with an explicit non-unicode type:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">func</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">literal</span>
<span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">String</span>
<span class="n">conn</span><span class="o">.</span><span class="n">execute</span><span class="p">(</span>
<span class="n">func</span><span class="o">.</span><span class="n">trunc</span><span class="p">(</span><span class="n">func</span><span class="o">.</span><span class="n">sysdate</span><span class="p">(),</span> <span class="n">literal</span><span class="p">(</span><span class="s1">'dd'</span><span class="p">,</span> <span class="n">String</span><span class="p">))</span>
<span class="p">)</span></pre></div>
</div>
<p>For full control over this <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> behavior, see the section
<a class="reference internal" href="#cx-oracle-setinputsizes"><span class="std std-ref">Fine grained control over cx_Oracle data binding performance with setinputsizes</span></a></p>
</div>
</div>
<div class="section" id="fine-grained-control-over-cx-oracle-data-binding-performance-with-setinputsizes">
<span id="cx-oracle-setinputsizes"></span><h3>Fine grained control over cx_Oracle data binding performance with setinputsizes<a class="headerlink" href="#fine-grained-control-over-cx-oracle-data-binding-performance-with-setinputsizes" title="Permalink to this headline">¶</a></h3>
<p>The cx_Oracle DBAPI has a deep and fundamental reliance upon the usage of the
DBAPI <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> call. The purpose of this call is to establish the
datatypes that are bound to a SQL statement for Python values being passed as
parameters. While virtually no other DBAPI assigns any use to the
<code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> call, the cx_Oracle DBAPI relies upon it heavily in its
interactions with the Oracle client interface, and in some scenarios it is not
possible for SQLAlchemy to know exactly how data should be bound, as some
settings can cause profoundly different performance characteristics, while
altering the type coercion behavior at the same time.</p>
<p>Users of the cx_Oracle dialect are <strong>strongly encouraged</strong> to read through
cx_Oracle’s list of built-in datatype symbols at
<a class="reference external" href="http://cx-oracle.readthedocs.io/en/latest/module.html#types">http://cx-oracle.readthedocs.io/en/latest/module.html#types</a>.
Note that in some cases, significant performance degradation can occur when
using these types vs. not, in particular when specifying <code class="docutils literal notranslate"><span class="pre">cx_Oracle.CLOB</span></code>.</p>
<p>On the SQLAlchemy side, the <a class="reference internal" href="../core/events.html#sqlalchemy.events.DialectEvents.do_setinputsizes" title="sqlalchemy.events.DialectEvents.do_setinputsizes"><code class="xref py py-meth docutils literal notranslate"><span class="pre">DialectEvents.do_setinputsizes()</span></code></a> event can
be used both for runtime visibility (e.g. logging) of the setinputsizes step as
well as to fully control how <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code> is used on a per-statement
basis.</p>
<div class="versionadded">
<p><span class="versionmodified added">New in version 1.2.9: </span>Added <code class="xref py py-meth docutils literal notranslate"><span class="pre">DialectEvents.setinputsizes()</span></code></p>
</div>
<div class="section" id="example-1-logging-all-setinputsizes-calls">
<h4>Example 1 - logging all setinputsizes calls<a class="headerlink" href="#example-1-logging-all-setinputsizes-calls" title="Permalink to this headline">¶</a></h4>
<p>The following example illustrates how to log the intermediary values from a
SQLAlchemy perspective before they are converted to the raw <code class="docutils literal notranslate"><span class="pre">setinputsizes()</span></code>
parameter dictionary. The keys of the dictionary are <a class="reference internal" href="../core/sqlelement.html#sqlalchemy.sql.expression.BindParameter" title="sqlalchemy.sql.expression.BindParameter"><code class="xref py py-class docutils literal notranslate"><span class="pre">BindParameter</span></code></a>
objects which have a <code class="docutils literal notranslate"><span class="pre">.key</span></code> and a <code class="docutils literal notranslate"><span class="pre">.type</span></code> attribute:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">event</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://scott:tiger@host/xe"</span><span class="p">)</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="s2">"do_setinputsizes"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">_log_setinputsizes</span><span class="p">(</span><span class="n">inputsizes</span><span class="p">,</span> <span class="n">cursor</span><span class="p">,</span> <span class="n">statement</span><span class="p">,</span> <span class="n">parameters</span><span class="p">,</span> <span class="n">context</span><span class="p">):</span>
<span class="k">for</span> <span class="n">bindparam</span><span class="p">,</span> <span class="n">dbapitype</span> <span class="ow">in</span> <span class="n">inputsizes</span><span class="o">.</span><span class="n">items</span><span class="p">():</span>
<span class="n">log</span><span class="o">.</span><span class="n">info</span><span class="p">(</span>
<span class="s2">"Bound parameter name: </span><span class="si">%s</span><span class="s2"> SQLAlchemy type: </span><span class="si">%r</span><span class="s2"> "</span>
<span class="s2">"DBAPI object: </span><span class="si">%s</span><span class="s2">"</span><span class="p">,</span>
<span class="n">bindparam</span><span class="o">.</span><span class="n">key</span><span class="p">,</span> <span class="n">bindparam</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">dbapitype</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="example-2-remove-all-bindings-to-clob">
<h4>Example 2 - remove all bindings to CLOB<a class="headerlink" href="#example-2-remove-all-bindings-to-clob" title="Permalink to this headline">¶</a></h4>
<p>The <code class="docutils literal notranslate"><span class="pre">CLOB</span></code> datatype in cx_Oracle incurs a significant performance overhead,
however is set by default for the <code class="docutils literal notranslate"><span class="pre">Text</span></code> type within the SQLAlchemy 1.2
series. This setting can be modified as follows:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="k">import</span> <span class="n">create_engine</span><span class="p">,</span> <span class="n">event</span>
<span class="kn">from</span> <span class="nn">cx_Oracle</span> <span class="k">import</span> <span class="n">CLOB</span>
<span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://scott:tiger@host/xe"</span><span class="p">)</span>
<span class="nd">@event</span><span class="o">.</span><span class="n">listens_for</span><span class="p">(</span><span class="n">engine</span><span class="p">,</span> <span class="s2">"do_setinputsizes"</span><span class="p">)</span>
<span class="k">def</span> <span class="nf">_remove_clob</span><span class="p">(</span><span class="n">inputsizes</span><span class="p">,</span> <span class="n">cursor</span><span class="p">,</span> <span class="n">statement</span><span class="p">,</span> <span class="n">parameters</span><span class="p">,</span> <span class="n">context</span><span class="p">):</span>
<span class="k">for</span> <span class="n">bindparam</span><span class="p">,</span> <span class="n">dbapitype</span> <span class="ow">in</span> <span class="nb">list</span><span class="p">(</span><span class="n">inputsizes</span><span class="o">.</span><span class="n">items</span><span class="p">()):</span>
<span class="k">if</span> <span class="n">dbapitype</span> <span class="ow">is</span> <span class="n">CLOB</span><span class="p">:</span>
<span class="k">del</span> <span class="n">inputsizes</span><span class="p">[</span><span class="n">bindparam</span><span class="p">]</span></pre></div>
</div>
</div>
</div>
<div class="section" id="cx-oracle-returning">
<span id="id2"></span><h3>RETURNING Support<a class="headerlink" href="#cx-oracle-returning" title="Permalink to this headline">¶</a></h3>
<p>The cx_Oracle dialect implements RETURNING using OUT parameters.
The dialect supports RETURNING fully, however cx_Oracle 6 is recommended
for complete support.</p>
</div>
<div class="section" id="lob-objects">
<span id="cx-oracle-lob"></span><h3>LOB Objects<a class="headerlink" href="#lob-objects" title="Permalink to this headline">¶</a></h3>
<p>cx_oracle returns oracle LOBs using the cx_oracle.LOB object. SQLAlchemy
converts these to strings so that the interface of the Binary type is
consistent with that of other backends, which takes place within a cx_Oracle
outputtypehandler.</p>
<p>cx_Oracle prior to version 6 would require that LOB objects be read before
a new batch of rows would be read, as determined by the <code class="docutils literal notranslate"><span class="pre">cursor.arraysize</span></code>.
As of the 6 series, this limitation has been lifted. Nevertheless, because
SQLAlchemy pre-reads these LOBs up front, this issue is avoided in any case.</p>
<p>To disable the auto “read()” feature of the dialect, the flag
<code class="docutils literal notranslate"><span class="pre">auto_convert_lobs=False</span></code> may be passed to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>. Under
the cx_Oracle 5 series, having this flag turned off means there is the chance
of reading from a stale LOB object if not read as it is fetched. With
cx_Oracle 6, this issue is resolved.</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>the LOB handling system has been greatly simplified
internally to make use of outputtypehandlers, and no longer makes use
of alternate “buffered” result set objects.</p>
</div>
</div>
<div class="section" id="two-phase-transactions-not-supported">
<h3>Two Phase Transactions Not Supported<a class="headerlink" href="#two-phase-transactions-not-supported" title="Permalink to this headline">¶</a></h3>
<p>Two phase transactions are <strong>not supported</strong> under cx_Oracle due to poor
driver support. As of cx_Oracle 6.0b1, the interface for
two phase transactions has been changed to be more of a direct pass-through
to the underlying OCI layer with less automation. The additional logic
to support this system is not implemented in SQLAlchemy.</p>
</div>
<div class="section" id="precision-numerics">
<span id="cx-oracle-numeric"></span><h3>Precision Numerics<a class="headerlink" href="#precision-numerics" title="Permalink to this headline">¶</a></h3>
<p>SQLAlchemy’s numeric types can handle receiving and returning values as Python
<code class="docutils literal notranslate"><span class="pre">Decimal</span></code> objects or float objects. When a <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a> object, or a
subclass such as <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Float" title="sqlalchemy.types.Float"><code class="xref py py-class docutils literal notranslate"><span class="pre">Float</span></code></a>, <a class="reference internal" href="#sqlalchemy.dialects.oracle.DOUBLE_PRECISION" title="sqlalchemy.dialects.oracle.DOUBLE_PRECISION"><code class="xref py py-class docutils literal notranslate"><span class="pre">oracle.DOUBLE_PRECISION</span></code></a> etc. is in
use, the <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric.params.asdecimal" title="sqlalchemy.types.Numeric"><code class="xref py py-paramref docutils literal notranslate"><span class="pre">Numeric.asdecimal</span></code></a> flag determines if values should be
coerced to <code class="docutils literal notranslate"><span class="pre">Decimal</span></code> upon return, or returned as float objects. To make
matters more complicated under Oracle, Oracle’s <code class="docutils literal notranslate"><span class="pre">NUMBER</span></code> type can also
represent integer values if the “scale” is zero, so the Oracle-specific
<a class="reference internal" href="#sqlalchemy.dialects.oracle.NUMBER" title="sqlalchemy.dialects.oracle.NUMBER"><code class="xref py py-class docutils literal notranslate"><span class="pre">oracle.NUMBER</span></code></a> type takes this into account as well.</p>
<p>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 <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a> in
use, as well as if no SQLAlchemy typing objects are present. There are
observed scenarios where Oracle may sends incomplete or ambiguous information
about the numeric types being returned, such as a query where the numeric types
are buried under multiple levels of subquery. The type handlers do their best
to make the right decision in all cases, deferring to the underlying cx_Oracle
DBAPI for all those cases where the driver can make the best decision.</p>
<p>When no typing objects are present, as when executing plain SQL strings, a
default “outputtypehandler” is present which will generally return numeric
values which specify precision and scale as Python <code class="docutils literal notranslate"><span class="pre">Decimal</span></code> objects. To
disable this coercion to decimal for performance reasons, pass the flag
<code class="docutils literal notranslate"><span class="pre">coerce_to_decimal=False</span></code> to <a class="reference internal" href="../core/engines.html#sqlalchemy.create_engine" title="sqlalchemy.create_engine"><code class="xref py py-func docutils literal notranslate"><span class="pre">create_engine()</span></code></a>:</p>
<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">engine</span> <span class="o">=</span> <span class="n">create_engine</span><span class="p">(</span><span class="s2">"oracle+cx_oracle://dsn"</span><span class="p">,</span> <span class="n">coerce_to_decimal</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span></pre></div>
</div>
<p>The <code class="docutils literal notranslate"><span class="pre">coerce_to_decimal</span></code> flag only impacts the results of plain string
SQL staements that are not otherwise associated with a <a class="reference internal" href="../core/type_basics.html#sqlalchemy.types.Numeric" title="sqlalchemy.types.Numeric"><code class="xref py py-class docutils literal notranslate"><span class="pre">Numeric</span></code></a>
SQLAlchemy type (or a subclass of such).</p>
<div class="versionchanged">
<p><span class="versionmodified changed">Changed in version 1.2: </span>The numeric handling system for cx_Oracle has been
reworked to take advantage of newer cx_Oracle features as well
as better integration of outputtypehandlers.</p>
</div>
</div>
</div>
<div class="section" id="module-sqlalchemy.dialects.oracle.zxjdbc">
<span id="zxjdbc"></span><h2>zxjdbc<a class="headerlink" href="#module-sqlalchemy.dialects.oracle.zxjdbc" title="Permalink to this headline">¶</a></h2>
<p>Support for the Oracle database via the zxJDBC for Jython driver.<div class="admonition note">
<p class="admonition-title">Note</p>
<p>Jython is not supported by current versions of SQLAlchemy. The
zxjdbc dialect should be considered as experimental.</p>
</div>
</p>
<div class="section" id="dialect-oracle-zxjdbc-url">
<h3>DBAPI<a class="headerlink" href="#dialect-oracle-zxjdbc-url" title="Permalink to this headline">¶</a></h3>
<p>Drivers for this database are available at:
<a class="reference external" href="http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html">http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html</a></p>
</div>
<div class="section" id="dialect-oracle-zxjdbc-connect">
<h3>Connecting<a class="headerlink" href="#dialect-oracle-zxjdbc-connect" title="Permalink to this headline">¶</a></h3>
<p>Connect String:<div class="highlight-default notranslate"><div class="highlight"><pre><span></span><span class="n">oracle</span><span class="o">+</span><span class="n">zxjdbc</span><span class="p">:</span><span class="o">//</span><span class="n">user</span><span class="p">:</span><span class="k">pass</span><span class="nd">@host</span><span class="o">/</span><span class="n">dbname</span></pre></div>
</div>
</p>
</div>
</div>
</div>
</div>
</div>
<div id="docs-bottom-navigation" class="docs-navigation-links, withsidebar">
Previous:
<a href="sqlite.html" title="previous chapter">SQLite</a>
Next:
<a href="mssql.html" title="next chapter">Microsoft SQL Server</a>
<div id="docs-copyright">
© <a href="../copyright.html">Copyright</a> 2007-2019, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 2.0.1.
</div>
</div>
</div>
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '1.2.19',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" id="documentation_options" data-url_root="../" src="../_static/documentation_options.js"></script>
<!-- begin iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/language_data.js"></script>
<!-- end iterate through sphinx environment script_files -->
<script type="text/javascript" src="../_static/detectmobile.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
</body>
</html>
distrib
>
Mageia
>
7
>
armv7hl
>
media
>
core-updates
>
by-pkgid
>
b0b6ffab06cbeede296e36ce94734bf8
>
files
>
790
