Mike Bayer avatar Mike Bayer committed b727d05

add caveats regarding RETURNING

Comments (0)

Files changed (2)


 The dialect supports several :func:`~sqlalchemy.create_engine()` arguments which
 affect the behavior of the dialect regardless of driver in use.
-* *use_ansi* - Use ANSI JOIN constructs (see the section on Oracle 8).  Defaults
+* ``use_ansi`` - Use ANSI JOIN constructs (see the section on Oracle 8).  Defaults
   to ``True``.  If ``False``, Oracle-8 compatible constructs are used for joins.
-* *optimize_limits* - defaults to ``False``. see the section on LIMIT/OFFSET.
+* ``optimize_limits`` - defaults to ``False``. see the section on LIMIT/OFFSET.
-* *use_binds_for_limits* - defaults to ``True``.  see the section on LIMIT/OFFSET.
+* ``use_binds_for_limits`` - defaults to ``True``.  see the section on LIMIT/OFFSET.
 Auto Increment Behavior
 which installs a select compiler that overrides the generation of limit/offset with
 a window function.
+The Oracle database supports a limited form of RETURNING, in order to retrieve result
+sets of matched rows from INSERT, UPDATE and DELETE statements.  Oracle's
+RETURNING..INTO syntax only supports one row being returned, as it relies upon
+OUT parameters in order to function.  In addition, supported DBAPIs have further
+limitations (see :ref:`cx_oracle_returning`).
+SQLAlchemy's "implicit returning" feature, which employs RETURNING within an INSERT
+and sometimes an UPDATE statement in order to fetch newly generated primary key values
+and other SQL defaults and expressions, is normally enabled on the Oracle
+backend.  By default, "implicit returning" typically only fetches the value of a
+single ``nextval(some_seq)`` expression embedded into an INSERT in order to increment
+a sequence within an INSERT statement and get the value back at the same time.
+To disable this feature across the board, specify ``implicit_returning=False`` to
+    engine = create_engine("oracle://scott:tiger@dsn", implicit_returning=False)
+Implicit returning can also be disabled on a table-by-table basis as a table option::
+    # Core Table
+    my_table = Table("my_table", metadata, ..., implicit_returning=False)
+    # declarative
+    class MyClass(Base):
+        __tablename__ = 'my_table'
+        __table_args__ = {"implicit_returning": False}
+.. seealso::
+    :ref:`cx_oracle_returning` - additional cx_oracle-specific restrictions on implicit returning.


 Note that this behavior is disabled when Oracle 8 is detected, as it has been
 observed that issues remain when passing Python unicodes to cx_oracle with Oracle 8.
+.. _cx_oracle_returning:
+cx_oracle supports a limited subset of Oracle's already limited RETURNING support.
+Typically, results can only be guaranteed for at most one column being returned;
+this is the typical case when SQLAlchemy uses RETURNING to get just the value of a
+primary-key-associated sequence value.    Additional column expressions will
+cause problems in a non-determinative way, due to cx_oracle's lack of support for
+the OCI_DATA_AT_EXEC API which is required for more complex RETURNING scenarios.
+.. seealso::
+    http://docs.oracle.com/cd/B10501_01/appdev.920/a96584/oci05bnd.htm#420693 - OCI documentation for RETURNING
+    http://sourceforge.net/mailarchive/message.php?msg_id=31338136 - cx_oracle developer commentary
 LOB Objects
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.