diff options
Diffstat (limited to 'lib/sqlalchemy/dialects/oracle')
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/__init__.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/base.py | 87 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/cx_oracle.py | 46 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/oracle/zxjdbc.py | 2 |
4 files changed, 102 insertions, 35 deletions
diff --git a/lib/sqlalchemy/dialects/oracle/__init__.py b/lib/sqlalchemy/dialects/oracle/__init__.py index 576790707..070e387d0 100644 --- a/lib/sqlalchemy/dialects/oracle/__init__.py +++ b/lib/sqlalchemy/dialects/oracle/__init__.py @@ -1,5 +1,5 @@ # oracle/__init__.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 272bd1740..218a7ccfc 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -1,5 +1,5 @@ # oracle/base.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php @@ -16,12 +16,12 @@ Connect Arguments 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 ----------------------- @@ -99,6 +99,41 @@ http://www.sqlalchemy.org/trac/wiki/UsageRecipes/WindowFunctionsByDefault which installs a select compiler that overrides the generation of limit/offset with a window function. +RETURNING Support +----------------- + +The Oracle database supports a limited form of RETURNING, in order to retrieve result +sets of matched rows from INSERT, UPDATE and DELETE statements. Oracle's +RETURNING..INTO syntax only supports one row being returned, as it relies upon +OUT parameters in order to function. In addition, supported DBAPIs have further +limitations (see :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 +:func:`.create_engine`:: + + engine = create_engine("oracle://scott:tiger@dsn", implicit_returning=False) + +Implicit returning can also be disabled on a table-by-table basis as a table option:: + + # Core Table + my_table = Table("my_table", metadata, ..., implicit_returning=False) + + + # declarative + class MyClass(Base): + __tablename__ = 'my_table' + __table_args__ = {"implicit_returning": False} + +.. seealso:: + + :ref:`cx_oracle_returning` - additional cx_oracle-specific restrictions on implicit returning. + ON UPDATE CASCADE ----------------- @@ -133,9 +168,10 @@ Synonym/DBLINK Reflection ------------------------- When using reflection with Table objects, the dialect can optionally search for tables -indicated by synonyms that reference DBLINK-ed tables by passing the flag -oracle_resolve_synonyms=True as a keyword argument to the Table construct. If DBLINK -is not in use this flag should be left off. +indicated by synonyms, either in local or remote schemas or accessed over DBLINK, +by passing the flag oracle_resolve_synonyms=True as a +keyword argument to the Table construct. If synonyms are not in use +this flag should be left off. """ @@ -145,7 +181,7 @@ from sqlalchemy import util, sql from sqlalchemy.engine import default, base, reflection from sqlalchemy.sql import compiler, visitors, expression from sqlalchemy.sql import operators as sql_operators, functions as sql_functions -from sqlalchemy import types as sqltypes +from sqlalchemy import types as sqltypes, schema as sa_schema from sqlalchemy.types import VARCHAR, NVARCHAR, CHAR, DATE, DATETIME, \ BLOB, CLOB, TIMESTAMP, FLOAT @@ -362,7 +398,9 @@ class OracleTypeCompiler(compiler.GenericTypeCompiler): return self._visit_varchar(type_, '', '') def _visit_varchar(self, type_, n, num): - if not n and self.dialect._supports_char_length: + if not type_.length: + return "%(n)sVARCHAR%(two)s" % {'two': num, 'n': n} + elif not n and self.dialect._supports_char_length: varchar = "VARCHAR%(two)s(%(length)s CHAR)" return varchar % {'length': type_.length, 'two': num} else: @@ -521,7 +559,6 @@ class OracleCompiler(compiler.SQLCompiler): return self.process(alias.original, **kwargs) def returning_clause(self, stmt, returning_cols): - columns = [] binds = [] for i, column in enumerate(expression._select_iterables(returning_cols)): @@ -595,7 +632,7 @@ class OracleCompiler(compiler.SQLCompiler): # If needed, add the ora_rn, and wrap again with offset. if select._offset is None: - limitselect.for_update = select.for_update + limitselect._for_update_arg = select._for_update_arg select = limitselect else: limitselect = limitselect.column( @@ -614,7 +651,7 @@ class OracleCompiler(compiler.SQLCompiler): offsetselect.append_whereclause( sql.literal_column("ora_rn") > offset_value) - offsetselect.for_update = select.for_update + offsetselect._for_update_arg = select._for_update_arg select = offsetselect kwargs['iswrapper'] = getattr(select, '_is_wrapper', False) @@ -626,10 +663,19 @@ class OracleCompiler(compiler.SQLCompiler): def for_update_clause(self, select): if self.is_subquery(): return "" - elif select.for_update == "nowait": - return " FOR UPDATE NOWAIT" - else: - return super(OracleCompiler, self).for_update_clause(select) + + tmp = ' FOR UPDATE' + + if select._for_update_arg.of: + tmp += ' OF ' + ', '.join( + self.process(elem) for elem in + select._for_update_arg.of + ) + + if select._for_update_arg.nowait: + tmp += " NOWAIT" + + return tmp class OracleDDLCompiler(compiler.DDLCompiler): @@ -708,6 +754,10 @@ class OracleDialect(default.DefaultDialect): reflection_options = ('oracle_resolve_synonyms', ) + construct_arguments = [ + (sa_schema.Table, {"resolve_synonyms": False}) + ] + def __init__(self, use_ansi=True, optimize_limits=False, @@ -800,14 +850,15 @@ class OracleDialect(default.DefaultDialect): returns the actual name, owner, dblink name, and synonym name if found. """ - q = "SELECT owner, table_owner, table_name, db_link, synonym_name FROM all_synonyms WHERE " + q = "SELECT owner, table_owner, table_name, db_link, "\ + "synonym_name FROM all_synonyms WHERE " clauses = [] params = {} if desired_synonym: clauses.append("synonym_name = :synonym_name") params['synonym_name'] = desired_synonym if desired_owner: - clauses.append("table_owner = :desired_owner") + clauses.append("owner = :desired_owner") params['desired_owner'] = desired_owner if desired_table: clauses.append("table_name = :tname") diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py index e013799db..599eb21a3 100644 --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py @@ -1,5 +1,5 @@ # oracle/cx_oracle.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php @@ -17,11 +17,11 @@ Additional Connect Arguments When connecting with ``dbname`` present, the host, port, and dbname tokens are converted to a TNS name using -the cx_oracle :func:`makedsn()` function. Otherwise, the host token is taken +the cx_oracle ``makedsn()`` function. Otherwise, the host token is taken directly as a TNS name. Additional arguments which may be specified either as query string arguments -on the URL, or as keyword arguments to :func:`~sqlalchemy.create_engine()` are: +on the URL, or as keyword arguments to :func:`.create_engine()` are: * allow_twophase - enable two-phase transactions. Defaults to ``True``. @@ -65,6 +65,27 @@ of the encoding to be used. 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: + +RETURNING Support +----------------- + +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 ----------- @@ -75,7 +96,7 @@ like result.fetchmany() and result.fetchall(). This means that by default, LOB objects are fully fetched unconditionally by SQLAlchemy, and the linkage to a live cursor is broken. -To disable this processing, pass ``auto_convert_lobs=False`` to :func:`create_engine()`. +To disable this processing, pass ``auto_convert_lobs=False`` to :func:`.create_engine()`. Two Phase Transaction Support ----------------------------- @@ -108,7 +129,7 @@ the application can make one of several choices: * For ad-hoc two-phase operations without disabling pooling, the DBAPI connection in use can be evicted from the connection pool using the - :class:`.Connection.detach` method. + :meth:`.Connection.detach` method. .. versionchanged:: 0.8.0b2,0.7.10 Support for cx_oracle prepared transactions has been implemented @@ -211,10 +232,7 @@ class _OracleNumeric(sqltypes.Numeric): if dialect.supports_native_decimal: if self.asdecimal: - if self.scale is None: - fstring = "%.10f" - else: - fstring = "%%.%df" % self.scale + fstring = "%%.%df" % self._effective_decimal_return_scale def to_decimal(value): if value is None: @@ -362,7 +380,8 @@ class _OracleRowid(oracle.ROWID): class OracleCompiler_cx_oracle(OracleCompiler): - def bindparam_string(self, name, quote=None, **kw): + def bindparam_string(self, name, **kw): + quote = getattr(name, 'quote', None) if quote is True or quote is not False and \ self.preparer._bindparam_requires_quotes(name): quoted_name = '"%s"' % name @@ -506,7 +525,6 @@ class ReturningResultProxy(_result.FullyBufferedResultProxy): def _cursor_description(self): returning = self.context.compiled.returning - return [ ("ret_%d" % i, None) for i, col in enumerate(returning) @@ -730,9 +748,6 @@ class OracleDialect_cx_oracle(OracleDialect): 255, outconverter=self._detect_decimal, arraysize=cursor.arraysize) - # allow all strings to come back natively as Unicode - elif defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR): - return cursor.var(util.text_type, size, cursor.arraysize) def on_connect(conn): conn.outputtypehandler = output_type_handler @@ -805,8 +820,9 @@ class OracleDialect_cx_oracle(OracleDialect): # ORA-03113: end-of-file on communication channel # ORA-03135: connection lost contact # ORA-01033: ORACLE initialization or shutdown in progress + # ORA-02396: exceeded maximum idle time, please connect again # TODO: Others ? - return error.code in (28, 3114, 3113, 3135, 1033) + return error.code in (28, 3114, 3113, 3135, 1033, 2396) else: return False diff --git a/lib/sqlalchemy/dialects/oracle/zxjdbc.py b/lib/sqlalchemy/dialects/oracle/zxjdbc.py index ad53b89a1..710645b23 100644 --- a/lib/sqlalchemy/dialects/oracle/zxjdbc.py +++ b/lib/sqlalchemy/dialects/oracle/zxjdbc.py @@ -1,5 +1,5 @@ # oracle/zxjdbc.py -# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file> +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php |