codekingpro/portable-devtools
114k
1# dialects/oracle/base.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7# mypy: ignore-errors
8
9
10r"""
11.. dialect:: oracle
12 :name: Oracle
13 :full_support: 18c
14 :normal_support: 11+
15 :best_effort: 9+
16
17
18Auto Increment Behavior
19-----------------------
20
21SQLAlchemy Table objects which include integer primary keys are usually
22assumed to have "autoincrementing" behavior, meaning they can generate their
23own primary key values upon INSERT. For use within Oracle, two options are
24available, which are the use of IDENTITY columns (Oracle 12 and above only)
25or the association of a SEQUENCE with the column.
26
27Specifying GENERATED AS IDENTITY (Oracle 12 and above)
28~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
29
30Starting from version 12 Oracle can make use of identity columns using
31the :class:`_sql.Identity` to specify the autoincrementing behavior::
32
33 t = Table('mytable', metadata,
34 Column('id', Integer, Identity(start=3), primary_key=True),
35 Column(...), ...
36 )
37
38The CREATE TABLE for the above :class:`_schema.Table` object would be:
39
40.. sourcecode:: sql
41
42 CREATE TABLE mytable (
43 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 3),
44 ...,
45 PRIMARY KEY (id)
46 )
47
48The :class:`_schema.Identity` object support many options to control the
49"autoincrementing" behavior of the column, like the starting value, the
50incrementing value, etc.
51In addition to the standard options, Oracle supports setting
52:paramref:`_schema.Identity.always` to ``None`` to use the default
53generated mode, rendering GENERATED AS IDENTITY in the DDL. It also supports
54setting :paramref:`_schema.Identity.on_null` to ``True`` to specify ON NULL
55in conjunction with a 'BY DEFAULT' identity column.
56
57Using a SEQUENCE (all Oracle versions)
58~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
59
60Older version of Oracle had no "autoincrement"
61feature, SQLAlchemy relies upon sequences to produce these values. With the
62older Oracle versions, *a sequence must always be explicitly specified to
63enable autoincrement*. This is divergent with the majority of documentation
64examples which assume the usage of an autoincrement-capable database. To
65specify sequences, use the sqlalchemy.schema.Sequence object which is passed
66to a Column construct::
67
68 t = Table('mytable', metadata,
69 Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
70 Column(...), ...
71 )
72
73This step is also required when using table reflection, i.e. autoload_with=engine::
74
75 t = Table('mytable', metadata,
76 Column('id', Integer, Sequence('id_seq', start=1), primary_key=True),
77 autoload_with=engine
78 )
79
80.. versionchanged:: 1.4 Added :class:`_schema.Identity` construct
81 in a :class:`_schema.Column` to specify the option of an autoincrementing
82 column.
83
84.. _oracle_isolation_level:
85
86Transaction Isolation Level / Autocommit
87----------------------------------------
88
89The Oracle database supports "READ COMMITTED" and "SERIALIZABLE" modes of
90isolation. The AUTOCOMMIT isolation level is also supported by the cx_Oracle
91dialect.
92
93To set using per-connection execution options::
94
95 connection = engine.connect()
96 connection = connection.execution_options(
97 isolation_level="AUTOCOMMIT"
98 )
99
100For ``READ COMMITTED`` and ``SERIALIZABLE``, the Oracle dialect sets the
101level at the session level using ``ALTER SESSION``, which is reverted back
102to its default setting when the connection is returned to the connection
103pool.
104
105Valid values for ``isolation_level`` include:
106
107* ``READ COMMITTED``
108* ``AUTOCOMMIT``
109* ``SERIALIZABLE``
110
111.. note:: The implementation for the
112 :meth:`_engine.Connection.get_isolation_level` method as implemented by the
113 Oracle dialect necessarily forces the start of a transaction using the
114 Oracle LOCAL_TRANSACTION_ID function; otherwise no level is normally
115 readable.
116
117 Additionally, the :meth:`_engine.Connection.get_isolation_level` method will
118 raise an exception if the ``v$transaction`` view is not available due to
119 permissions or other reasons, which is a common occurrence in Oracle
120 installations.
121
122 The cx_Oracle dialect attempts to call the
123 :meth:`_engine.Connection.get_isolation_level` method when the dialect makes
124 its first connection to the database in order to acquire the
125 "default"isolation level. This default level is necessary so that the level
126 can be reset on a connection after it has been temporarily modified using
127 :meth:`_engine.Connection.execution_options` method. In the common event
128 that the :meth:`_engine.Connection.get_isolation_level` method raises an
129 exception due to ``v$transaction`` not being readable as well as any other
130 database-related failure, the level is assumed to be "READ COMMITTED". No
131 warning is emitted for this initial first-connect condition as it is
132 expected to be a common restriction on Oracle databases.
133
134.. versionadded:: 1.3.16 added support for AUTOCOMMIT to the cx_oracle dialect
135 as well as the notion of a default isolation level
136
137.. versionadded:: 1.3.21 Added support for SERIALIZABLE as well as live
138 reading of the isolation level.
139
140.. versionchanged:: 1.3.22 In the event that the default isolation
141 level cannot be read due to permissions on the v$transaction view as
142 is common in Oracle installations, the default isolation level is hardcoded
143 to "READ COMMITTED" which was the behavior prior to 1.3.21.
144
145.. seealso::
146
147 :ref:`dbapi_autocommit`
148
149Identifier Casing
150-----------------
151
152In Oracle, the data dictionary represents all case insensitive identifier
153names using UPPERCASE text. SQLAlchemy on the other hand considers an
154all-lower case identifier name to be case insensitive. The Oracle dialect
155converts all case insensitive identifiers to and from those two formats during
156schema level communication, such as reflection of tables and indexes. Using
157an UPPERCASE name on the SQLAlchemy side indicates a case sensitive
158identifier, and SQLAlchemy will quote the name - this will cause mismatches
159against data dictionary data received from Oracle, so unless identifier names
160have been truly created as case sensitive (i.e. using quoted names), all
161lowercase names should be used on the SQLAlchemy side.
162
163.. _oracle_max_identifier_lengths:
164
165Max Identifier Lengths
166----------------------
167
168Oracle has changed the default max identifier length as of Oracle Server
169version 12.2. Prior to this version, the length was 30, and for 12.2 and
170greater it is now 128. This change impacts SQLAlchemy in the area of
171generated SQL label names as well as the generation of constraint names,
172particularly in the case where the constraint naming convention feature
173described at :ref:`constraint_naming_conventions` is being used.
174
175To assist with this change and others, Oracle includes the concept of a
176"compatibility" version, which is a version number that is independent of the
177actual server version in order to assist with migration of Oracle databases,
178and may be configured within the Oracle server itself. This compatibility
179version is retrieved using the query ``SELECT value FROM v$parameter WHERE
180name = 'compatible';``. The SQLAlchemy Oracle dialect, when tasked with
181determining the default max identifier length, will attempt to use this query
182upon first connect in order to determine the effective compatibility version of
183the server, which determines what the maximum allowed identifier length is for
184the server. If the table is not available, the server version information is
185used instead.
186
187As of SQLAlchemy 1.4, the default max identifier length for the Oracle dialect
188is 128 characters. Upon first connect, the compatibility version is detected
189and if it is less than Oracle version 12.2, the max identifier length is
190changed to be 30 characters. In all cases, setting the
191:paramref:`_sa.create_engine.max_identifier_length` parameter will bypass this
192change and the value given will be used as is::
193
194 engine = create_engine(
195 "oracle+cx_oracle://scott:tiger@oracle122",
196 max_identifier_length=30)
197
198The maximum identifier length comes into play both when generating anonymized
199SQL labels in SELECT statements, but more crucially when generating constraint
200names from a naming convention. It is this area that has created the need for
201SQLAlchemy to change this default conservatively. For example, the following
202naming convention produces two very different constraint names based on the
203identifier length::
204
205 from sqlalchemy import Column
206 from sqlalchemy import Index
207 from sqlalchemy import Integer
208 from sqlalchemy import MetaData
209 from sqlalchemy import Table
210 from sqlalchemy.dialects import oracle
211 from sqlalchemy.schema import CreateIndex
212
213 m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"})
214
215 t = Table(
216 "t",
217 m,
218 Column("some_column_name_1", Integer),
219 Column("some_column_name_2", Integer),
220 Column("some_column_name_3", Integer),
221 )
222
223 ix = Index(
224 None,
225 t.c.some_column_name_1,
226 t.c.some_column_name_2,
227 t.c.some_column_name_3,
228 )
229
230 oracle_dialect = oracle.dialect(max_identifier_length=30)
231 print(CreateIndex(ix).compile(dialect=oracle_dialect))
232
233With an identifier length of 30, the above CREATE INDEX looks like::
234
235 CREATE INDEX ix_some_column_name_1s_70cd ON t
236 (some_column_name_1, some_column_name_2, some_column_name_3)
237
238However with length=128, it becomes::
239
240 CREATE INDEX ix_some_column_name_1some_column_name_2some_column_name_3 ON t
241 (some_column_name_1, some_column_name_2, some_column_name_3)
242
243Applications which have run versions of SQLAlchemy prior to 1.4 on an Oracle
244server version 12.2 or greater are therefore subject to the scenario of a
245database migration that wishes to "DROP CONSTRAINT" on a name that was
246previously generated with the shorter length. This migration will fail when
247the identifier length is changed without the name of the index or constraint
248first being adjusted. Such applications are strongly advised to make use of
249:paramref:`_sa.create_engine.max_identifier_length`
250in order to maintain control
251of the generation of truncated names, and to fully review and test all database
252migrations in a staging environment when changing this value to ensure that the
253impact of this change has been mitigated.
254
255.. versionchanged:: 1.4 the default max_identifier_length for Oracle is 128
256 characters, which is adjusted down to 30 upon first connect if an older
257 version of Oracle server (compatibility version < 12.2) is detected.
258
259
260LIMIT/OFFSET/FETCH Support
261--------------------------
262
263Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` make
264use of ``FETCH FIRST N ROW / OFFSET N ROWS`` syntax assuming
265Oracle 12c or above, and assuming the SELECT statement is not embedded within
266a compound statement like UNION. This syntax is also available directly by using
267the :meth:`_sql.Select.fetch` method.
268
269.. versionchanged:: 2.0 the Oracle dialect now uses
270 ``FETCH FIRST N ROW / OFFSET N ROWS`` for all
271 :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` usage including
272 within the ORM and legacy :class:`_orm.Query`. To force the legacy
273 behavior using window functions, specify the ``enable_offset_fetch=False``
274 dialect parameter to :func:`_sa.create_engine`.
275
276The use of ``FETCH FIRST / OFFSET`` may be disabled on any Oracle version
277by passing ``enable_offset_fetch=False`` to :func:`_sa.create_engine`, which
278will force the use of "legacy" mode that makes use of window functions.
279This mode is also selected automatically when using a version of Oracle
280prior to 12c.
281
282When using legacy mode, or when a :class:`.Select` statement
283with limit/offset is embedded in a compound statement, an emulated approach for
284LIMIT / OFFSET based on window functions is used, which involves creation of a
285subquery using ``ROW_NUMBER`` that is prone to performance issues as well as
286SQL construction issues for complex statements. However, this approach is
287supported by all Oracle versions. See notes below.
288
289Notes on LIMIT / OFFSET emulation (when fetch() method cannot be used)
290~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
291
292If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`, or with the
293ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods on an
294Oracle version prior to 12c, the following notes apply:
295
296* SQLAlchemy currently makes use of ROWNUM to achieve
297 LIMIT/OFFSET; the exact methodology is taken from
298 https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results .
299
300* the "FIRST_ROWS()" optimization keyword is not used by default. To enable
301 the usage of this optimization directive, specify ``optimize_limits=True``
302 to :func:`_sa.create_engine`.
303
304 .. versionchanged:: 1.4
305 The Oracle dialect renders limit/offset integer values using a "post
306 compile" scheme which renders the integer directly before passing the
307 statement to the cursor for execution. The ``use_binds_for_limits`` flag
308 no longer has an effect.
309
310 .. seealso::
311
312 :ref:`change_4808`.
313
314.. _oracle_returning:
315
316RETURNING Support
317-----------------
318
319The Oracle database supports RETURNING fully for INSERT, UPDATE and DELETE
320statements that are invoked with a single collection of bound parameters
321(that is, a ``cursor.execute()`` style statement; SQLAlchemy does not generally
322support RETURNING with :term:`executemany` statements). Multiple rows may be
323returned as well.
324
325.. versionchanged:: 2.0 the Oracle backend has full support for RETURNING
326 on parity with other backends.
327
328
329
330ON UPDATE CASCADE
331-----------------
332
333Oracle doesn't have native ON UPDATE CASCADE functionality. A trigger based
334solution is available at
335https://asktom.oracle.com/tkyte/update_cascade/index.html .
336
337When using the SQLAlchemy ORM, the ORM has limited ability to manually issue
338cascading updates - specify ForeignKey objects using the
339"deferrable=True, initially='deferred'" keyword arguments,
340and specify "passive_updates=False" on each relationship().
341
342Oracle 8 Compatibility
343----------------------
344
345.. warning:: The status of Oracle 8 compatibility is not known for SQLAlchemy
346 2.0.
347
348When Oracle 8 is detected, the dialect internally configures itself to the
349following behaviors:
350
351* the use_ansi flag is set to False. This has the effect of converting all
352 JOIN phrases into the WHERE clause, and in the case of LEFT OUTER JOIN
353 makes use of Oracle's (+) operator.
354
355* the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL when
356 the :class:`~sqlalchemy.types.Unicode` is used - VARCHAR2 and CLOB are issued
357 instead. This because these types don't seem to work correctly on Oracle 8
358 even though they are available. The :class:`~sqlalchemy.types.NVARCHAR` and
359 :class:`~sqlalchemy.dialects.oracle.NCLOB` types will always generate
360 NVARCHAR2 and NCLOB.
361
362
363Synonym/DBLINK Reflection
364-------------------------
365
366When using reflection with Table objects, the dialect can optionally search
367for tables indicated by synonyms, either in local or remote schemas or
368accessed over DBLINK, by passing the flag ``oracle_resolve_synonyms=True`` as
369a keyword argument to the :class:`_schema.Table` construct::
370
371 some_table = Table('some_table', autoload_with=some_engine,
372 oracle_resolve_synonyms=True)
373
374When this flag is set, the given name (such as ``some_table`` above) will
375be searched not just in the ``ALL_TABLES`` view, but also within the
376``ALL_SYNONYMS`` view to see if this name is actually a synonym to another
377name. If the synonym is located and refers to a DBLINK, the oracle dialect
378knows how to locate the table's information using DBLINK syntax(e.g.
379``@dblink``).
380
381``oracle_resolve_synonyms`` is accepted wherever reflection arguments are
382accepted, including methods such as :meth:`_schema.MetaData.reflect` and
383:meth:`_reflection.Inspector.get_columns`.
384
385If synonyms are not in use, this flag should be left disabled.
386
387.. _oracle_constraint_reflection:
388
389Constraint Reflection
390---------------------
391
392The Oracle dialect can return information about foreign key, unique, and
393CHECK constraints, as well as indexes on tables.
394
395Raw information regarding these constraints can be acquired using
396:meth:`_reflection.Inspector.get_foreign_keys`,
397:meth:`_reflection.Inspector.get_unique_constraints`,
398:meth:`_reflection.Inspector.get_check_constraints`, and
399:meth:`_reflection.Inspector.get_indexes`.
400
401.. versionchanged:: 1.2 The Oracle dialect can now reflect UNIQUE and
402 CHECK constraints.
403
404When using reflection at the :class:`_schema.Table` level, the
405:class:`_schema.Table`
406will also include these constraints.
407
408Note the following caveats:
409
410* When using the :meth:`_reflection.Inspector.get_check_constraints` method,
411 Oracle
412 builds a special "IS NOT NULL" constraint for columns that specify
413 "NOT NULL". This constraint is **not** returned by default; to include
414 the "IS NOT NULL" constraints, pass the flag ``include_all=True``::
415
416 from sqlalchemy import create_engine, inspect
417
418 engine = create_engine("oracle+cx_oracle://s:t@dsn")
419 inspector = inspect(engine)
420 all_check_constraints = inspector.get_check_constraints(
421 "some_table", include_all=True)
422
423* in most cases, when reflecting a :class:`_schema.Table`,
424 a UNIQUE constraint will
425 **not** be available as a :class:`.UniqueConstraint` object, as Oracle
426 mirrors unique constraints with a UNIQUE index in most cases (the exception
427 seems to be when two or more unique constraints represent the same columns);
428 the :class:`_schema.Table` will instead represent these using
429 :class:`.Index`
430 with the ``unique=True`` flag set.
431
432* Oracle creates an implicit index for the primary key of a table; this index
433 is **excluded** from all index results.
434
435* the list of columns reflected for an index will not include column names
436 that start with SYS_NC.
437
438Table names with SYSTEM/SYSAUX tablespaces
439-------------------------------------------
440
441The :meth:`_reflection.Inspector.get_table_names` and
442:meth:`_reflection.Inspector.get_temp_table_names`
443methods each return a list of table names for the current engine. These methods
444are also part of the reflection which occurs within an operation such as
445:meth:`_schema.MetaData.reflect`. By default,
446these operations exclude the ``SYSTEM``
447and ``SYSAUX`` tablespaces from the operation. In order to change this, the
448default list of tablespaces excluded can be changed at the engine level using
449the ``exclude_tablespaces`` parameter::
450
451 # exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM
452 e = create_engine(
453 "oracle+cx_oracle://scott:tiger@xe",
454 exclude_tablespaces=["SYSAUX", "SOME_TABLESPACE"])
455
456DateTime Compatibility
457----------------------
458
459Oracle has no datatype known as ``DATETIME``, it instead has only ``DATE``,
460which can actually store a date and time value. For this reason, the Oracle
461dialect provides a type :class:`_oracle.DATE` which is a subclass of
462:class:`.DateTime`. This type has no special behavior, and is only
463present as a "marker" for this type; additionally, when a database column
464is reflected and the type is reported as ``DATE``, the time-supporting
465:class:`_oracle.DATE` type is used.
466
467.. _oracle_table_options:
468
469Oracle Table Options
470-------------------------
471
472The CREATE TABLE phrase supports the following options with Oracle
473in conjunction with the :class:`_schema.Table` construct:
474
475
476* ``ON COMMIT``::
477
478 Table(
479 "some_table", metadata, ...,
480 prefixes=['GLOBAL TEMPORARY'], oracle_on_commit='PRESERVE ROWS')
481
482* ``COMPRESS``::
483
484 Table('mytable', metadata, Column('data', String(32)),
485 oracle_compress=True)
486
487 Table('mytable', metadata, Column('data', String(32)),
488 oracle_compress=6)
489
490 The ``oracle_compress`` parameter accepts either an integer compression
491 level, or ``True`` to use the default compression level.
492
493.. _oracle_index_options:
494
495Oracle Specific Index Options
496-----------------------------
497
498Bitmap Indexes
499~~~~~~~~~~~~~~
500
501You can specify the ``oracle_bitmap`` parameter to create a bitmap index
502instead of a B-tree index::
503
504 Index('my_index', my_table.c.data, oracle_bitmap=True)
505
506Bitmap indexes cannot be unique and cannot be compressed. SQLAlchemy will not
507check for such limitations, only the database will.
508
509Index compression
510~~~~~~~~~~~~~~~~~
511
512Oracle has a more efficient storage mode for indexes containing lots of
513repeated values. Use the ``oracle_compress`` parameter to turn on key
514compression::
515
516 Index('my_index', my_table.c.data, oracle_compress=True)
517
518 Index('my_index', my_table.c.data1, my_table.c.data2, unique=True,
519 oracle_compress=1)
520
521The ``oracle_compress`` parameter accepts either an integer specifying the
522number of prefix columns to compress, or ``True`` to use the default (all
523columns for non-unique indexes, all but the last column for unique indexes).
524
525""" # noqa
526
527from __future__ import annotations
528
529from collections import defaultdict
530from functools import lru_cache
531from functools import wraps
532import re
533
534from . import dictionary
535from .types import _OracleBoolean
536from .types import _OracleDate
537from .types import BFILE
538from .types import BINARY_DOUBLE
539from .types import BINARY_FLOAT
540from .types import DATE
541from .types import FLOAT
542from .types import INTERVAL
543from .types import LONG
544from .types import NCLOB
545from .types import NUMBER
546from .types import NVARCHAR2 # noqa
547from .types import OracleRaw # noqa
548from .types import RAW
549from .types import ROWID # noqa
550from .types import TIMESTAMP
551from .types import VARCHAR2 # noqa
552from ... import Computed
553from ... import exc
554from ... import schema as sa_schema
555from ... import sql
556from ... import util
557from ...engine import default
558from ...engine import ObjectKind
559from ...engine import ObjectScope
560from ...engine import reflection
561from ...engine.reflection import ReflectionDefaults
562from ...sql import and_
563from ...sql import bindparam
564from ...sql import compiler
565from ...sql import expression
566from ...sql import func
567from ...sql import null
568from ...sql import or_
569from ...sql import select
570from ...sql import sqltypes
571from ...sql import util as sql_util
572from ...sql import visitors
573from ...sql.visitors import InternalTraversal
574from ...types import BLOB
575from ...types import CHAR
576from ...types import CLOB
577from ...types import DOUBLE_PRECISION
578from ...types import INTEGER
579from ...types import NCHAR
580from ...types import NVARCHAR
581from ...types import REAL
582from ...types import VARCHAR
583
584RESERVED_WORDS = set(
585 "SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN "
586 "DEFAULT ALTER IS INTO MINUS INTEGER NUMBER GRANT IDENTIFIED "
587 "ALL TO ORDER ON FLOAT DATE HAVING CLUSTER NOWAIT RESOURCE "
588 "ANY TABLE INDEX FOR UPDATE WHERE CHECK SMALLINT WITH DELETE "
589 "BY ASC REVOKE LIKE SIZE RENAME NOCOMPRESS NULL GROUP VALUES "
590 "AS IN VIEW EXCLUSIVE COMPRESS SYNONYM SELECT INSERT EXISTS "
591 "NOT TRIGGER ELSE CREATE INTERSECT PCTFREE DISTINCT USER "
592 "CONNECT SET MODE OF UNIQUE VARCHAR2 VARCHAR LOCK OR CHAR "
593 "DECIMAL UNION PUBLIC AND START UID COMMENT CURRENT LEVEL".split()
594)
595
596NO_ARG_FNS = set(
597 "UID CURRENT_DATE SYSDATE USER CURRENT_TIME CURRENT_TIMESTAMP".split()
598)
599
600
601colspecs = {
602 sqltypes.Boolean: _OracleBoolean,
603 sqltypes.Interval: INTERVAL,
604 sqltypes.DateTime: DATE,
605 sqltypes.Date: _OracleDate,
606}
607
608ischema_names = {
609 "VARCHAR2": VARCHAR,
610 "NVARCHAR2": NVARCHAR,
611 "CHAR": CHAR,
612 "NCHAR": NCHAR,
613 "DATE": DATE,
614 "NUMBER": NUMBER,
615 "BLOB": BLOB,
616 "BFILE": BFILE,
617 "CLOB": CLOB,
618 "NCLOB": NCLOB,
619 "TIMESTAMP": TIMESTAMP,
620 "TIMESTAMP WITH TIME ZONE": TIMESTAMP,
621 "TIMESTAMP WITH LOCAL TIME ZONE": TIMESTAMP,
622 "INTERVAL DAY TO SECOND": INTERVAL,
623 "RAW": RAW,
624 "FLOAT": FLOAT,
625 "DOUBLE PRECISION": DOUBLE_PRECISION,
626 "REAL": REAL,
627 "LONG": LONG,
628 "BINARY_DOUBLE": BINARY_DOUBLE,
629 "BINARY_FLOAT": BINARY_FLOAT,
630 "ROWID": ROWID,
631}
632
633
634class OracleTypeCompiler(compiler.GenericTypeCompiler):
635 # Note:
636 # Oracle DATE == DATETIME
637 # Oracle does not allow milliseconds in DATE
638 # Oracle does not support TIME columns
639
640 def visit_datetime(self, type_, **kw):
641 return self.visit_DATE(type_, **kw)
642
643 def visit_float(self, type_, **kw):
644 return self.visit_FLOAT(type_, **kw)
645
646 def visit_double(self, type_, **kw):
647 return self.visit_DOUBLE_PRECISION(type_, **kw)
648
649 def visit_unicode(self, type_, **kw):
650 if self.dialect._use_nchar_for_unicode:
651 return self.visit_NVARCHAR2(type_, **kw)
652 else:
653 return self.visit_VARCHAR2(type_, **kw)
654
655 def visit_INTERVAL(self, type_, **kw):
656 return "INTERVAL DAY%s TO SECOND%s" % (
657 type_.day_precision is not None
658 and "(%d)" % type_.day_precision
659 or "",
660 type_.second_precision is not None
661 and "(%d)" % type_.second_precision
662 or "",
663 )
664
665 def visit_LONG(self, type_, **kw):
666 return "LONG"
667
668 def visit_TIMESTAMP(self, type_, **kw):
669 if getattr(type_, "local_timezone", False):
670 return "TIMESTAMP WITH LOCAL TIME ZONE"
671 elif type_.timezone:
672 return "TIMESTAMP WITH TIME ZONE"
673 else:
674 return "TIMESTAMP"
675
676 def visit_DOUBLE_PRECISION(self, type_, **kw):
677 return self._generate_numeric(type_, "DOUBLE PRECISION", **kw)
678
679 def visit_BINARY_DOUBLE(self, type_, **kw):
680 return self._generate_numeric(type_, "BINARY_DOUBLE", **kw)
681
682 def visit_BINARY_FLOAT(self, type_, **kw):
683 return self._generate_numeric(type_, "BINARY_FLOAT", **kw)
684
685 def visit_FLOAT(self, type_, **kw):
686 kw["_requires_binary_precision"] = True
687 return self._generate_numeric(type_, "FLOAT", **kw)
688
689 def visit_NUMBER(self, type_, **kw):
690 return self._generate_numeric(type_, "NUMBER", **kw)
691
692 def _generate_numeric(
693 self,
694 type_,
695 name,
696 precision=None,
697 scale=None,
698 _requires_binary_precision=False,
699 **kw,
700 ):
701 if precision is None:
702 precision = getattr(type_, "precision", None)
703
704 if _requires_binary_precision:
705 binary_precision = getattr(type_, "binary_precision", None)
706
707 if precision and binary_precision is None:
708 # https://www.oracletutorial.com/oracle-basics/oracle-float/
709 estimated_binary_precision = int(precision / 0.30103)
710 raise exc.ArgumentError(
711 "Oracle FLOAT types use 'binary precision', which does "
712 "not convert cleanly from decimal 'precision'. Please "
713 "specify "
714 f"this type with a separate Oracle variant, such as "
715 f"{type_.__class__.__name__}(precision={precision})."
716 f"with_variant(oracle.FLOAT"
717 f"(binary_precision="
718 f"{estimated_binary_precision}), 'oracle'), so that the "
719 "Oracle specific 'binary_precision' may be specified "
720 "accurately."
721 )
722 else:
723 precision = binary_precision
724
725 if scale is None:
726 scale = getattr(type_, "scale", None)
727
728 if precision is None:
729 return name
730 elif scale is None:
731 n = "%(name)s(%(precision)s)"
732 return n % {"name": name, "precision": precision}
733 else:
734 n = "%(name)s(%(precision)s, %(scale)s)"
735 return n % {"name": name, "precision": precision, "scale": scale}
736
737 def visit_string(self, type_, **kw):
738 return self.visit_VARCHAR2(type_, **kw)
739
740 def visit_VARCHAR2(self, type_, **kw):
741 return self._visit_varchar(type_, "", "2")
742
743 def visit_NVARCHAR2(self, type_, **kw):
744 return self._visit_varchar(type_, "N", "2")
745
746 visit_NVARCHAR = visit_NVARCHAR2
747
748 def visit_VARCHAR(self, type_, **kw):
749 return self._visit_varchar(type_, "", "")
750
751 def _visit_varchar(self, type_, n, num):
752 if not type_.length:
753 return "%(n)sVARCHAR%(two)s" % {"two": num, "n": n}
754 elif not n and self.dialect._supports_char_length:
755 varchar = "VARCHAR%(two)s(%(length)s CHAR)"
756 return varchar % {"length": type_.length, "two": num}
757 else:
758 varchar = "%(n)sVARCHAR%(two)s(%(length)s)"
759 return varchar % {"length": type_.length, "two": num, "n": n}
760
761 def visit_text(self, type_, **kw):
762 return self.visit_CLOB(type_, **kw)
763
764 def visit_unicode_text(self, type_, **kw):
765 if self.dialect._use_nchar_for_unicode:
766 return self.visit_NCLOB(type_, **kw)
767 else:
768 return self.visit_CLOB(type_, **kw)
769
770 def visit_large_binary(self, type_, **kw):
771 return self.visit_BLOB(type_, **kw)
772
773 def visit_big_integer(self, type_, **kw):
774 return self.visit_NUMBER(type_, precision=19, **kw)
775
776 def visit_boolean(self, type_, **kw):
777 return self.visit_SMALLINT(type_, **kw)
778
779 def visit_RAW(self, type_, **kw):
780 if type_.length:
781 return "RAW(%(length)s)" % {"length": type_.length}
782 else:
783 return "RAW"
784
785 def visit_ROWID(self, type_, **kw):
786 return "ROWID"
787
788
789class OracleCompiler(compiler.SQLCompiler):
790 """Oracle compiler modifies the lexical structure of Select
791 statements to work under non-ANSI configured Oracle databases, if
792 the use_ansi flag is False.
793 """
794
795 compound_keywords = util.update_copy(
796 compiler.SQLCompiler.compound_keywords,
797 {expression.CompoundSelect.EXCEPT: "MINUS"},
798 )
799
800 def __init__(self, *args, **kwargs):
801 self.__wheres = {}
802 super().__init__(*args, **kwargs)
803
804 def visit_mod_binary(self, binary, operator, **kw):
805 return "mod(%s, %s)" % (
806 self.process(binary.left, **kw),
807 self.process(binary.right, **kw),
808 )
809
810 def visit_now_func(self, fn, **kw):
811 return "CURRENT_TIMESTAMP"
812
813 def visit_char_length_func(self, fn, **kw):
814 return "LENGTH" + self.function_argspec(fn, **kw)
815
816 def visit_match_op_binary(self, binary, operator, **kw):
817 return "CONTAINS (%s, %s)" % (
818 self.process(binary.left),
819 self.process(binary.right),
820 )
821
822 def visit_true(self, expr, **kw):
823 return "1"
824
825 def visit_false(self, expr, **kw):
826 return "0"
827
828 def get_cte_preamble(self, recursive):
829 return "WITH"
830
831 def get_select_hint_text(self, byfroms):
832 return " ".join("/*+ %s */" % text for table, text in byfroms.items())
833
834 def function_argspec(self, fn, **kw):
835 if len(fn.clauses) > 0 or fn.name.upper() not in NO_ARG_FNS:
836 return compiler.SQLCompiler.function_argspec(self, fn, **kw)
837 else:
838 return ""
839
840 def visit_function(self, func, **kw):
841 text = super().visit_function(func, **kw)
842 if kw.get("asfrom", False):
843 text = "TABLE (%s)" % text
844 return text
845
846 def visit_table_valued_column(self, element, **kw):
847 text = super().visit_table_valued_column(element, **kw)
848 text = text + ".COLUMN_VALUE"
849 return text
850
851 def default_from(self):
852 """Called when a ``SELECT`` statement has no froms,
853 and no ``FROM`` clause is to be appended.
854
855 The Oracle compiler tacks a "FROM DUAL" to the statement.
856 """
857
858 return " FROM DUAL"
859
860 def visit_join(self, join, from_linter=None, **kwargs):
861 if self.dialect.use_ansi:
862 return compiler.SQLCompiler.visit_join(
863 self, join, from_linter=from_linter, **kwargs
864 )
865 else:
866 if from_linter:
867 from_linter.edges.add((join.left, join.right))
868
869 kwargs["asfrom"] = True
870 if isinstance(join.right, expression.FromGrouping):
871 right = join.right.element
872 else:
873 right = join.right
874 return (
875 self.process(join.left, from_linter=from_linter, **kwargs)
876 + ", "
877 + self.process(right, from_linter=from_linter, **kwargs)
878 )
879
880 def _get_nonansi_join_whereclause(self, froms):
881 clauses = []
882
883 def visit_join(join):
884 if join.isouter:
885 # https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354
886 # "apply the outer join operator (+) to all columns of B in
887 # the join condition in the WHERE clause" - that is,
888 # unconditionally regardless of operator or the other side
889 def visit_binary(binary):
890 if isinstance(
891 binary.left, expression.ColumnClause
892 ) and join.right.is_derived_from(binary.left.table):
893 binary.left = _OuterJoinColumn(binary.left)
894 elif isinstance(
895 binary.right, expression.ColumnClause
896 ) and join.right.is_derived_from(binary.right.table):
897 binary.right = _OuterJoinColumn(binary.right)
898
899 clauses.append(
900 visitors.cloned_traverse(
901 join.onclause, {}, {"binary": visit_binary}
902 )
903 )
904 else:
905 clauses.append(join.onclause)
906
907 for j in join.left, join.right:
908 if isinstance(j, expression.Join):
909 visit_join(j)
910 elif isinstance(j, expression.FromGrouping):
911 visit_join(j.element)
912
913 for f in froms:
914 if isinstance(f, expression.Join):
915 visit_join(f)
916
917 if not clauses:
918 return None
919 else:
920 return sql.and_(*clauses)
921
922 def visit_outer_join_column(self, vc, **kw):
923 return self.process(vc.column, **kw) + "(+)"
924
925 def visit_sequence(self, seq, **kw):
926 return self.preparer.format_sequence(seq) + ".nextval"
927
928 def get_render_as_alias_suffix(self, alias_name_text):
929 """Oracle doesn't like ``FROM table AS alias``"""
930
931 return " " + alias_name_text
932
933 def returning_clause(
934 self, stmt, returning_cols, *, populate_result_map, **kw
935 ):
936 columns = []
937 binds = []
938
939 for i, column in enumerate(
940 expression._select_iterables(returning_cols)
941 ):
942 if (
943 self.isupdate
944 and isinstance(column, sa_schema.Column)
945 and isinstance(column.server_default, Computed)
946 and not self.dialect._supports_update_returning_computed_cols
947 ):
948 util.warn(
949 "Computed columns don't work with Oracle UPDATE "
950 "statements that use RETURNING; the value of the column "
951 "*before* the UPDATE takes place is returned. It is "
952 "advised to not use RETURNING with an Oracle computed "
953 "column. Consider setting implicit_returning to False on "
954 "the Table object in order to avoid implicit RETURNING "
955 "clauses from being generated for this Table."
956 )
957 if column.type._has_column_expression:
958 col_expr = column.type.column_expression(column)
959 else:
960 col_expr = column
961
962 outparam = sql.outparam("ret_%d" % i, type_=column.type)
963 self.binds[outparam.key] = outparam
964 binds.append(
965 self.bindparam_string(self._truncate_bindparam(outparam))
966 )
967
968 # has_out_parameters would in a normal case be set to True
969 # as a result of the compiler visiting an outparam() object.
970 # in this case, the above outparam() objects are not being
971 # visited. Ensure the statement itself didn't have other
972 # outparam() objects independently.
973 # technically, this could be supported, but as it would be
974 # a very strange use case without a clear rationale, disallow it
975 if self.has_out_parameters:
976 raise exc.InvalidRequestError(
977 "Using explicit outparam() objects with "
978 "UpdateBase.returning() in the same Core DML statement "
979 "is not supported in the Oracle dialect."
980 )
981
982 self._oracle_returning = True
983
984 columns.append(self.process(col_expr, within_columns_clause=False))
985 if populate_result_map:
986 self._add_to_result_map(
987 getattr(col_expr, "name", col_expr._anon_name_label),
988 getattr(col_expr, "name", col_expr._anon_name_label),
989 (
990 column,
991 getattr(column, "name", None),
992 getattr(column, "key", None),
993 ),
994 column.type,
995 )
996
997 return "RETURNING " + ", ".join(columns) + " INTO " + ", ".join(binds)
998
999 def _row_limit_clause(self, select, **kw):
1000 """ORacle 12c supports OFFSET/FETCH operators
1001 Use it instead subquery with row_number
1002
1003 """
1004
1005 if (
1006 select._fetch_clause is not None
1007 or not self.dialect._supports_offset_fetch
1008 ):
1009 return super()._row_limit_clause(
1010 select, use_literal_execute_for_simple_int=True, **kw
1011 )
1012 else:
1013 return self.fetch_clause(
1014 select,
1015 fetch_clause=self._get_limit_or_fetch(select),
1016 use_literal_execute_for_simple_int=True,
1017 **kw,
1018 )
1019
1020 def _get_limit_or_fetch(self, select):
1021 if select._fetch_clause is None:
1022 return select._limit_clause
1023 else:
1024 return select._fetch_clause
1025
1026 def translate_select_structure(self, select_stmt, **kwargs):
1027 select = select_stmt
1028
1029 if not getattr(select, "_oracle_visit", None):
1030 if not self.dialect.use_ansi:
1031 froms = self._display_froms_for_select(
1032 select, kwargs.get("asfrom", False)
1033 )
1034 whereclause = self._get_nonansi_join_whereclause(froms)
1035 if whereclause is not None:
1036 select = select.where(whereclause)
1037 select._oracle_visit = True
1038
1039 # if fetch is used this is not needed
1040 if (
1041 select._has_row_limiting_clause
1042 and not self.dialect._supports_offset_fetch
1043 and select._fetch_clause is None
1044 ):
1045 limit_clause = select._limit_clause
1046 offset_clause = select._offset_clause
1047
1048 if select._simple_int_clause(limit_clause):
1049 limit_clause = limit_clause.render_literal_execute()
1050
1051 if select._simple_int_clause(offset_clause):
1052 offset_clause = offset_clause.render_literal_execute()
1053
1054 # currently using form at:
1055 # https://blogs.oracle.com/oraclemagazine/\
1056 # on-rownum-and-limiting-results
1057
1058 orig_select = select
1059 select = select._generate()
1060 select._oracle_visit = True
1061
1062 # add expressions to accommodate FOR UPDATE OF
1063 for_update = select._for_update_arg
1064 if for_update is not None and for_update.of:
1065 for_update = for_update._clone()
1066 for_update._copy_internals()
1067
1068 for elem in for_update.of:
1069 if not select.selected_columns.contains_column(elem):
1070 select = select.add_columns(elem)
1071
1072 # Wrap the middle select and add the hint
1073 inner_subquery = select.alias()
1074 limitselect = sql.select(
1075 *[
1076 c
1077 for c in inner_subquery.c
1078 if orig_select.selected_columns.corresponding_column(c)
1079 is not None
1080 ]
1081 )
1082
1083 if (
1084 limit_clause is not None
1085 and self.dialect.optimize_limits
1086 and select._simple_int_clause(limit_clause)
1087 ):
1088 limitselect = limitselect.prefix_with(
1089 expression.text(
1090 "/*+ FIRST_ROWS(%s) */"
1091 % self.process(limit_clause, **kwargs)
1092 )
1093 )
1094
1095 limitselect._oracle_visit = True
1096 limitselect._is_wrapper = True
1097
1098 # add expressions to accommodate FOR UPDATE OF
1099 if for_update is not None and for_update.of:
1100 adapter = sql_util.ClauseAdapter(inner_subquery)
1101 for_update.of = [
1102 adapter.traverse(elem) for elem in for_update.of
1103 ]
1104
1105 # If needed, add the limiting clause
1106 if limit_clause is not None:
1107 if select._simple_int_clause(limit_clause) and (
1108 offset_clause is None
1109 or select._simple_int_clause(offset_clause)
1110 ):
1111 max_row = limit_clause
1112
1113 if offset_clause is not None:
1114 max_row = max_row + offset_clause
1115
1116 else:
1117 max_row = limit_clause
1118
1119 if offset_clause is not None:
1120 max_row = max_row + offset_clause
1121 limitselect = limitselect.where(
1122 sql.literal_column("ROWNUM") <= max_row
1123 )
1124
1125 # If needed, add the ora_rn, and wrap again with offset.
1126 if offset_clause is None:
1127 limitselect._for_update_arg = for_update
1128 select = limitselect
1129 else:
1130 limitselect = limitselect.add_columns(
1131 sql.literal_column("ROWNUM").label("ora_rn")
1132 )
1133 limitselect._oracle_visit = True
1134 limitselect._is_wrapper = True
1135
1136 if for_update is not None and for_update.of:
1137 limitselect_cols = limitselect.selected_columns
1138 for elem in for_update.of:
1139 if (
1140 limitselect_cols.corresponding_column(elem)
1141 is None
1142 ):
1143 limitselect = limitselect.add_columns(elem)
1144
1145 limit_subquery = limitselect.alias()
1146 origselect_cols = orig_select.selected_columns
1147 offsetselect = sql.select(
1148 *[
1149 c
1150 for c in limit_subquery.c
1151 if origselect_cols.corresponding_column(c)
1152 is not None
1153 ]
1154 )
1155
1156 offsetselect._oracle_visit = True
1157 offsetselect._is_wrapper = True
1158
1159 if for_update is not None and for_update.of:
1160 adapter = sql_util.ClauseAdapter(limit_subquery)
1161 for_update.of = [
1162 adapter.traverse(elem) for elem in for_update.of
1163 ]
1164
1165 offsetselect = offsetselect.where(
1166 sql.literal_column("ora_rn") > offset_clause
1167 )
1168
1169 offsetselect._for_update_arg = for_update
1170 select = offsetselect
1171
1172 return select
1173
1174 def limit_clause(self, select, **kw):
1175 return ""
1176
1177 def visit_empty_set_expr(self, type_, **kw):
1178 return "SELECT 1 FROM DUAL WHERE 1!=1"
1179
1180 def for_update_clause(self, select, **kw):
1181 if self.is_subquery():
1182 return ""
1183
1184 tmp = " FOR UPDATE"
1185
1186 if select._for_update_arg.of:
1187 tmp += " OF " + ", ".join(
1188 self.process(elem, **kw) for elem in select._for_update_arg.of
1189 )
1190
1191 if select._for_update_arg.nowait:
1192 tmp += " NOWAIT"
1193 if select._for_update_arg.skip_locked:
1194 tmp += " SKIP LOCKED"
1195
1196 return tmp
1197
1198 def visit_is_distinct_from_binary(self, binary, operator, **kw):
1199 return "DECODE(%s, %s, 0, 1) = 1" % (
1200 self.process(binary.left),