L i&dZddlmZddlmZddlmZddlmZddlm Z ddl Z dd l m Z dd l mZdd l mZdd l mZdd l mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddl mZddlm Z ddlm!Z!ddlm"Z"ddl m#Z#dd l m$Z$dd!l m%Z&dd"l m'Z'dd#l m(Z(dd$l)m*Z*dd%l)m+Z+dd&l)m,Z,dd'l)m-Z-dd(l.m/Z/dd)l'm0Z0dd*l'm1Z1dd+l'm2Z2dd,l'm3Z3dd-l'm4Z4dd.l'm5Z5dd/l'm6Z6dd0l'm7Z7dd1l'm8Z9dd2l'm:Z:dd#l'm(Z;dd3l'mZ>dd5l m?Z?dd6l m@Z@dd7l mAZAdd8l mBZBdd9l mCZCdd:l mDZDdd;l mEZEddjZJeHd?jZKe:jee:jee:jee:jeiZPid@eGdAeEdBe@dCeDdDedEedFe?dGedHeAdIedJedKedLedMedNedOedPeBeFeeeee dQZQGdRdSe2jZSGdTdUe2jZUGdVdWe2jZWGdXdYe2jZYGdZd[e*jZ[Gd\d]e*jZ]Gd^d_e'jZ_y)`u .. dialect:: oracle :name: Oracle Database :normal_support: 11+ :best_effort: 9+ Auto Increment Behavior ----------------------- 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. For use within Oracle Database, two options are available, which are the use of IDENTITY columns (Oracle Database 12 and above only) or the association of a SEQUENCE with the column. Specifying GENERATED AS IDENTITY (Oracle Database 12 and above) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Starting from version 12, Oracle Database can make use of identity columns using the :class:`_sql.Identity` to specify the autoincrementing behavior:: t = Table( "mytable", metadata, Column("id", Integer, Identity(start=3), primary_key=True), Column(...), ..., ) The CREATE TABLE for the above :class:`_schema.Table` object would be: .. sourcecode:: sql CREATE TABLE mytable ( id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 3), ..., PRIMARY KEY (id) ) The :class:`_schema.Identity` object support many options to control the "autoincrementing" behavior of the column, like the starting value, the incrementing value, etc. In addition to the standard options, Oracle Database supports setting :paramref:`_schema.Identity.always` to ``None`` to use the default generated mode, rendering GENERATED AS IDENTITY in the DDL. It also supports setting :paramref:`_schema.Identity.on_null` to ``True`` to specify ON NULL in conjunction with a 'BY DEFAULT' identity column. Using a SEQUENCE (all Oracle Database versions) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Older version of Oracle Database had no "autoincrement" feature: SQLAlchemy relies upon sequences to produce these values. With the older Oracle Database versions, *a sequence must always be explicitly specified to enable autoincrement*. 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:: t = Table( "mytable", metadata, Column("id", Integer, Sequence("id_seq", start=1), primary_key=True), Column(...), ..., ) This step is also required when using table reflection, i.e. autoload_with=engine:: t = Table( "mytable", metadata, Column("id", Integer, Sequence("id_seq", start=1), primary_key=True), autoload_with=engine, ) .. versionchanged:: 1.4 Added :class:`_schema.Identity` construct in a :class:`_schema.Column` to specify the option of an autoincrementing column. .. _oracle_isolation_level: Transaction Isolation Level / Autocommit ---------------------------------------- Oracle Database supports "READ COMMITTED" and "SERIALIZABLE" modes of isolation. The AUTOCOMMIT isolation level is also supported by the python-oracledb and cx_Oracle dialects. To set using per-connection execution options:: connection = engine.connect() connection = connection.execution_options(isolation_level="AUTOCOMMIT") For ``READ COMMITTED`` and ``SERIALIZABLE``, the Oracle Database dialects sets the level at the session level using ``ALTER SESSION``, which is reverted back to its default setting when the connection is returned to the connection pool. Valid values for ``isolation_level`` include: * ``READ COMMITTED`` * ``AUTOCOMMIT`` * ``SERIALIZABLE`` .. note:: The implementation for the :meth:`_engine.Connection.get_isolation_level` method as implemented by the Oracle Database dialects necessarily force the start of a transaction using the Oracle Database DBMS_TRANSACTION.LOCAL_TRANSACTION_ID function; otherwise no level is normally readable. Additionally, the :meth:`_engine.Connection.get_isolation_level` method will raise an exception if the ``v$transaction`` view is not available due to permissions or other reasons, which is a common occurrence in Oracle Database installations. The python-oracledb and cx_Oracle dialects attempt to call the :meth:`_engine.Connection.get_isolation_level` method when the dialect makes its first connection to the database in order to acquire the "default"isolation level. This default level is necessary so that the level can be reset on a connection after it has been temporarily modified using :meth:`_engine.Connection.execution_options` method. In the common event that the :meth:`_engine.Connection.get_isolation_level` method raises an exception due to ``v$transaction`` not being readable as well as any other database-related failure, the level is assumed to be "READ COMMITTED". No warning is emitted for this initial first-connect condition as it is expected to be a common restriction on Oracle databases. .. versionadded:: 1.3.16 added support for AUTOCOMMIT to the cx_Oracle dialect as well as the notion of a default isolation level .. versionadded:: 1.3.21 Added support for SERIALIZABLE as well as live reading of the isolation level. .. versionchanged:: 1.3.22 In the event that the default isolation level cannot be read due to permissions on the v$transaction view as is common in Oracle installations, the default isolation level is hardcoded to "READ COMMITTED" which was the behavior prior to 1.3.21. .. seealso:: :ref:`dbapi_autocommit` Identifier Casing ----------------- In Oracle Database, the data dictionary represents all case insensitive identifier names using UPPERCASE text. This is in contradiction to the expectations of SQLAlchemy, which assume a case insensitive name is represented as lowercase text. As an example of case insensitive identifier names, consider the following table: .. sourcecode:: sql CREATE TABLE MyTable (Identifier INTEGER PRIMARY KEY) If you were to ask Oracle Database for information about this table, the table name would be reported as ``MYTABLE`` and the column name would be reported as ``IDENTIFIER``. Compare to most other databases such as PostgreSQL and MySQL which would report these names as ``mytable`` and ``identifier``. The names are **not quoted, therefore are case insensitive**. The special casing of ``MyTable`` and ``Identifier`` would only be maintained if they were quoted in the table definition: .. sourcecode:: sql CREATE TABLE "MyTable" ("Identifier" INTEGER PRIMARY KEY) When constructing a SQLAlchemy :class:`.Table` object, **an all lowercase name is considered to be case insensitive**. So the following table assumes case insensitive names:: Table("mytable", metadata, Column("identifier", Integer, primary_key=True)) Whereas when mixed case or UPPERCASE names are used, case sensitivity is assumed:: Table("MyTable", metadata, Column("Identifier", Integer, primary_key=True)) A similar situation occurs at the database driver level when emitting a textual SQL SELECT statement and looking at column names in the DBAPI ``cursor.description`` attribute. A database like PostgreSQL will normalize case insensitive names to be lowercase:: >>> pg_engine = create_engine("postgresql://scott:tiger@localhost/test") >>> pg_connection = pg_engine.connect() >>> result = pg_connection.exec_driver_sql("SELECT 1 AS SomeName") >>> result.cursor.description (Column(name='somename', type_code=23),) Whereas Oracle normalizes them to UPPERCASE:: >>> oracle_engine = create_engine("oracle+oracledb://scott:tiger@oracle18c/xe") >>> oracle_connection = oracle_engine.connect() >>> result = oracle_connection.exec_driver_sql( ... "SELECT 1 AS SomeName FROM DUAL" ... ) >>> result.cursor.description [('SOMENAME', , 127, None, 0, -127, True)] In order to achieve cross-database parity for the two cases of a. table reflection and b. textual-only SQL statement round trips, SQLAlchemy performs a step called **name normalization** when using the Oracle dialect. This process may also apply to other third party dialects that have similar UPPERCASE handling of case insensitive names. When using name normalization, SQLAlchemy attempts to detect if a name is case insensitive by checking if all characters are UPPERCASE letters only; if so, then it assumes this is a case insensitive name and is delivered as a lowercase name. For table reflection, a tablename that is seen represented as all UPPERCASE in Oracle Database's catalog tables will be assumed to have a case insensitive name. This is what allows the ``Table`` definition to use lower case names and be equally compatible from a reflection point of view on Oracle Database and all other databases such as PostgreSQL and MySQL:: # matches a table created with CREATE TABLE mytable Table("mytable", metadata, autoload_with=some_engine) Above, the all lowercase name ``"mytable"`` is case insensitive; it will match a table reported by PostgreSQL as ``"mytable"`` and a table reported by Oracle as ``"MYTABLE"``. If name normalization were not present, it would not be possible for the above :class:`.Table` definition to be introspectable in a cross-database way, since we are dealing with a case insensitive name that is not reported by each database in the same way. Case sensitivity can be forced on in this case, such as if we wanted to represent the quoted tablename ``"MYTABLE"`` with that exact casing, most simply by using that casing directly, which will be seen as a case sensitive name:: # matches a table created with CREATE TABLE "MYTABLE" Table("MYTABLE", metadata, autoload_with=some_engine) For the unusual case of a quoted all-lowercase name, the :class:`.quoted_name` construct may be used:: from sqlalchemy import quoted_name # matches a table created with CREATE TABLE "mytable" Table( quoted_name("mytable", quote=True), metadata, autoload_with=some_engine ) Name normalization also takes place when handling result sets from **purely textual SQL strings**, that have no other :class:`.Table` or :class:`.Column` metadata associated with them. This includes SQL strings executed using :meth:`.Connection.exec_driver_sql` and SQL strings executed using the :func:`.text` construct which do not include :class:`.Column` metadata. Returning to the Oracle Database SELECT statement, we see that even though ``cursor.description`` reports the column name as ``SOMENAME``, SQLAlchemy name normalizes this to ``somename``:: >>> oracle_engine = create_engine("oracle+oracledb://scott:tiger@oracle18c/xe") >>> oracle_connection = oracle_engine.connect() >>> result = oracle_connection.exec_driver_sql( ... "SELECT 1 AS SomeName FROM DUAL" ... ) >>> result.cursor.description [('SOMENAME', , 127, None, 0, -127, True)] >>> result.keys() RMKeyView(['somename']) The single scenario where the above behavior produces inaccurate results is when using an all-uppercase, quoted name. SQLAlchemy has no way to determine that a particular name in ``cursor.description`` was quoted, and is therefore case sensitive, or was not quoted, and should be name normalized:: >>> result = oracle_connection.exec_driver_sql( ... 'SELECT 1 AS "SOMENAME" FROM DUAL' ... ) >>> result.cursor.description [('SOMENAME', , 127, None, 0, -127, True)] >>> result.keys() RMKeyView(['somename']) For this case, a new feature will be available in SQLAlchemy 2.1 to disable the name normalization behavior in specific cases. .. _oracle_max_identifier_lengths: Maximum Identifier Lengths -------------------------- SQLAlchemy is sensitive to the maximum identifier length supported by Oracle Database. This affects generated SQL label names as well as the generation of constraint names, particularly in the case where the constraint naming convention feature described at :ref:`constraint_naming_conventions` is being used. Oracle Database 12.2 increased the default maximum identifier length from 30 to 128. As of SQLAlchemy 1.4, the default maximum identifier length for the Oracle dialects is 128 characters. Upon first connection, the maximum length actually supported by the database is obtained. In all cases, setting the :paramref:`_sa.create_engine.max_identifier_length` parameter will bypass this change and the value given will be used as is:: engine = create_engine( "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1", max_identifier_length=30, ) If :paramref:`_sa.create_engine.max_identifier_length` is not set, the oracledb dialect internally uses the ``max_identifier_length`` attribute available on driver connections since python-oracledb version 2.5. When using an older driver version, or using the cx_Oracle dialect, SQLAlchemy will instead attempt to use the query ``SELECT value FROM v$parameter WHERE name = 'compatible'`` upon first connect in order to determine the effective compatibility version of the database. The "compatibility" version is a version number that is independent of the actual database version. It is used to assist database migration. It is configured by an Oracle Database initialization parameter. The compatibility version then determines the maximum allowed identifier length for the database. If the V$ view is not available, the database version information is used instead. The maximum identifier length comes into play both when generating anonymized SQL labels in SELECT statements, but more crucially when generating constraint names from a naming convention. It is this area that has created the need for SQLAlchemy to change this default conservatively. For example, the following naming convention produces two very different constraint names based on the identifier length:: from sqlalchemy import Column from sqlalchemy import Index from sqlalchemy import Integer from sqlalchemy import MetaData from sqlalchemy import Table from sqlalchemy.dialects import oracle from sqlalchemy.schema import CreateIndex m = MetaData(naming_convention={"ix": "ix_%(column_0N_name)s"}) t = Table( "t", m, Column("some_column_name_1", Integer), Column("some_column_name_2", Integer), Column("some_column_name_3", Integer), ) ix = Index( None, t.c.some_column_name_1, t.c.some_column_name_2, t.c.some_column_name_3, ) oracle_dialect = oracle.dialect(max_identifier_length=30) print(CreateIndex(ix).compile(dialect=oracle_dialect)) With an identifier length of 30, the above CREATE INDEX looks like: .. sourcecode:: sql CREATE INDEX ix_some_column_name_1s_70cd ON t (some_column_name_1, some_column_name_2, some_column_name_3) However with length of 128, it becomes:: .. sourcecode:: sql CREATE INDEX ix_some_column_name_1some_column_name_2some_column_name_3 ON t (some_column_name_1, some_column_name_2, some_column_name_3) Applications which have run versions of SQLAlchemy prior to 1.4 on Oracle Database version 12.2 or greater are therefore subject to the scenario of a database migration that wishes to "DROP CONSTRAINT" on a name that was previously generated with the shorter length. This migration will fail when the identifier length is changed without the name of the index or constraint first being adjusted. Such applications are strongly advised to make use of :paramref:`_sa.create_engine.max_identifier_length` in order to maintain control of the generation of truncated names, and to fully review and test all database migrations in a staging environment when changing this value to ensure that the impact of this change has been mitigated. .. versionchanged:: 1.4 the default max_identifier_length for Oracle Database is 128 characters, which is adjusted down to 30 upon first connect if the Oracle Database, or its compatibility setting, are lower than version 12.2. LIMIT/OFFSET/FETCH Support -------------------------- Methods like :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` make use of ``FETCH FIRST N ROW / OFFSET N ROWS`` syntax assuming Oracle Database 12c or above, and assuming the SELECT statement is not embedded within a compound statement like UNION. This syntax is also available directly by using the :meth:`_sql.Select.fetch` method. .. versionchanged:: 2.0 the Oracle Database dialects now use ``FETCH FIRST N ROW / OFFSET N ROWS`` for all :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset` usage including within the ORM and legacy :class:`_orm.Query`. To force the legacy behavior using window functions, specify the ``enable_offset_fetch=False`` dialect parameter to :func:`_sa.create_engine`. The use of ``FETCH FIRST / OFFSET`` may be disabled on any Oracle Database version by passing ``enable_offset_fetch=False`` to :func:`_sa.create_engine`, which will force the use of "legacy" mode that makes use of window functions. This mode is also selected automatically when using a version of Oracle Database prior to 12c. When using legacy mode, or when a :class:`.Select` statement with limit/offset is embedded in a compound statement, an emulated approach for LIMIT / OFFSET based on window functions is used, which involves creation of a subquery using ``ROW_NUMBER`` that is prone to performance issues as well as SQL construction issues for complex statements. However, this approach is supported by all Oracle Database versions. See notes below. Notes on LIMIT / OFFSET emulation (when fetch() method cannot be used) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If using :meth:`_sql.Select.limit` and :meth:`_sql.Select.offset`, or with the ORM the :meth:`_orm.Query.limit` and :meth:`_orm.Query.offset` methods on an Oracle Database version prior to 12c, the following notes apply: * SQLAlchemy currently makes use of ROWNUM to achieve LIMIT/OFFSET; the exact methodology is taken from https://blogs.oracle.com/oraclemagazine/on-rownum-and-limiting-results . * the "FIRST_ROWS()" optimization keyword is not used by default. To enable the usage of this optimization directive, specify ``optimize_limits=True`` to :func:`_sa.create_engine`. .. versionchanged:: 1.4 The Oracle Database dialect renders limit/offset integer values using a "post compile" scheme which renders the integer directly before passing the statement to the cursor for execution. The ``use_binds_for_limits`` flag no longer has an effect. .. seealso:: :ref:`change_4808`. .. _oracle_returning: RETURNING Support ----------------- Oracle Database supports RETURNING fully for INSERT, UPDATE and DELETE statements that are invoked with a single collection of bound parameters (that is, a ``cursor.execute()`` style statement; SQLAlchemy does not generally support RETURNING with :term:`executemany` statements). Multiple rows may be returned as well. .. versionchanged:: 2.0 the Oracle Database backend has full support for RETURNING on parity with other backends. ON UPDATE CASCADE ----------------- Oracle Database doesn't have native ON UPDATE CASCADE functionality. A trigger based solution is available at https://web.archive.org/web/20090317041251/https://asktom.oracle.com/tkyte/update_cascade/index.html 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(). Oracle Database 8 Compatibility ------------------------------- .. warning:: The status of Oracle Database 8 compatibility is not known for SQLAlchemy 2.0. When Oracle Database 8 is detected, the dialect internally configures itself to the following behaviors: * 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. * the NVARCHAR2 and NCLOB datatypes are no longer generated as DDL when the :class:`~sqlalchemy.types.Unicode` 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 :class:`~sqlalchemy.types.NVARCHAR` and :class:`~sqlalchemy.dialects.oracle.NCLOB` types will always generate NVARCHAR2 and NCLOB. Synonym/DBLINK Reflection ------------------------- 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 ``oracle_resolve_synonyms=True`` as a keyword argument to the :class:`_schema.Table` construct:: some_table = Table( "some_table", autoload_with=some_engine, oracle_resolve_synonyms=True ) When this flag is set, the given name (such as ``some_table`` above) will be searched not just in the ``ALL_TABLES`` view, but also within the ``ALL_SYNONYMS`` 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 Database dialects know how to locate the table's information using DBLINK syntax(e.g. ``@dblink``). ``oracle_resolve_synonyms`` is accepted wherever reflection arguments are accepted, including methods such as :meth:`_schema.MetaData.reflect` and :meth:`_reflection.Inspector.get_columns`. If synonyms are not in use, this flag should be left disabled. .. _oracle_constraint_reflection: Constraint Reflection --------------------- The Oracle Database dialects can return information about foreign key, unique, and CHECK constraints, as well as indexes on tables. Raw information regarding these constraints can be acquired using :meth:`_reflection.Inspector.get_foreign_keys`, :meth:`_reflection.Inspector.get_unique_constraints`, :meth:`_reflection.Inspector.get_check_constraints`, and :meth:`_reflection.Inspector.get_indexes`. .. versionchanged:: 1.2 The Oracle Database dialect can now reflect UNIQUE and CHECK constraints. When using reflection at the :class:`_schema.Table` level, the :class:`_schema.Table` will also include these constraints. Note the following caveats: * When using the :meth:`_reflection.Inspector.get_check_constraints` method, Oracle Database builds a special "IS NOT NULL" constraint for columns that specify "NOT NULL". This constraint is **not** returned by default; to include the "IS NOT NULL" constraints, pass the flag ``include_all=True``:: from sqlalchemy import create_engine, inspect engine = create_engine( "oracle+oracledb://scott:tiger@localhost:1521?service_name=freepdb1" ) inspector = inspect(engine) all_check_constraints = inspector.get_check_constraints( "some_table", include_all=True ) * in most cases, when reflecting a :class:`_schema.Table`, a UNIQUE constraint will **not** be available as a :class:`.UniqueConstraint` object, as Oracle Database 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 :class:`_schema.Table` will instead represent these using :class:`.Index` with the ``unique=True`` flag set. * Oracle Database creates an implicit index for the primary key of a table; this index is **excluded** from all index results. * the list of columns reflected for an index will not include column names that start with SYS_NC. Table names with SYSTEM/SYSAUX tablespaces ------------------------------------------- The :meth:`_reflection.Inspector.get_table_names` and :meth:`_reflection.Inspector.get_temp_table_names` 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 :meth:`_schema.MetaData.reflect`. By default, these operations exclude the ``SYSTEM`` and ``SYSAUX`` tablespaces from the operation. In order to change this, the default list of tablespaces excluded can be changed at the engine level using the ``exclude_tablespaces`` parameter:: # exclude SYSAUX and SOME_TABLESPACE, but not SYSTEM e = create_engine( "oracle+oracledb://scott:tiger@localhost:1521/?service_name=freepdb1", exclude_tablespaces=["SYSAUX", "SOME_TABLESPACE"], ) .. _oracle_float_support: FLOAT / DOUBLE Support and Behaviors ------------------------------------ The SQLAlchemy :class:`.Float` and :class:`.Double` datatypes are generic datatypes that resolve to the "least surprising" datatype for a given backend. For Oracle Database, this means they resolve to the ``FLOAT`` and ``DOUBLE`` types:: >>> from sqlalchemy import cast, literal, Float >>> from sqlalchemy.dialects import oracle >>> float_datatype = Float() >>> print(cast(literal(5.0), float_datatype).compile(dialect=oracle.dialect())) CAST(:param_1 AS FLOAT) Oracle's ``FLOAT`` / ``DOUBLE`` datatypes are aliases for ``NUMBER``. Oracle Database stores ``NUMBER`` values with full precision, not floating point precision, which means that ``FLOAT`` / ``DOUBLE`` do not actually behave like native FP values. Oracle Database instead offers special datatypes ``BINARY_FLOAT`` and ``BINARY_DOUBLE`` to deliver real 4- and 8- byte FP values. SQLAlchemy supports these datatypes directly using :class:`.BINARY_FLOAT` and :class:`.BINARY_DOUBLE`. To use the :class:`.Float` or :class:`.Double` datatypes in a database agnostic way, while allowing Oracle backends to utilize one of these types, use the :meth:`.TypeEngine.with_variant` method to set up a variant:: >>> from sqlalchemy import cast, literal, Float >>> from sqlalchemy.dialects import oracle >>> float_datatype = Float().with_variant(oracle.BINARY_FLOAT(), "oracle") >>> print(cast(literal(5.0), float_datatype).compile(dialect=oracle.dialect())) CAST(:param_1 AS BINARY_FLOAT) E.g. to use this datatype in a :class:`.Table` definition:: my_table = Table( "my_table", metadata, Column( "fp_data", Float().with_variant(oracle.BINARY_FLOAT(), "oracle") ), ) DateTime Compatibility ---------------------- Oracle Database has no datatype known as ``DATETIME``, it instead has only ``DATE``, which can actually store a date and time value. For this reason, the Oracle Database dialects provide a type :class:`_oracle.DATE` which is a subclass of :class:`.DateTime`. 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 ``DATE``, the time-supporting :class:`_oracle.DATE` type is used. .. _oracle_table_options: Oracle Database Table Options ----------------------------- The CREATE TABLE phrase supports the following options with Oracle Database dialects in conjunction with the :class:`_schema.Table` construct: * ``ON COMMIT``:: Table( "some_table", metadata, ..., prefixes=["GLOBAL TEMPORARY"], oracle_on_commit="PRESERVE ROWS", ) * ``COMPRESS``:: 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. * ``TABLESPACE``:: Table("mytable", metadata, ..., oracle_tablespace="EXAMPLE_TABLESPACE") The ``oracle_tablespace`` parameter specifies the tablespace in which the table is to be created. This is useful when you want to create a table in a tablespace other than the default tablespace of the user. .. versionadded:: 2.0.37 .. _oracle_index_options: Oracle Database Specific Index Options -------------------------------------- Bitmap Indexes ~~~~~~~~~~~~~~ You can specify the ``oracle_bitmap`` parameter to create a bitmap index instead of a B-tree index:: Index("my_index", my_table.c.data, oracle_bitmap=True) Bitmap indexes cannot be unique and cannot be compressed. SQLAlchemy will not check for such limitations, only the database will. Index compression ~~~~~~~~~~~~~~~~~ Oracle Database has a more efficient storage mode for indexes containing lots of repeated values. Use the ``oracle_compress`` parameter to turn on key compression:: Index("my_index", my_table.c.data, oracle_compress=True) Index( "my_index", my_table.c.data1, my_table.c.data2, unique=True, oracle_compress=1, ) The ``oracle_compress`` parameter accepts either an integer specifying the number of prefix columns to compress, or ``True`` to use the default (all columns for non-unique indexes, all but the last column for unique indexes). .. _oracle_vector_datatype: VECTOR Datatype --------------- Oracle Database 23ai introduced a new VECTOR datatype for artificial intelligence and machine learning search operations. The VECTOR datatype is a homogeneous array of 8-bit signed integers, 8-bit unsigned integers (binary), 32-bit floating-point numbers, or 64-bit floating-point numbers. A vector's storage type can be either DENSE or SPARSE. A dense vector contains meaningful values in most or all of its dimensions. In contrast, a sparse vector has non-zero values in only a few dimensions, with the majority being zero. Sparse vectors are represented by the total number of vector dimensions, an array of indices, and an array of values where each value’s location in the vector is indicated by the corresponding indices array position. All other vector values are treated as zero. The storage formats that can be used with sparse vectors are float32, float64, and int8. Note that the binary storage format cannot be used with sparse vectors. Sparse vectors are supported when you are using Oracle Database 23.7 or later. .. seealso:: `Using VECTOR Data `_ - in the documentation for the :ref:`oracledb` driver. .. versionadded:: 2.0.41 - Added VECTOR datatype .. versionadded:: 2.0.43 - Added DENSE/SPARSE support CREATE TABLE support for VECTOR ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ With the :class:`.VECTOR` datatype, you can specify the number of dimensions, the storage format, and the storage type for the data. Valid values for the storage format are enum members of :class:`.VectorStorageFormat`. Valid values for the storage type are enum members of :class:`.VectorStorageType`. If storage type is not specified, a DENSE vector is created by default. To create a table that includes a :class:`.VECTOR` column:: from sqlalchemy.dialects.oracle import ( VECTOR, VectorStorageFormat, VectorStorageType, ) t = Table( "t1", metadata, Column("id", Integer, primary_key=True), Column( "embedding", VECTOR( dim=3, storage_format=VectorStorageFormat.FLOAT32, storage_type=VectorStorageType.SPARSE, ), ), Column(...), ..., ) Vectors can also be defined with an arbitrary number of dimensions and formats. This allows you to specify vectors of different dimensions with the various storage formats mentioned below. **Examples** * In this case, the storage format is flexible, allowing any vector type data to be inserted, such as INT8 or BINARY etc:: vector_col: Mapped[array.array] = mapped_column(VECTOR(dim=3)) * The dimension is flexible in this case, meaning that any dimension vector can be used:: vector_col: Mapped[array.array] = mapped_column( VECTOR(storage_format=VectorStorageType.INT8) ) * Both the dimensions and the storage format are flexible. It creates a DENSE vector:: vector_col: Mapped[array.array] = mapped_column(VECTOR) * To create a SPARSE vector with both dimensions and the storage format as flexible, use the :attr:`.VectorStorageType.SPARSE` storage type:: vector_col: Mapped[array.array] = mapped_column( VECTOR(storage_type=VectorStorageType.SPARSE) ) Python Datatypes for VECTOR ~~~~~~~~~~~~~~~~~~~~~~~~~~~ VECTOR data can be inserted using Python list or Python ``array.array()`` objects. Python arrays of type FLOAT (32-bit), DOUBLE (64-bit), INT (8-bit signed integers), or BINARY (8-bit unsigned integers) are used as bind values when inserting VECTOR columns:: from sqlalchemy import insert, select with engine.begin() as conn: conn.execute( insert(t1), {"id": 1, "embedding": [1, 2, 3]}, ) Data can be inserted into a sparse vector using the :class:`_oracle.SparseVector` class, creating an object consisting of the number of dimensions, an array of indices, and a corresponding array of values:: from sqlalchemy import insert, select from sqlalchemy.dialects.oracle import SparseVector sparse_val = SparseVector(10, [1, 2], array.array("d", [23.45, 221.22])) with engine.begin() as conn: conn.execute( insert(t1), {"id": 1, "embedding": sparse_val}, ) VECTOR Indexes ~~~~~~~~~~~~~~ The VECTOR feature supports an Oracle-specific parameter ``oracle_vector`` on the :class:`.Index` construct, which allows the construction of VECTOR indexes. SPARSE vectors cannot be used in the creation of vector indexes. To utilize VECTOR indexing, set the ``oracle_vector`` parameter to True to use the default values provided by Oracle. HNSW is the default indexing method:: from sqlalchemy import Index Index( "vector_index", t1.c.embedding, oracle_vector=True, ) The full range of parameters for vector indexes are available by using the :class:`.VectorIndexConfig` dataclass in place of a boolean; this dataclass allows full configuration of the index:: Index( "hnsw_vector_index", t1.c.embedding, oracle_vector=VectorIndexConfig( index_type=VectorIndexType.HNSW, distance=VectorDistanceType.COSINE, accuracy=90, hnsw_neighbors=5, hnsw_efconstruction=20, parallel=10, ), ) Index( "ivf_vector_index", t1.c.embedding, oracle_vector=VectorIndexConfig( index_type=VectorIndexType.IVF, distance=VectorDistanceType.DOT, accuracy=90, ivf_neighbor_partitions=5, ), ) For complete explanation of these parameters, see the Oracle documentation linked below. .. seealso:: `CREATE VECTOR INDEX `_ - in the Oracle documentation Similarity Searching ~~~~~~~~~~~~~~~~~~~~ When using the :class:`_oracle.VECTOR` datatype with a :class:`.Column` or similar ORM mapped construct, additional comparison functions are available, including: * ``l2_distance`` * ``cosine_distance`` * ``inner_product`` Example Usage:: result_vector = connection.scalars( select(t1).order_by(t1.embedding.l2_distance([2, 3, 4])).limit(3) ) for user in vector: print(user.id, user.embedding) FETCH APPROXIMATE support ~~~~~~~~~~~~~~~~~~~~~~~~~ Approximate vector search can only be performed when all syntax and semantic rules are satisfied, the corresponding vector index is available, and the query optimizer determines to perform it. If any of these conditions are unmet, then an approximate search is not performed. In this case the query returns exact results. To enable approximate searching during similarity searches on VECTORS, the ``oracle_fetch_approximate`` parameter may be used with the :meth:`.Select.fetch` clause to add ``FETCH APPROX`` to the SELECT statement:: select(users_table).fetch(5, oracle_fetch_approximate=True) ) annotations) defaultdict)fields) lru_cachewrapsN) dictionary)_OracleBoolean) _OracleDate)BFILE) BINARY_DOUBLE) BINARY_FLOAT)DATE)FLOAT)INTERVAL)LONG)NCLOB)NUMBER) NVARCHAR2) OracleRaw)RAW)ROWID) TIMESTAMP)VARCHAR2)VECTOR)VectorIndexConfig)VectorIndexType)Computed)exc)schema)sql)util)default) ObjectKind) ObjectScope) reflection)ReflectionDefaults)and_ bindparam)compiler) expression)func)null)or_)select) selectable)sqltypes)visitors)InternalTraversal)BLOB)CHAR)CLOB)DOUBLE_PRECISION)INTEGER)NCHAR)NVARCHAR)REAL)VARCHARa SHARE RAW DROP BETWEEN FROM DESC OPTION PRIOR LONG THEN DEFAULT ALTER IS INTO MINUS INTEGER NUMBER GRANT IDENTIFIED ALL TO ORDER ON FLOAT DATE HAVING CLUSTER NOWAIT RESOURCE ANY TABLE INDEX FOR UPDATE WHERE CHECK SMALLINT WITH DELETE BY ASC REVOKE LIKE SIZE RENAME NOCOMPRESS NULL GROUP VALUES AS IN VIEW EXCLUSIVE COMPRESS SYNONYM SELECT INSERT EXISTS NOT TRIGGER ELSE CREATE INTERSECT PCTFREE DISTINCT USER CONNECT SET MODE OF UNIQUE VARCHAR2 VARCHAR LOCK OR CHAR DECIMAL UNION PUBLIC AND START UID COMMENT CURRENT LEVELzrrrrrceZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Z ddZdZdZdZeZdZdZdZdZdZdZdZdZdZdZy )OracleTypeCompilerc (|j|fi|SN) visit_DATEselftype_kws e/mnt/ssd/data/python-lab/Trading/venv/lib/python3.12/site-packages/sqlalchemy/dialects/oracle/base.pyvisit_datetimez!OracleTypeCompiler.visit_datetime(tu+++c (|j|fi|SrF) visit_FLOATrHs rL visit_floatzOracleTypeCompiler.visit_float+st,,,rOc (|j|fi|SrF)visit_DOUBLE_PRECISIONrHs rL visit_doublezOracleTypeCompiler.visit_double.s*t**57B77rOc z|jjr|j|fi|S|j|fi|SrF)dialect_use_nchar_for_unicodevisit_NVARCHAR2visit_VARCHAR2rHs rL visit_unicodez OracleTypeCompiler.visit_unicode1s? << . .'4''44 4&4&&u33 3rOc d|jduxrd|jzxsdd|jduxrd|jzxsdS)Nz INTERVAL DAYz(%d)z TO SECOND) day_precisionsecond_precisionrHs rLvisit_INTERVALz!OracleTypeCompiler.visit_INTERVAL7sj   t + -,,,    " "$ . 0///     rOc y)NrrHs rL visit_LONGzOracleTypeCompiler.visit_LONGArOc :t|ddry|jryy)Nlocal_timezoneFrAr@r)getattrtimezonerHs rLvisit_TIMESTAMPz"OracleTypeCompiler.visit_TIMESTAMPDs 5*E 23 ^^-rOc *|j|dfi|S)NrB_generate_numericrHs rLrTz)OracleTypeCompiler.visit_DOUBLE_PRECISIONLs%t%%e-?F2FFrOc *|j|dfi|S)NrrkrHs rLvisit_BINARY_DOUBLEz&OracleTypeCompiler.visit_BINARY_DOUBLEOs%t%%e_CCCrOc *|j|dfi|S)NrrkrHs rLvisit_BINARY_FLOATz%OracleTypeCompiler.visit_BINARY_FLOATRs%t%%e^BrBBrOc 4d|d<|j|dfi|S)NT_requires_binary_precisionrrkrHs rLrQzOracleTypeCompiler.visit_FLOATUs'+/ '(%t%%eW;;;rOc *|j|dfi|S)NrrkrHs rL visit_NUMBERzOracleTypeCompiler.visit_NUMBERYs%t%%eX<<|jrdd|jizSy)NzRAW(%(length)s)rr)rrHs rL visit_RAWzOracleTypeCompiler.visit_RAWs <<$%,,'?? ?rOc y)NrrbrHs rL visit_ROWIDzOracleTypeCompiler.visit_ROWIDsrOc |j |jnd}|j|jjnd}|j|jjnd}d|d|d|dS)N*zVECTOR(,))dimstorage_formatvalue storage_type)rIrJrKrrrs rL visit_VECTORzOracleTypeCompiler.visit_VECTORs~ 990eiic##/  & &  ).(:(:(FE   $ $C Q~.a ~Q??rO)NNF)r} __module__ __qualname__rMrRrUr[r`rcrirTrnrprQrtrlrrZrYvisit_NVARCHARrrrrrrrrrrrbrOrLrDrD"s ,-84  GDC<=#( +NZ034%N2J,0 ,<0  @rOrDceZdZdZej ejje jjdiZfdZ dZ dZdZdZdZd Zd Zd Zd Zfd ZfdZdZd*dZdZdZdZdZdZfdZdZ d+fd Z!dZ"dZ#dZ$dZ%dZ&dZ'dZ(d Z)d!Z*d"Z+d*d#Z,d$Z-d%Z.d&Z/d'Z0d(Z1d)Z2xZ3S),OracleCompilerzOracle compiler modifies the lexical structure of Select statements to work under non-ANSI configured Oracle databases, if the use_ansi flag is False. MINUSc2i|_t||i|yrF)_OracleCompiler__wheressuper__init__)rIargskwargsr|s rLrzOracleCompiler.__init__s  $)&)rOc d|j|jfi|d|j|jfi|dS)Nzmod(, rprocessleftrightrIbinaryoperatorrKs rLvisit_mod_binaryzOracleCompiler.visit_mod_binarys: DLL + + DLL , ,  rOc y)NCURRENT_TIMESTAMPrbrIfnrKs rLvisit_now_funczOracleCompiler.visit_now_funcs"rOc .d|j|fi|zS)NLENGTHfunction_argspecrs rLvisit_char_length_funcz%OracleCompiler.visit_char_length_funcs /$//9b999rOc xd|j|jd|j|jdS)Nz CONTAINS (rrrrs rLvisit_match_op_binaryz$OracleCompiler.visit_match_op_binary, LL % LL &  rOc y)N1rbrIexprrKs rL visit_truezOracleCompiler.visit_truerOc y)N0rbrs rL visit_falsezOracleCompiler.visit_falserrOcy)NWITHrb)rI recursives rLget_cte_preamblezOracleCompiler.get_cte_preamblerdrOcNdjd|jDS)N c3,K|] \}}d|zyw)z /*+ %s */Nrb).0tabletexts rL z6OracleCompiler.get_select_hint_text..sN{ud d*Ns)joinitems)rIbyfromss rLget_select_hint_textz#OracleCompiler.get_select_hint_textsxxNgmmoNNNrOc t|jdkDs |jjtvr!t j j||fi|Sy)Nrr])lenclausesryupper NO_ARG_FNSr- SQLCompilerrrs rLrzOracleCompiler.function_argspecsD rzz?Q "''--/"C''88rHRH HrOc t||fi|}|jddr"|jj dk7rd|z}|S)NasfromFrz TABLE (%s))rvisit_functiongetrylower)rIr/rKrr|s rLrzOracleCompiler.visit_functionsFw%d1b1 66(E "tyy'8G'C$&D rOc 2t||fi|}|dz}|S)Nz .COLUMN_VALUE)rvisit_table_valued_column)rIelementrKrr|s rLrz(OracleCompiler.visit_table_valued_columns&w0?B?o% rOcy)zCalled when a ``SELECT`` statement has no froms, and no ``FROM`` clause is to be appended. The Oracle compiler tacks a "FROM DUAL" to the statement. z FROM DUALrbrIs rL default_fromzOracleCompiler.default_fromsrOc |jjr#tjj||fd|i|S|r1|j j |j|jfd|d<t|jtjr|jj}n |j}|j|jfd|i|dz|j|fd|i|zS)N from_linterTrr)rWuse_ansir-r visit_joinedgesaddrr isinstancer. FromGroupingrr)rIrrrrs rLrzOracleCompiler.visit_joins << ''22d(37= !!%%tyy$**&=>#F8 $**j&=&=> **  TYYJKJ6J$,,uH+HHI rOcgfd|D]%}t|tjs|'sytjS)Ncjr8fd}jtjjid|injjj j fD]R}t|tjr |&t|tjsA|jTy)Nct|jtjrJjj |jj rt|j|_yt|jtjrKjj |jj rt|j|_yyyrF)rrr. ColumnClauseris_derived_fromr_OuterJoinColumn)rrs rL visit_binaryzVOracleCompiler._get_nonansi_join_whereclause..visit_join..visit_binary-s! Z%<%<**44V[[5F5FG&6v{{&C # j&=&=**44V\\5G5GH'7 'E IrOr) isouterappendr5cloned_traverseonclauserrrr.Joinrr)rrjrrs` rLrz@OracleCompiler._get_nonansi_join_whereclause..visit_join's|| F,, rHl+C t}}-YY * *a1qM:#:#:;qyy)  *rO)rr.r r#r*)rIfromsfrrs @@rL_get_nonansi_join_whereclausez,OracleCompiler._get_nonansi_join_whereclause$sJ *< A!Z__-1  88W% %rOc B|j|jfi|dzS)Nz(+))rcolumn)rIvcrKs rLvisit_outer_join_columnz&OracleCompiler.visit_outer_join_columnNs!t||BII,,u44rOc >|jj|dzS)Nz.nextval)preparerformat_sequence)rIseqrKs rLvisit_sequencezOracleCompiler.visit_sequenceQs}},,S1J>>rOc d|zS)z+Oracle doesn't like ``FROM table AS alias``rrb)rIalias_name_texts rLget_render_as_alias_suffixz)OracleCompiler.get_render_as_alias_suffixTs_$$rOc  g}g}ttj|D]\}}|jr_t |t j rEt |jtr+|jjstjd|jjr|jj|} n|} t!j"d|z|j} | |j$| j&<|j)|j+|j-| |j.rt1j2dd|_|j)|j7| d|s`|j9t;| d| j<t;| d| j<|t;|ddt;|d df|jd d j?|zd zd j?|zS) Na}Computed columns don't work with Oracle Database UPDATE statements that use RETURNING; the value of the column *before* the UPDATE takes place is returned. It is advised to not use RETURNING with an Oracle Database computed column. Consider setting implicit_returning to False on the Table object in order to avoid implicit RETURNING clauses from being generated for this Table.zret_%d)rJzUsing explicit outparam() objects with UpdateBase.returning() in the same Core DML statement is not supported in the Oracle Database dialects.TF)within_columns_clauserykeyz RETURNING rz INTO ) enumerater._select_iterablesisupdater sa_schemaColumnserver_defaultr rW(_supports_update_returning_computed_colsr$warntype_has_column_expressioncolumn_expressionr#outparambindsr r bindparam_string_truncate_bindparamhas_out_parametersr!InvalidRequestError_oracle_returningr_add_to_result_maprg_anon_name_labelr) rIstmtreturning_colspopulate_result_maprKcolumnsr-ircol_exprr,s rLreturning_clausezOracleCompiler.returning_clauseYs"  ( ( 8 8 IAv vy'7'78v44h? MM M{{11!;;88@!||HqL DH'/DJJx|| $ LL%%d&>&>x&HI &&--H &*D " NN4<<<N O"''Hfh.G.GHHfh.G.GH5t4 KK _8 tdii008;dii>NNNrOc |j|jjst||fddi|S|j |f|j |dd|S)zmOracle Database 12c supports OFFSET/FETCH operators Use it instead subquery with row_number "use_literal_execute_for_simple_intT) fetch_clauser=) _fetch_clauserW_supports_offset_fetchr_row_limit_clauser>_get_limit_or_fetch)rIr2rKr|s rLrAz OracleCompiler._row_limit_clauses}  ,<<667,;?CE %4$$!55f=37  rOcJ|j |jS|jSrF)r? _limit_clause)rIr2s rLrBz"OracleCompiler._get_limit_or_fetchs&    ''' ''' 'rOc t||f|||d|}|jddrtjdd|}|S)N)r>require_offsetr=oraclefetch_approximatez FETCH FIRSTzFETCH APPROX FIRST)rr>dialect_optionsresub)rIr2r>rFr=rKrr|s rLr>zOracleCompiler.fetch_clauses]w#  %)2     ! !( +,? @66-)=tDD rOc R |}t|dds|jjsN|j||j dd}|j |}||j |}d|_|jr|jjs|j|j}|j}|j|r|j}|j|r|j}|}|j}d|_|j } | k| j"r_| j%} | j'| j"D]0} |j(j+| r|j,| }2|j.} t1j2| j4D cgc] } |j(j7|  | "c} } |`|jj8rJ|j|r8| j;t=j>d|j@|fi|z} d| _d| _!| O| j"rCtEjF| }| j"D cgc]} |jI| c} | _|`|j|r||j|r |}|||z}n |}|||z}| j t1jJd|k} | | | _| }|S| j-t1jJdjMd} d| _d| _!| M| j"rA| j(}| j"D]&} |j7|  | j-| } (| j/}|j(}t1j2|j4D cgc]} |j7|  | c} }d|_d|_!| O| j"rCtEjF|}| j"D cgc]} |jI| c} | _|j t1jJd|kD}| |_|}|Scc} wcc} wcc} wcc} w)N _oracle_visitrFTz/*+ FIRST_ROWS(%s) */ROWNUMora_rn)'rgrWr_display_froms_for_selectrrwhererM_has_row_limiting_clauser@r?rD_offset_clause_simple_int_clauserender_literal_execute _generate_for_update_argof_clone_copy_internalsselected_columnscontains_column add_columnsaliasr#r2ccorresponding_columnoptimize_limits prefix_withr.rr _is_wrappersql_util ClauseAdaptertraverseliteral_columnlabel)rI select_stmtrr2r whereclause limit_clause offset_clause orig_select for_updateeleminner_subqueryr_ limitselectadaptermax_rowlimitselect_colslimit_subqueryorigselect_cols offsetselects rLtranslate_select_structurez)OracleCompiler.translate_select_structuresv5<<((66FJJx7#@@G *)V\\+6F+/F(// ;;((0%33 & 5 5 ,6,,\:#/#F#F#HL,6,,];$1$H$H$JM % )))+'+$$33 )jmm!+!2!2!4J..0 * >%66FFtL%7V%7%7%=F> ".!jj"0!1!1&77LLQO#$ !, 44111,?"-"9"9"3*dll<B6BC#K-1 )*. ')jmm&44^DG;E==%37((.%JM  +0v00>%-4644]C".(4&- &=G#/(4&- &=G"-"3"3**84?#K !(2L0)F C4%^)s%R R%RR$c yrrb)rIr2rKs rLrkzOracleCompiler.limit_clauseasrOc y)NzSELECT 1 FROM DUAL WHERE 1!=1rbrHs rLvisit_empty_set_exprz#OracleCompiler.visit_empty_set_exprds.rOc 2jryd}|jjr5|ddjfd|jjDzz }|jjr|dz }|jj r|dz }|S)Nr]z FOR UPDATEz OF rc3DK|]}j|fiywrF)r)rrorKrIs rLrz3OracleCompiler.for_update_clause..ns&&-1  T(R(& z NOWAITz SKIP LOCKED) is_subqueryrWrXrnowait skip_locked)rIr2rKtmps` ` rLfor_update_clausez OracleCompiler.for_update_clausegs      ! ! $ $ 6DII&5;5K5K5N5N& C  ! ! ( ( 9 C  ! ! - - > !C rOc xd|j|jd|j|jdS)NDECODE(rz , 0, 1) = 1rrs rLvisit_is_distinct_from_binaryz,OracleCompiler.visit_is_distinct_from_binaryyrrOc xd|j|jd|j|jdS)Nrrz , 0, 1) = 0rrs rL!visit_is_not_distinct_from_binaryz0OracleCompiler.visit_is_not_distinct_from_binaryrrOc |j|jfi|}|j|jfi|}|jd}| d|d|dSd|d|d|j |t j dS)Nflagsz REGEXP_LIKE(rrrrr modifiersrender_literal_valuer4 STRINGTYPE)rIrrrKstringpatternrs rLvisit_regexp_match_op_binaryz+OracleCompiler.visit_regexp_match_op_binarysfkk0R0$,,v||2r2  ) =,2G< < ))%1D1DE rOc 0d|j||fi|zS)NzNOT %s)rrs rL visit_not_regexp_match_op_binaryz/OracleCompiler.visit_not_regexp_match_op_binarys,;$;; H "   rOc |j|jfi|}|j|jfi|}|jd}| d|d|dSd|d|d|j |t j dS)NrzREGEXP_REPLACE(rrr)rIrrrKrpattern_replacers rLvisit_regexp_replace_op_binaryz-OracleCompiler.visit_regexp_replace_op_binarysfkk0R0&$,,v||:r:  ) =  ))%1D1DE rOc .d|j|fi|zS)Nz LISTAGG%srrs rLvisit_aggregate_strings_funcz+OracleCompiler.visit_aggregate_strings_funcs 2T222<<<rxrkr{rrrrrrrrrrrrrr __classcell__r|s@rLrrs )((..  " " ) )73 * #: O   ((&T5?% @OD*(+0 .Rh/$    =-;:;JJGrOrcReZdZ d dZdZdZdZdZfdZdZ dZ xZ S) OracleDDLCompilercg}dddddd}|jtjk(r|jdn.|jtjk(r|jd|j (|jd |j j |j|jd |jd |jjg}|jjjd z}t|D]h}|jj|s|j|j}t||j}|S|j|d |jdj|}|jd|d|j|jd|jd j|S)N neighborsefconstructionzneighbor partitionssample_per_partitionmin_vectors_per_partition)hnsw_neighborshnsw_efconstructionivf_neighbor_partitionsivf_sample_per_partitionivf_min_vectors_per_partitionz$ORGANIZATION INMEMORY NEIGHBOR GRAPHz ORGANIZATION NEIGHBOR PARTITIONSz DISTANCE zWITH TARGET ACCURACY ztype _rrz PARAMETERS (rz PARALLEL ) index_typerHNSWr IVFdistanceraccuracyryrr startswithrrgrparallel) rIvector_index_configpartssql_param_nameparameters_strprefixfieldr rs rL_build_vector_index_configz,OracleDDLCompiler._build_vector_index_configs)#3'<(>-H    ) )_-A-A A LL? @ + +/B/B B LL; <  ' ' 3 LL9%8%A%A%G%G$HI J  ' ' 3 LL'(;(D(D'EF ""5"@"@"E"E!FGH$//44::2 |N#3156  ' ' 3 LL9%8%A%A$BC DxxrOcd}|j|d|jzz }|jtjd|S)Nr]z ON DELETE %szOracle Database does not contain native UPDATE CASCADE functionality - onupdates will not be rendered for foreign keys. Consider using deferrable=True, initially='deferred' or triggers.)ondeleteonupdater$r()rI constraintrs rLdefine_constraint_cascadesz,OracleDDLCompiler.define_constraint_cascadessN    * Oj&9&99 9D    * II  rOc Rd|jj|jzS)NzCOMMENT ON TABLE %s IS '')r format_tabler)rIdroprKs rLvisit_drop_table_commentz*OracleDDLCompiler.visit_drop_table_comments'*T]]-G-G LL.   rOc t|j}j|j}d}|jr|dz }|jddr|dz }|jdd}|r|dz }|dj |d d |j |jd d djfd|jDdz }|jdddur2|jddd ur|dz }n|d|jddzz }|r%|d ur t}|dj|zz }|S)NzCREATE zUNIQUE rGbitmapzBITMAP vectorzVECTOR zINDEX T)include_schemaz ON ) use_schemaz (rc3ZK|]"}jj|dd$yw)FT include_table literal_bindsN) sql_compilerr)rrrIs rLrz7OracleDDLCompiler.visit_create_index..s8!!))T*s(+rcompressFz COMPRESSz COMPRESS %dr) r_verify_index_tableruniquerI_prepared_index_namerrr expressionsrr)rIcreaterKindexrrvector_optionss` rLvisit_create_indexz$OracleDDLCompiler.visit_create_indexsf   '== << I D   *8 4 I D..x8B  I D  % %eD % A  ! !%++$ ! ? II"--       *: 6e C$$X.z:dB #))(3J? %!2!4 C$99.II ID rOcg}|jd}|dr7|djddj}|jd|z|dr0|ddur|jdn|jd |dz|d r0|jd |jj |d zd j |S) NrG on_commitrrz ON COMMIT %srTz COMPRESSz COMPRESS FOR %s tablespacez TABLESPACE %sr])rIreplacerr rquoter)rIr table_optsoptson_commit_optionss rLpost_create_tablez#OracleDDLCompiler.post_create_table*s $$X.   $[ 1 9 9#s C I I K    /2CC D  J4'!!-0!!"6$z:J"KL     "T]]%8%8l9K%LL wwz""rOct||}|jdd}|jdd}|jdd}|j||jrdndz }|j S) Nz NO MINVALUE NOMINVALUEz NO MAXVALUE NOMAXVALUEzNO CYCLENOCYCLEz ORDERz NOORDER)rget_identity_optionsrorderstrip)rIidentity_optionsrr|s rLrz&OracleDDLCompiler.get_identity_options=spw+,<=||M<8||M<8||J 2  ! ! -  0 6 6HJ FDzz|rOc d|jj|jddz}|jdurt j d|jdur|dz }|S)NzGENERATED ALWAYS AS (%s)FTrzOracle Database computed columns do not support 'stored' persistence; set the 'persisted' flag to None or False for Oracle Database support.z VIRTUAL)rrsqltext persistedr!r)rI generatedrKrs rLvisit_computed_columnz'OracleDDLCompiler.visit_computed_columnFsy)D,=,=,E,E   U$-F-     $ &""+   E ) J D rOc |jd}n|jrdnd}d|z}|jr|dz }|dz }|j|}|r|d|zz }|S)Nr]ALWAYSz BY DEFAULTz GENERATED %sz ON NULLz AS IDENTITYz (%s))alwayson_nullr)rIidentityrKkindroptionss rLvisit_identity_columnz'OracleDDLCompiler.visit_identity_columnTsl ?? "D'8LD$    J D ++H5  Gg% %D rO)rrreturnstr) r}rrrrrrrrrr rrs@rLrrs='#4' 'R$ "H#&  rOrceZdZeDchc]}|j c}}ZeddDchc] }t|c}}}jddgZ dZ fdZ xZ Scc}}wcc}}}w)OracleIdentifierPreparerr r$c|j}||jvxs8|d|jvxs%|jj t | S)z5Return True if the given identifier requires quoting.r)rreserved_wordsillegal_initial_characterslegal_charactersmatchr)rIrlc_values rL_bindparam_requires_quotesz3OracleIdentifierPreparer._bindparam_requires_quotesisW;;= ++ + ;Qx4::: ;((..s5z:: rOcZ|jjd}t| ||S)Nr)identlstriprformat_savepoint)rI savepointryr|s rLrz)OracleIdentifierPreparer.format_savepointrs)%%c*w' 488rO) r}rrRESERVED_WORDSrrrangerunionrrrr)rxdigrr|s0000@rLrrcs`)78Aaggi8N6;Arl!C!Cs#c(!C!I!I c " 999!Cs A'A-rceZdZdZdZy)OracleExecutionContextcd|jd|jj|zdz|S)NzSELECT z.nextval FROM DUAL)_execute_scalaridentifier_preparerr)rIrrJs rL fire_sequencez$OracleExecutionContext.fire_sequencexs>## &&66s; <" #    rOc|jrLd|jvr=|jjtj|jd|_yyy)N_oracle_dblink) statementexecution_optionsrr DB_LINK_PLACEHOLDERrs rLpre_execzOracleExecutionContext.pre_execsM >>.$2H2HH!^^33..&&'78DNI>rON)r}rrr*r0rbrOrLr&r&ws  rOr&cPeZdZdZdZdZdZdZdZdZ dZ dZ dZ dZ dZdZdZdZdZeZeZdZdZdZdZdZdZeZeZeZ e!Z"e#Z$dZ%dZ&e'jPdddddfe'jRdddd fe*jVd dife*jXd difgZ-e.j^d  dPd Z0fdZ1dZ2e3dZ4e3dZ5e3dZ6e3dZ7e3dZ8e3dZ9dZ:dZ;dZe.j~dZ@eAj dRdZCeAj dRdZDdZEfdZFeAjd eHjfd!eHjfd"eHjfd#ZKeLd$ZMeAjd eHjfd%eHjfd&eHjfd!eHjfd"eHjfd'ZOd(ZPd)ZQeAjdQd*ZReAjdRd+ZSeAjdQd,ZTeAj dSd-ZUeAjdRd.ZVeAjdRd/ZWd0ZXd1ZYeAjdQd2ZZeLd3Z[ePdd4d5Z\eAjdQd6Z]d7Z^eLd8Z_ePdd4d9Z`d:ZaeAjdQd;ZbeLd<ZcePdd4d=ZdeAjdQd>ZeeLd?ZfeAjd eHjfd"eHjfd@eHjfdAZgePdd4dBZheAjdQdCZieLdDZjeAjd eHjfd"eHjfd@eHjfdEZkePdd4dFZleAj dQdGZmePdd4dHZneAj dQdIZoePdd4dJZpeAj dRdKZqeAj dTdLZrePdddMdNZsdQdOZtxZuS)U OracleDialectrGTFnamed)oracle_resolve_synonymsN)resolve_synonymsrrr)rrrrH)z1.4a The ``use_binds_for_limits`` Oracle Database dialect parameter is deprecated. The dialect now renders LIMIT / OFFSET integers inline in all cases using a post-compilation hook, so that the value is still represented by a 'bound parameter' on the Core Expression side.)use_binds_for_limitsc tjj|fi|||_||_||_||_|x|_|_yrF) r%DefaultDialectrrXrraexclude_tablespacesenable_offset_fetchr@)rIrrar7use_nchar_for_unicoder:r;rs rLrzOracleDialect.__init__sN( ''77&;#  .#6   4#>rOcFt|||jrO|jj |_|jj t jd|_|jdk\|_ |jxr|jdk\|_ y)NF ) r initialize _is_oracle_8colspecscopypopr4Intervalrserver_version_infosupports_identity_columnsr;r@)rI connectionr|s rLr@zOracleDialect.initializes :&    MM..0DM MM  h// 0!DM)-)A)AU)J&  $ $ J)A)AU)J #rOc4|jdkr |jS |jdj}|r" t d|j dDS|jS#tj$rd}YGwxYw#|jcYSxYw)Nr?z7SELECT value FROM v$parameter WHERE name = 'compatible'c32K|]}t|ywrF)rz)rr#s rLrzJOracleDialect._get_effective_compat_server_version_info.. s?SV?s.)rFexec_driver_sqlscalarr! DBAPIErrortuplesplit)rIrHcompats rL)_get_effective_compat_server_version_infoz7OracleDialect._get_effective_compat_server_version_infos  # #g -++ + //Ifh   0?V\\#->???++ +~~ F   0///sA, B,BBBc<|jxr|jdkS)N) rFrs rLrAzOracleDialect._is_oracle_8s''KD,D,Dt,KKrOc<|jxr|jdk\S)N)rr rWrs rL_supports_table_compressionz)OracleDialect._supports_table_compressions''OD,D,D,OOrOc<|jxr|jdk\S)N) rWrs rL_supports_table_compress_forz*OracleDialect._supports_table_compress_for''MD,D,D,MMrOc|j SrF)rArs rLrz#OracleDialect._supports_char_lengths$$$$rOc<|jxr|jdk\S)N)rWrs rLr'z6OracleDialect._supports_update_returning_computed_colss ''MD,D,D,MMrOc<|jxr|jdk\S)N)rWrs rL_supports_except_allz"OracleDialect._supports_except_all%r]rOcyrFrb)rIrHrys rLdo_release_savepointz"OracleDialect.do_release_savepoint)s rOc.|j|dkryy)NrJ)rTrIrHs rL_check_max_identifier_lengthz*OracleDialect._check_max_identifier_length-s%  9 9* EI  rOc ddgS)NREAD COMMITTED SERIALIZABLErb)rIdbapi_connections rLget_isolation_level_valuesz(OracleDialect.get_isolation_level_values7s  .11rOcF |j|S#t$rYyxYw)Nrk)get_isolation_levelNotImplementedError)rI dbapi_conns rLget_default_isolation_levelz)OracleDialect.get_default_isolation_level:s- $++J7 7"   $#s c|r|jdsd|}|xsddd}|r|rd}tj|id|i}|j|||S)N@r])r,schema_translate_mapcd|_y)NT)literal_executer+s rLvisit_bindparamz:OracleDialect._execute_reflection..visit_bindparamTs ,0 )rOr,)r.)rr5r execute)rIrHquerydblink returns_longparamsr.rys rL_execute_reflectionz!OracleDialect._execute_reflectionBs &++C0\F%l$(   l  1,,rK9E!! 6->"  rOczttjjjtjjj j ttjjjjdtjjj jd}t|jjj|jjtdk(|jj tdk(}|S)N table_nametables_and_viewsowner) r2r all_tablesr_rr union_all all_views view_namerhsubqueryrQr,)rItablesr{s rL_has_table_queryzOracleDialect._has_table_query^s %%''22%%''-- Y((**44::<H((**00 X( ) vxx**+11 HH  9\#: : HHNNi0 0  rOc |j||s |j}|j||j|d}|j ||j |d|}t |jS)@Supported kw arguments are: ``dblink`` to reflect via a db link.)rrFr}r~)_ensure_has_table_connectiondefault_schema_namedenormalize_namedenormalize_schema_namerrboolrO)rIrHrr"r|rKr~cursors rL has_tablezOracleDialect.has_tableus ))*5--F// ;11&9 ))   ! !  * FMMO$$rOc |s |j}ttjjj j tjjj |j|k(tjjj|j|k(}|j|||d}t|jSrFr}) rr2r all_sequencesr_ sequence_namerQrsequence_ownerrrrO)rIrHrr"r|rKr{rs rL has_sequencezOracleDialect.has_sequences --Fz//11??@FF  $ $ & & 4 4++M: ;  $ $ & & 5 5++F3 4 )) vE* FMMO$$rOc^|j|jdjS)Nz;select sys_context( 'userenv', 'current_schema' ) from dual)normalize_namerNrOrhs rL_get_default_schema_namez&OracleDialect._get_default_schema_names-""  & &M fh  rOcLt|dd}||dk(ryt| |S)NrpublicPUBLIC)rgrr)rIryforcer|s rLrz%OracleDialect.denormalize_schema_names0gt, =TX-w'--rOr" filter_namesr|c |j|xs |j}|j|\}}ttj j jtj j jtj j jtj j jjtj j j|k(} |rE| jtj j jj|d} |j|| |dj} | j!S)NrFr)rr_prepare_filter_namesr2r all_synonymsr_ synonym_namer table_ownerdb_linkrQrin_rmappingsall) rIrHr"rr|rKrhas_filter_namesr~r{results rL _get_synonymszOracleDialect._get_synonymss1 ,,  .d.. $(#=#=l#K &  # # % % 2 2  # # % % 0 0  # # % % 1 1  # # % % - -  % ''))//58 9  KK''))66::>*E )) vE* (* zz|rOcttjjjj tjj tjjj|k(}|tjurD|j tjjjjd}ng}tj|vr|jdtj|vr#tj|vr|jdtj|vrp|jd|r]tj|vrK|j tjjjj!t#d}|j tjjjj|}|t$j&ur7|j tjjj(dk(}nH|t$j*ur6|j tjjj(dk(}|rK|j tjjjjt#d}|S) N)TABLEVIEWrzMATERIALIZED VIEWr mat_viewsrYr)r2r all_objectsr_ object_name select_fromrQrr&ANY object_typerrr MATERIALIZED_VIEWrnot_inr,r'DEFAULT temporary TEMPORARY)rIrscoper r has_mat_viewsr{rs rL_all_objects_queryz OracleDialect._all_objects_querys :))++77 8 [// 0 U:))++11U: ;  :>> !KK&&((44889JKEK$&""6*,,4$$D0""#674'""7+ Z%A%A%M"KK"..00<<CC%k2E KK&&((4488EE K'' 'KK 6 6 8 8 B Bc IJE k++ +KK 6 6 8 8 B Bc IJE KK&&((4488n-E  rOrr c |j|xs |j}|j|\} } d} tj|vr2tj |vr |j |||fddi|} | r| | d<d} |j|||| | } |j|| |d| j}|jS)NF _normalizerTr) rrrr&rrget_materialized_view_namesrrscalarsr)rIrHr"rr rr|rKrrr~rrr{rs rL_get_all_objectszOracleDialect._get_all_objects s,,  .d.. $(#=#=l#K &    $,,D8988FF7<@BI&/{# $ '' 5$ 0- )) vE&* ') zz|rOc.tfd}|S)Nc0|jg|i|SrF)_handle_synonyms)rIrrrs rLwrapperz9OracleDialect._handle_synonyms_decorator..wrapper/ s (4((=d=f= =rOr)rrs` rL_handle_synonyms_decoratorz(OracleDialect._handle_synonyms_decorator. s r >  >rOc |jdds |||g|i|S|j}|jdd}|j|||jdd|jdd|jdd}t t }|D]+} | d| d f} |j | d } | d || | <-|s |||g|i|Si} |jD]d\\} }}i|||j | |jd }|||g|i|}|D]#\\}} }|j || }|| ||f<%f| jS) Nr5Fr"rr| info_cache)r"rr|rrrrr)r"r|r) rrCrDrrdictrrkeys)rIrrHrr original_kwr"rdblinks_ownersrowr tndatar|rmappingcall_kw call_resultrrrs rLrzOracleDialect._handle_synonyms5 szz3U;dJ888 8kkm Hd+## ND9::h-zz,5 $ %T* :Ci.#m"44C$$S%67B&).&9N3  # : dJ=== =.<.B.B.D 5 * !V[7%--f5 '  G T:@@@K"- 5B#2272;? /4fl+, 5 5zz|rOc Lttjjjj tjjj}|j |||dj}|Dcgc]}|j|c}Scc}wr) r2r all_usersr_usernameorder_byrrr)rIrHr|rKr{rrs rLget_schema_nameszOracleDialect.get_schema_names[ sz++--667@@  " " + + )) vE* ') 5;;S##C(;;;sB!c  | |j}|j|}|jddrttj j jtj j jtj j jtj j jtj j jjttjj jjdtjj jtj j jtj j jtj j jj!tj j#tjt%tj j jtjj jk(tj j jt'j(tjj j*tjj jk(j-d}ntj }t|j j}|j.rR|j1t'j(|j jdj3|j.}|j1|j j|k(|j jj5t7|j jj5t7}ttj8j j:jdj1tj8j j|k(}|j<r|j?|n|jA|}|jC|||djE} | D cgc]} |jG| c} Scc} w)rr5Fravailable_tables no tablespacer)$rrrr2r rr_rriot_namedurationtablespace_namerrrrhrrr*r/coalescerrr:rQris_r0 all_mviews mview_namerc except_allexcept_rrr) rIrHr"r|rK den_schemarr{ mat_queryrrs rLget_table_nameszOracleDialect.get_table_namesf sp >--F11&9 66+U 3))++66))++11))++44))++44))++;; "//11>>DD(#//1177"--//88"--//88"--//??![!6!67T"//&1133>>)6688CCD&113399#}} * 7 7 9 9 E E * 7 7 9 9 ? ?  0,-A F **Fvxx**+  # #KK HH,,o&112E  HHNNj ( HH   ! !$& ) HH   ! !$& )   ! ! # # . . 4 4\ B % %%''--; <  ((   Y 'y)  )) vE* ') 5;;S##C(;;;s,Sc L|j|j}ttjj j }|jr`|jtjtjj jdj|j}|jtjj j|k(tjj jjt!tjj j"j%t!}|j'|||dj)}|Dcgc]}|j+|c}Scc}w)rrFr)rrr2r rr_rr:rQr/rrrrrrr0ris_notrrr)rIrHr|rKr"r{rrs rLget_temp_table_namesz"OracleDialect.get_temp_table_names s9--d.F.FGz,,..99:  # #KK ))++;;_&112E   ! ! # # ) )V 3  ! ! # # , , 0 0 8  ! ! # # , , 3 3DF ;  )) vE* ') 5;;S##C(;;;sF!c |s |j}ttjjj j tjjj|j|k(}|j|||dj}|r|Dcgc]}|j|c}S|jScc}wr) rr2r rr_rrQrrrrrr) rIrHr"r|rrKr{rrs rLrz)OracleDialect.get_materialized_view_names s --Fz,,..99:@@  ! ! # # ) )++F3 4 )) vE* ')  8>?D'',? ?::< @s(Cc |s |j}ttjjj j tjjj|j|k(}|j|||dj}|Dcgc]}|j|c}Scc}wr) rr2r rr_rrQrrrrrrIrHr"r|rKr{rrs rLget_view_nameszOracleDialect.get_view_names s--Fz++--778>>  " " ( (++F3 4 )) vE* ') 5;;S##C(;;;&Cc |s |j}ttjjj j tjjj|j|k(}|j|||dj}|Dcgc]}|j|c}Scc}wr) rr2r rr_rrQrrrrrrs rLget_sequence_namesz OracleDialect.get_sequence_names s--Fz//11??@FF  $ $ & & 5 5++F3 4  )) vE* ') 5;;S##C(;;;rc|jt|} t|||fS#t$r%t j |r |d|d|dwxYw)NrM)rrrKeyErrorr!NoSuchTableError)rIrrr"s rL_value_or_raisezOracleDialect._value_or_raise ss##CJ/ :vuo. . &&'-6(!E7# 38  s ,.Ac`|r$|Dcgc]}|j|}}dd|ifSdifScc}w)NTrF)r)rIrryrs rLrz#OracleDialect._prepare_filter_names sC :FG$$''-GBG."-- -"9 Hs+c |j|f||gtjtjd|}|j |||SSupported kw arguments are: ``dblink`` to reflect via a db link; ``oracle_resolve_synonyms`` to resolve names to synonyms )r"rrr )get_multi_table_optionsr'rr&rrIrHrr"rKrs rLget_table_optionszOracleDialect.get_table_options U ,t++  $//    ##D*f==rOc&ttjjj|j r$tjjj n"tjjd|jr$tjjjn"tjjdtjjjjtjjj|k(}|rK|jtjjjjt!d}|t"j$urK|jtjjj&j)t}n\|t"j*urJ|jtjjj&j-t}|rqt.j0|vr_t.j2|vrM|jtjjjj5t!d}|St.j0|vr]t.j2|vrK|jtjjjjt!d}|S)N compression compress_forrr)r2r rr_rrYrr#r0rhr\rrrQrrr,r'rrrrrr&rrr)rIrrr rrr{s rL_table_options_queryz"OracleDialect._table_options_query s8  ! ! # # . .33%%''33XXZ%%m444%%''44XXZ%%n5  ! ! # # 3 3   % %%''--6 7  KK%%''2266n-E K'' 'KK 5 5 7 7 @ @ D DTV LME k++ +KK%%''0077?E   D(,,D8KK%%''2299k*E   D (,,4KK%%''2266y7MNE rO)r|c n|j|xs |j}|j|\} } d} tj|vr3tj |vr!|j |||fddi|} | rH| | d<d} n@tj|vr.tj |vr|j |||fddi|} | | d<i} tj}tj|vstj |vre|j|||| | }|j|||d| }|D]5\}}}}|}|dk(r||d<|r||d<|| ||j|f<7tj|vr?tj|vr-|j|||fi|D]}|r||vs || ||f<| j!S) rFrrTrENABLEDoracle_compressoracle_tablespace)rrrr&rrrr) table_optionsrrrrr'rrr)rIrHr"rrr r|rKrrr~rrr r%r{rrrrrrviews rLrz%OracleDialect.get_multi_table_optionsP s,,  .d.. $(#=#=l#K &     $,,D8988FF7<@BI&/{# $   D (,,4888FF7<@BI#,F; $22   t #z'C'Ct'K--ud$4mE--E6f.FAG E<{L*y)+.:D*+0:D,-@D!4!4U!;<=  E ??d "{':':e'C+++JM"M 8#t|';.5iGVTN+ 8}}rOc |j|f||gtjtjd|}|j |||Sr)get_multi_columnsr'rr&rrs rL get_columnszOracleDialect.get_columns sU &t%%  $//    ##D*f==rOc#Kd}t|}|rK|d|} g|d||j||||d| i} |r| jEd{n | Ed{|rJyy77 w)Nirrr)listrr) rIrHr{r|r}rr each_batchbatchesbatchrs rL _run_batcheszOracleDialect._run_batches s {#Aj)E$&GAj !--)%u- .F!??,,,!!!-!s*AA" A A"A A"A" A"c tj}tj}tj}|jdk\r|j j tj|j jjdtjf|j jdz|j jzjdf}d}nHtjjdtjjdf}d}t|j j|j j |j j"|j j$|j j&|j j(|j j*|j j,|j j.|j j0g |j3|j5|t7|j j|j jk(|j j |j j k(|j j8|j j8k(}|r|j5|t7|j j|j jk(|j j |j j k(|j j8|j j8k(}|j;|j jj=t?d|j j@d k(|j j8|k(jC|j j|j jD}|S) Nr>r)else_rTdefault_on_nullFrNO)#r all_tab_colsall_col_commentsall_tab_identity_colsrFr_rr#caserrr0generation_typerrhr2 column_name data_type char_lengthdata_precision data_scalenullable data_defaultcommentsvirtual_columnr outerjoinr*rrQrr, hidden_columnr column_id)rIrall_cols all_commentsall_idsadd_colsjoin_identity_colsr{s rL _column_queryzOracleDialect._column_query s**!22 22  # #u , **YY))--d3SXXZ@!))33ii001 %*+H"&    !23   !34H"'   %% && $$ && )) %% ## '''' ))  k(#YJJ))\^^-F-FFJJ**lnn.H.HHJJ$$ (<(<<# 4 OOJJ))WYY-A-AAJJ**gii.C.CCJJ$$ 7E JJ ! ! % %i &> ? JJ $ $ , JJ   %  (8::(((***>*> ?   rOc |j|xs |j}|j|} |rC|tjur1|t jur|D cgc]} |j | } } n|j||||||fi|} tt} |j|| |dd| } d}tjd}| D]}|j|d}|d}|j|}|d}||d}|d k(r*||d }||d k(r t}nt||}n|d k(r-|dk(r t!}n|dk(r t#}nt%|}nz|dvr-||d}|j&j)||}nId|vr t+d}n8d|vr t+d}n'tj,|d|} |j&|}|d}|ddk(rt9|}d }nd }|d}||j;||d }d }nd }|||d!d"k(||d#d$}|j=|k(rd|d%<|||d&<|||d'<| ||fj?|| jAScc} w#t.$r/t1j2d|d|dt4j6}YwxYw)(rTr}rrc\t|tr|jr t|S|SrF)rfloat is_integerrz)rs rL maybe_intz2OracleDialect.get_multi_columns..maybe_int- s&%'E,<,<,>5z! rOz\(\d+\)rrrr!rr"Nrr~?)rw)rrr8r<r zWITH TIME ZONE)rhzWITH LOCAL TIME ZONE)rfr]zDid not recognize type 'z ' of column ''r$r&YES)rrrr#rr%)ryr)r#r%commentrcomputedr )!rrr/r&rr'rrrrrrJcompilerr;rr:r>r ischema_namesrrrKrr$r(r4NULLTYPEr_parse_identity_optionsrr r)rIrHr"rrr r|rKrr{rrr8rr5 remove_sizerow_dictr orig_colnamecolnamecoltypervrxr r%r;rr cdicts rLr zOracleDialect.get_multi_columns sT,,  .d.. ""5)  &(=IJ4003JKJ/$//FE4vIKKd#""   # #   jj, K 8H,,Xl-CDJ#M2L)),7G{+G!(+;"<=I("!(<"89$!%iG$Y6GG##/0G"_#fG$Y?GFF'(?@ 9$,,009+F!W,#T2'72#48&&b':0"009G~.G()U20'(:; +77$h/@&A $Z0C7"#J/ E!!#|3!%g#$,j!#$,j! VZ( ) 0 0 7WK 8f}}[K@ 0II"G-'//G 0sJJ  5KKc|jdDcgc]}|j}}|ddk(|dk(d}|ddD]}|jd\}}|j}d|vrt||d <:d |vrt||d <Md |vrt||d <`d|vrt||d<sd|vr |dk(|d<d|vrt||d<d|vs|dk(|d<|Scc}w)Nrrrr9)rr r :z START WITHstartz INCREMENT BY increment MAX_VALUEmaxvalue MIN_VALUEminvalue CYCLE_FLAGrcycle CACHE_SIZEcache ORDER_FLAGr)rRrrz) rIrrprr partoptionrs rLr?z%OracleDialect._parse_identity_options s %5$:$:3$?@q@@Ah(*&%/  !"I 1D JJsOMFEKKMEv%$'J!6)(+E %&'*5z$&'*5z$'$)SL!'$'J!'$)SL!# 1$1AsC$c |j|f||gtjtjd|}|j |||Sr)get_multi_table_commentr'rr&rrs rLget_table_commentzOracleDialect.get_table_comment rrOc g}tj|vstj|vrXttj j jtj j jjtj j j|k(tj j jjd}tj|vr7|jtj j jdk(}nHtj|vr6|jtj j jdk(}|j|tj|vrttjj j j#dtjj jjtjj j|k(tjj j jd}|j|t%|dk(r|d}nUt'j(|j+d} t| j j| j j}|j,j} |t.j0t.j2fvr|t.j2urdnd } |j5j7tj8t;tj8j j|k(tj8j j<| k(tj8j j>| k(}|r)|j| jAtCd }|S) NzBIN$%rrrr rrrrr)"r&rrr2r all_tab_commentsr_rr%rQrnot_like table_typer rall_mview_commentsrrhrr#rrr[r'rrdistinctrrr*rrrr,) rIrrr rqueriestbl_viewmat_viewr{r"name_coltemps rL_comment_queryzOracleDialect._comment_query s   t #z$'>++--88++--66e++--33u<++--88AA'J d*#>>//11<<G!!-#>>//11<<F NN8 $  ' '4 /--//::@@N--//88e--//55>--//::CCGL  NN8 $ w<1 AJEMM7+445GHE577--uww/?/?@E))44 [((+*?*?@ @;#8#883cDNN$))&&**,,22e;**,,88HD**,,66$>E KK Y~-F GHE rOc  jxs j}j|\} } j|||| } j || |d| } t j d fd| DS)rFrzsnapshot table for snapshot c3K|]8\}}j|f||jsd|inf:yw)Nr)rr)rrr:r%ignore_mat_viewr"rIs rLrz8OracleDialect.get_multi_table_comment.. s[  w,,U34*#..?W%!    s>A)rrrrdrr) table_comment)rIrHr"rrr r|rKrrr~r{rr%rgs` ` @@rLrWz%OracleDialect.get_multi_table_comment s,,  .d.. $(#=#=l#K &##E5$8HI)) vE&* %229  #)  rOc |j|f||gtjtjd|}|j |||Sr)get_multi_indexesr'rr&rrs rL get_indexeszOracleDialect.get_indexes sU &t%%  $//    ##D*f==rOc ttjjjtjjj tjjj tjjjtjjjtjjjtjjjtjjjtjjj jtjj!tjt#j$tjjj tjjj k(tjjj&tjjj(k(j+tjt#j$tjjj tjjj k(tjjj&tjjj&k(tjjj,tjjj,k(j/tjjj0|k(tjjjj3t5dj7tjjj tjjj,S)Nr)r2r all_ind_columnsr_r index_namer all_indexesr uniquenessr prefix_lengthdescendall_ind_expressionsr+rrr#r* index_ownerrr'column_positionrQrrr,r)rIrs rL _index_queryzOracleDialect._index_query) s **,,77**,,77**,,88&&((33&&((33&&((44&&((66**,,44..00BB [33 4 T&&..00;;!--//::;..00<<!--//556Y..2244??!1133>>?2244@@!1133??@2244DD!1133CCD "U&&((44=&&((3377m, X**,,77**,,<<[1 rOrc .|j|xs |j}|j|}|j||||fi|Dchc]}|ddk(r|d} }|j |||dd|} | Dcgc] }|d| vr| c}Scc}wcc}w)Nconstraint_typePconstraint_nameTr1rn)rrrv_get_all_constraint_rowsr) rIrHr"r|rrKrr{rApksrs rL_get_indexes_rowszOracleDialect._get_indexes_rows^ s ,,  .d.. !!%(:D99FFK;= )*c1 & '  ""   # # #  %S0   % $ s B :Bc j|||||fi|}ddd} ddd} ddh} ttj|||fi|D]~} j | d} j | d}|f}| |vrN| gi| j | d dd x|| <}|d }| d | vrd|d <| j | ddr| d|d<n|| }| d}||dj dd|vr|dj |n|ddd|d<|dj || djdk7s| djdk(sJ|jdi}d||<%| djdk(sJj | d}|dj |d|vsk|dj |tjfdfd|DDS)rFT) NONUNIQUEUNIQUE)DISABLEDrBITMAPzFUNCTION-BASED BITMAPrnrrp)ry column_namesrIrrIr oracle_bitmaprrqrr+Nrrrrascdesccolumn_sorting)rrc3pK|]-}||vrt|jnf/ywrFrvalues)rr r%indexess rLrz2OracleDialect.get_multi_indexes.. s: $ws|**,-WY O 36c3DK|]}j|fywrFrrobj_namer"rIs rLrz2OracleDialect.get_multi_indexes.. ),,X67r~) rrrr}rrr r setdefaultr)r)rIrHr"rrr r|rKrrpenabled is_bitmaprArnr table_indexes index_dictdorcscnr%rs` ` @@rLrjzOracleDialect.get_multi_indexes s,d++ t\6 EG $)D9 $667 d#...   79 ) 9H,,Xl-CDJ,,Xl-CDJ#VZ$89M.&$&')(nnXl-CUK : j)J  12L)Y6*.B';;x 6>,4_,EB()+:6 /0D>*11$7 J.}-44T:0:>0J3B0OJ}-}-44T:I&,,.%7#I.446&@@@#../?DB(BtH *002e;;;((-)@A>*11"5 J.}-44R8S) 9V%,,  +  rOc |j|f||gtjtjd|}|j |||Sr)get_multi_pk_constraintr'rr&rrs rLget_pk_constraintzOracleDialect.get_pk_constraint rrOc tjjd}tjjd}ttjj j tjj jtjj j|j jjd|j j jd|j jjd|j jjdtjj jtjj j jtjj|t!|j jtjj jk(tjj j|j jk(j#|t!tjj j$|j jk(tjj j&|j jk(t)|j j*j-t/j0|j j*|j j*k(j3tjj j|k(tjj j j5t7dtjj jj5dj9tjj j|j j*S) Nlocalremote local_column remote_table remote_column remote_ownerr)RryUC)r all_cons_columnsr^r2all_constraintsr_rrxrzrrhrsearch_condition delete_rulerrr*r'r_ownerr_constraint_namer1positionrr#r0rQrr,r)rIrrrs rL_constraint_queryzOracleDialect._constraint_query s++11':,,228< **,,77**,,<<**,,<<##)).9##)).9$$**?;$$^4**,,==**,,88 [33 4 TGGMMZ%?%?%A%A%G%GG..00@@ww../Y..0088FHHNNJ..00BBxx//0))--chhj9((FHH,=,==  U**,,22e;**,,77;;m,**,,<<@@( X**,,<N>NU- rOc |j|xs |j}|j|}t|j |||dd|}|S)NFTr1)rrrrr) rIrHr"r|rrKrr{rs rLr{z&OracleDialect._get_all_constraint_rows sl,,  .d.. &&u-   "'     rOc j|||||fi|}tttjj |||fi|D]p} | ddk7r j | d} j | d} j | d} | f} | s | | d<| g| d<]| dj| rfdfd |DDS) rrxryrrzrryconstrained_columnsc3BK|]}||vr|nfywrFrb)rr r% primary_keyss rLrz8OracleDialect.get_multi_pk_constraint..R s. sl':,s# J c3DK|]}j|fywrFrrs rLrz8OracleDialect.get_multi_pk_constraint..T rr~)rrrr) pk_constraintr{rr )rIrHrr"rr r|rKrrArrzrtable_pkr%rs` ` @@rLrz%OracleDialect.get_multi_pk_constraint, s,d++ t\6 EG #4( $22555   79  DH)*c1,,Xl-CDJ"11(;L2MNO--h~.FGK#VZ$89H#2 3>-././66{C D   +  rOc |j|f||gtjtjd|}|j |||Sr)get_multi_foreign_keysr'rr&rrs rLget_foreign_keyszOracleDialect.get_foreign_keysZ sU+t**  $//    ##D*f==rOc t"#j|||||fi|}|jdd} jxs j} t } t t #j|||fi|D]A} | ddk7r j| d} j| d}#| f}|Jj| d}j| d }j| d }| d }j|}|| j||6|r|jd sd |}tjd |xsdd||vr4|gd|gidx||<}| r||d<|| k7r||d<| d}|dk7r||dd<n||}|dj||dj|D| r| rttj j"j$tj j"j&tj j"j(tj j"j*j-tj j"j$j/| }j1|||dj3}i}|D]9}j|d}j|d} |d|df||| f<;d}#j5D]~}|j5D]i}|j7d|df}|j||\}}|s1j|} | |d<|| k7rj|}!|!|d<ed|d<kt8j:""#fdfd |DDS)!rr5FrxrrrzNrrrrruz6Got 'None' querying 'table_name' from all_cons_columnsr]z1 - does the user have proper rights to the table?)ryrreferred_schemareferred_tablereferred_columnsr  _ref_schemarrz NO ACTIONr rrrrrrrNNrc3pK|]-}||vrt|jnf/ywrFr)rr r%fkeyss rLrz7OracleDialect.get_multi_foreign_keys.. s: se|$uSz((*+ K rc3DK|]}j|fywrFrrs rLrz7OracleDialect.get_multi_foreign_keys.. rr~)rrrrsetrrr{rrrr$r(r r2r rr_rrrrrQrrrrrDr) foreign_keys)$rIrHrr"rr r|rKrr6rall_remote_ownersrArrz table_fkeyrrrremote_owner_origrfkeyrr{rremote_owners_lutr synonym_ownerempty table_fkeysr syn_namesnror%rs$` ` @@rLrz$OracleDialect.get_multi_foreign_keyso sf,d++ t\6 EG 66";UC,,  .d..  ED!555   79 8 ;H)*c1,,Xl-CDJ"11(;L2MNO 34J". ....x/GHL..x/GHL //0IJM ( 8 ../@AL ,!%%&78#&"3"3C"8 \F ''-|n522 j0++-'+&2(*! 6 ?+d$*6D'%):e)C.:D*+&}5 +-2=DOJ/"/2 & ' . .| < # $ + +M :q8 ;t  1''))//''))44''))55''))66  eJ++--33778IJK --E6.hj !#   $ 3 3CL A !00\1BC  &'B!=*"=>  !E$||~ A "-"4"4"6 AJ"}5"#34C.?-B-B3-N*L(!00:79 #34!-1F!%!4!4\!BB<>J'89<@J'89 A A%11  +  rOc |j|f||gtjtjd|}|j |||Sr)get_multi_unique_constraintsr'rr&rrs rLget_unique_constraintsz$OracleDialect.get_unique_constraints sU1t00  $//    ##D*f==rOc 2j|||||fi|}ttj|||fi|D chc]} | d } } j|||fi|D]} | ddk7r j | d} | d} j | } j | d}| f}| J| |vr| g| | vr| nddx|| <}n|| }|d j |tjfd fd |DDScc} w) rrnrxrrrzrN)ryrduplicates_indexrc3pK|]-}||vrt|jnf/ywrFr)rr r% unique_conss rLrz=OracleDialect.get_multi_unique_constraints..@sG  k)S)0023     rc3DK|]}j|fywrFrrs rLrz=OracleDialect.get_multi_unique_constraints..Irr~) rrrr}r{rr r)unique_constraints)rIrHrr"rr r|rKrrA index_namesrconstraint_name_origrzrtable_ucucr%rs` ` @@rLrz*OracleDialect.get_multi_unique_constraintss,d++ t\6 EG "$' 3D22FFK;=  \ "  655   79  3H)*c1,,Xl-CDJ#+,=#> "112FGO--h~.FGK"FJ#78H". ..h.+$&0;>(! 2)Bo. ~  % %k 25 38%77   +  K s Dc |jddrK|j|||g|}|r3t|dk(sJ|d}|j|d}|d}|d}|j |}|j |xs |j } ttjjjjtjjj|k(tjjj| k(jttj jj"jtj jj$|k(tj jj| k(} |j'|| |d j)} | "t+j,|r |d ||| S) rr5F)rr|r rrrrrrM)rrrrrrrr2r rr_rrQrrrrr{rrrOr!r) rIrHrr"r|rKsynonymsrAryrr{rps rLget_view_definitionz!OracleDialect.get_view_definitionOs 66+U 3))F)V*H8})))#A;,,Xi-@A!-0$\2 $$Y/,,  .d..  :'')).. / U$$&&00D8$$&&,,5Yz,,..445;;))++66$>))++11U:  % % vE& &(  :&&+16(!I;' 7@ IrOc |j|f||gtj|tjd|}|j |||S)r)r"rr include_allr )get_multi_check_constraintsr'rr&r)rIrHrr"rrKrs rLget_check_constraintsz#OracleDialect.get_check_constraintssX0t//  $//#   ##D*f==rO)r|rc j|||||fi|} tjd} ttj ||| fi|D]j} | ddk7r j | d} j | d} | d}| f}| C|s| j|rW|j| |dltjfdfd | DDS) rz..+?. IS NOT NULL$rxrrrzr)ryrc3BK|]}||vr|nfywrFrb)rr check_constraintsr%s rLrz.s:  //&c*     rc3DK|]}j|fywrFrrs rLrz.rr~) rrJr<rrr{rrr r)r)rIrHr"rr|rr rrKrnot_nullrArrzr table_checksrr%s` ` @@rLrz)OracleDialect.get_multi_check_constraintss# ,d++ t\6 EG ::34'-555   79  H)*c1,,Xl-CDJ"11(;L2MNO'(:; ,fj-ABL*8>>2B#C##,9IJ "%66   +  rOcttjjj}|j |||dj }|Dcgc]}|j|c}Scc}w)NFr)r2r all_db_linksr_rrrr)rIrHr|r{linkslinks rL _list_dblinkszOracleDialect._list_dblinkssgz..00889(( vE) ') 7<%%,=A%%& .Z $../ *99: $../   .[::xZ $../ #001 "//0 *99: $../ >$L<<H<H >[44l A AF > >"*[FFP E EN@ > >[55n &  & P > >[2 2 hZ $../ $../ )889    > K  K Z > >[0 0 dZ $../ $../ )889   * +  + Z  >>( C  C J-1>>  F  F P  00d?D>>" 7  7 r=rOr2ceZdZdZdZy)router_join_columnc||_yrF)r)rIrs rLrz_OuterJoinColumn.__init__s  rON)r}rr__visit_name__rrbrOrLrrs (NrOr)`r __future__r collectionsr dataclassesr functoolsrrrJr]r typesr r r rrrrrrrrrrrrrrrrrrr r!r"r$r#r$enginer%r&r'r(engine.reflectionr)r*r,r-r.r/r0r1r2r3rr4rdr5 sql.visitorsr6r7r8r9r:r;r<r=r>r?rrRr rBooleanrEDateTimeDaterBr=GenericTypeCompilerrDrr DDLCompilerrIdentifierPreparerrDefaultExecutionContextr&r9r2 ClauseElementrrbrOrLr,seN## ! %## ! 3.#-%?@Euw BHHJ  n x t MM;   D U   D   f  D U D U %ih 3  U!"(#$  " / 6d@55d@NxGX))xGv\,,\~9x::9(W<<"K=G**K=\:s((rO