From 7bf231232c0f8eb82a88dc75f2759ddaa39265ae Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 27 Aug 2013 21:37:22 -0400 Subject: - cx_oracle seems to have a bug here though it is hard to track down - cx_oracle dialect doesn't use normal col names, lets just not rely on that for now --- lib/sqlalchemy/dialects/oracle/base.py | 1 - 1 file changed, 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 272bd1740..290f259ba 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -521,7 +521,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)): -- cgit v1.2.1 From b727d05ce1d23289878899b8982aeba09b824d62 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 2 Sep 2013 12:33:49 -0400 Subject: add caveats regarding RETURNING --- lib/sqlalchemy/dialects/oracle/base.py | 41 +++++++++++++++++++++++++++++++--- 1 file changed, 38 insertions(+), 3 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 290f259ba..54c254c0f 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -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 ----------------- -- cgit v1.2.1 From 5070c81ab963c1432bbbecf38d4cad7ac7b81652 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 25 Oct 2013 19:11:53 -0400 Subject: - Fixed bug where Oracle table reflection using synonyms would fail if the synonym and the table were in different remote schemas. Patch to fix courtesy Kyle Derr. [ticket:2853] --- lib/sqlalchemy/dialects/oracle/base.py | 12 +++++++----- 1 file changed, 7 insertions(+), 5 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 54c254c0f..1f5e05cba 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -168,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. """ @@ -834,14 +835,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") -- cgit v1.2.1 From 741da873841012d893ec08bd77a5ecc9237eaab8 Mon Sep 17 00:00:00 2001 From: Mario Lassnig Date: Thu, 14 Nov 2013 20:18:52 +0100 Subject: added ORM support --- lib/sqlalchemy/dialects/oracle/base.py | 14 ++++++++++++-- 1 file changed, 12 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 1f5e05cba..74441e9a8 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -661,8 +661,18 @@ class OracleCompiler(compiler.SQLCompiler): def for_update_clause(self, select): if self.is_subquery(): return "" - elif select.for_update == "nowait": - return " FOR UPDATE NOWAIT" + + tmp = ' FOR UPDATE' + + if isinstance(select.for_update_of, list): + tmp += ' OF ' + ', '.join(['.'.join(of) for of in select.for_update_of]) + elif isinstance(select.for_update_of, tuple): + tmp += ' OF ' + '.'.join(select.for_update_of) + + if select.for_update == 'nowait': + return tmp + ' NOWAIT' + elif select.for_update: + return tmp else: return super(OracleCompiler, self).for_update_clause(select) -- cgit v1.2.1 From 467784e89c0817a74df32db4b12bd8b3e28a05df Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 22 Nov 2013 17:56:35 -0500 Subject: Fixed bug where Oracle ``VARCHAR`` types given with no length (e.g. for a ``CAST`` or similar) would incorrectly render ``None CHAR`` or similar. [ticket:2870] --- lib/sqlalchemy/dialects/oracle/base.py | 4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 1f5e05cba..e7263ba52 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -398,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: -- cgit v1.2.1 From e9aaf8eb66343f247b1ec2189707f820e20a0629 Mon Sep 17 00:00:00 2001 From: Mario Lassnig Date: Thu, 28 Nov 2013 14:50:41 +0100 Subject: added LockmodeArgs --- lib/sqlalchemy/dialects/oracle/base.py | 22 ++++++++++++++++------ 1 file changed, 16 insertions(+), 6 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 74441e9a8..c0d9732b4 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -664,14 +664,24 @@ class OracleCompiler(compiler.SQLCompiler): tmp = ' FOR UPDATE' - if isinstance(select.for_update_of, list): - tmp += ' OF ' + ', '.join(['.'.join(of) for of in select.for_update_of]) - elif isinstance(select.for_update_of, tuple): - tmp += ' OF ' + '.'.join(select.for_update_of) + # backwards compatibility + if isinstance(select.for_update, bool): + if select.for_update: + return tmp + elif isinstance(select.for_update, str): + if select.for_update == 'nowait': + return tmp + ' NOWAIT' + else: + return tmp + + if isinstance(select.for_update.of, list): + tmp += ' OF ' + ', '.join(['.'.join(of) for of in select.for_update.of]) + elif isinstance(select.for_update.of, tuple): + tmp += ' OF ' + '.'.join(select.for_update.of) - if select.for_update == 'nowait': + if select.for_update.mode == 'update_nowait': return tmp + ' NOWAIT' - elif select.for_update: + elif select.for_update.mode == 'update': return tmp else: return super(OracleCompiler, self).for_update_clause(select) -- cgit v1.2.1 From bb60a8ad946dd331f546f06a156b7ebb87d1709d Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Nov 2013 12:37:15 -0500 Subject: - work in progress, will squash --- lib/sqlalchemy/dialects/oracle/base.py | 31 ++++++++++--------------------- 1 file changed, 10 insertions(+), 21 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 0bd009807..a3c31b7cc 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -632,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( @@ -651,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) @@ -666,27 +666,16 @@ class OracleCompiler(compiler.SQLCompiler): tmp = ' FOR UPDATE' - # backwards compatibility - if isinstance(select.for_update, bool): - if select.for_update: - return tmp - elif isinstance(select.for_update, str): - if select.for_update == 'nowait': - return tmp + ' NOWAIT' - else: - return tmp + if select._for_update_arg.nowait: + tmp += " NOWAIT" - if isinstance(select.for_update.of, list): - tmp += ' OF ' + ', '.join(['.'.join(of) for of in select.for_update.of]) - elif isinstance(select.for_update.of, tuple): - tmp += ' OF ' + '.'.join(select.for_update.of) + if select._for_update_arg.of: + tmp += ' OF ' + ', '.join( + self._process(elem) for elem in + select._for_update_arg.of + ) - if select.for_update.mode == 'update_nowait': - return tmp + ' NOWAIT' - elif select.for_update.mode == 'update': - return tmp - else: - return super(OracleCompiler, self).for_update_clause(select) + return tmp class OracleDDLCompiler(compiler.DDLCompiler): -- cgit v1.2.1 From 4aaf3753d75c68050c136e734c29aae5ff9504b4 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 28 Nov 2013 22:25:09 -0500 Subject: - fix up rendering of "of" - move out tests, dialect specific out of compiler, compiler tests use new API, legacy API tests in test_selecatble - add support for adaptation of ForUpdateArg, alias support in compilers --- lib/sqlalchemy/dialects/oracle/base.py | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index a3c31b7cc..ba69c3d1f 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -666,15 +666,15 @@ class OracleCompiler(compiler.SQLCompiler): tmp = ' FOR UPDATE' - if select._for_update_arg.nowait: - tmp += " NOWAIT" - if select._for_update_arg.of: tmp += ' OF ' + ', '.join( - self._process(elem) for elem in + self.process(elem) for elem in select._for_update_arg.of ) + if select._for_update_arg.nowait: + tmp += " NOWAIT" + return tmp -- cgit v1.2.1 From f89d4d216bd7605c920b7b8a10ecde6bfea2238c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 5 Jan 2014 16:57:05 -0500 Subject: - happy new year --- lib/sqlalchemy/dialects/oracle/base.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index ba69c3d1f..e5a160443 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 +# Copyright (C) 2005-2014 the SQLAlchemy authors and contributors # # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -- cgit v1.2.1 From 1af8e2491dcbed723d2cdafd44fd37f1a6908e91 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 18 Jan 2014 19:26:56 -0500 Subject: - implement kwarg validation and type system for dialect-specific arguments; [ticket:2866] - add dialect specific kwarg functionality to ForeignKeyConstraint, ForeignKey --- lib/sqlalchemy/dialects/oracle/base.py | 2 ++ 1 file changed, 2 insertions(+) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index e5a160443..74a587d0b 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -754,6 +754,8 @@ class OracleDialect(default.DefaultDialect): reflection_options = ('oracle_resolve_synonyms', ) + construct_arguments = [] + def __init__(self, use_ansi=True, optimize_limits=False, -- cgit v1.2.1 From 8e1a4fdced253a58af309c93c24a8a492b646bb7 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 19 Jan 2014 00:34:37 -0500 Subject: - some test fixes - clean up some shenanigans in reflection --- lib/sqlalchemy/dialects/oracle/base.py | 6 ++++-- 1 file changed, 4 insertions(+), 2 deletions(-) (limited to 'lib/sqlalchemy/dialects/oracle/base.py') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 74a587d0b..218a7ccfc 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -181,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 @@ -754,7 +754,9 @@ class OracleDialect(default.DefaultDialect): reflection_options = ('oracle_resolve_synonyms', ) - construct_arguments = [] + construct_arguments = [ + (sa_schema.Table, {"resolve_synonyms": False}) + ] def __init__(self, use_ansi=True, -- cgit v1.2.1