codekingpro/portable-devtools
114k
1# dialects/oracle/cx_oracle.py
2# Copyright (C) 2005-2026 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""".. dialect:: oracle+cx_oracle
11 :name: cx-Oracle
12 :dbapi: cx_oracle
13 :connectstring: oracle+cx_oracle://user:pass@hostname:port[/dbname][?service_name=<service>[&key=value&key=value...]]
14 :url: https://oracle.github.io/python-cx_Oracle/
15
16Description
17-----------
18
19cx_Oracle was the original driver for Oracle Database. It was superseded by
20python-oracledb which should be used instead.
21
22DSN vs. Hostname connections
23-----------------------------
24
25cx_Oracle provides several methods of indicating the target database. The
26dialect translates from a series of different URL forms.
27
28Hostname Connections with Easy Connect Syntax
29^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
30
31Given a hostname, port and service name of the target database, for example
32from Oracle Database's Easy Connect syntax then connect in SQLAlchemy using the
33``service_name`` query string parameter::
34
35 engine = create_engine(
36 "oracle+cx_oracle://scott:tiger@hostname:port?service_name=myservice&encoding=UTF-8&nencoding=UTF-8"
37 )
38
39Note that the default driver value for encoding and nencoding was changed to
40“UTF-8” in cx_Oracle 8.0 so these parameters can be omitted when using that
41version, or later.
42
43To use a full Easy Connect string, pass it as the ``dsn`` key value in a
44:paramref:`_sa.create_engine.connect_args` dictionary::
45
46 import cx_Oracle
47
48 e = create_engine(
49 "oracle+cx_oracle://@",
50 connect_args={
51 "user": "scott",
52 "password": "tiger",
53 "dsn": "hostname:port/myservice?transport_connect_timeout=30&expire_time=60",
54 },
55 )
56
57Connections with tnsnames.ora or to Oracle Autonomous Database
58^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
59
60Alternatively, if no port, database name, or service name is provided, the
61dialect will use an Oracle Database DSN "connection string". This takes the
62"hostname" portion of the URL as the data source name. For example, if the
63``tnsnames.ora`` file contains a TNS Alias of ``myalias`` as below:
64
65.. sourcecode:: text
66
67 myalias =
68 (DESCRIPTION =
69 (ADDRESS = (PROTOCOL = TCP)(HOST = mymachine.example.com)(PORT = 1521))
70 (CONNECT_DATA =
71 (SERVER = DEDICATED)
72 (SERVICE_NAME = orclpdb1)
73 )
74 )
75
76The cx_Oracle dialect connects to this database service when ``myalias`` is the
77hostname portion of the URL, without specifying a port, database name or
78``service_name``::
79
80 engine = create_engine("oracle+cx_oracle://scott:tiger@myalias")
81
82Users of Oracle Autonomous Database should use this syntax. If the database is
83configured for mutural TLS ("mTLS"), then you must also configure the cloud
84wallet as shown in cx_Oracle documentation `Connecting to Autononmous Databases
85<https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#autonomousdb>`_.
86
87SID Connections
88^^^^^^^^^^^^^^^
89
90To use Oracle Database's obsolete System Identifier connection syntax, the SID
91can be passed in a "database name" portion of the URL::
92
93 engine = create_engine(
94 "oracle+cx_oracle://scott:tiger@hostname:port/dbname"
95 )
96
97Above, the DSN passed to cx_Oracle is created by ``cx_Oracle.makedsn()`` as
98follows::
99
100 >>> import cx_Oracle
101 >>> cx_Oracle.makedsn("hostname", 1521, sid="dbname")
102 '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=dbname)))'
103
104Note that although the SQLAlchemy syntax ``hostname:port/dbname`` looks like
105Oracle's Easy Connect syntax it is different. It uses a SID in place of the
106service name required by Easy Connect. The Easy Connect syntax does not
107support SIDs.
108
109Passing cx_Oracle connect arguments
110-----------------------------------
111
112Additional connection arguments can usually be passed via the URL query string;
113particular symbols like ``SYSDBA`` are intercepted and converted to the correct
114symbol::
115
116 e = create_engine(
117 "oracle+cx_oracle://user:pass@dsn?encoding=UTF-8&nencoding=UTF-8&mode=SYSDBA&events=true"
118 )
119
120.. versionchanged:: 1.3 the cx_Oracle dialect now accepts all argument names
121 within the URL string itself, to be passed to the cx_Oracle DBAPI. As
122 was the case earlier but not correctly documented, the
123 :paramref:`_sa.create_engine.connect_args` parameter also accepts all
124 cx_Oracle DBAPI connect arguments.
125
126To pass arguments directly to ``.connect()`` without using the query
127string, use the :paramref:`_sa.create_engine.connect_args` dictionary.
128Any cx_Oracle parameter value and/or constant may be passed, such as::
129
130 import cx_Oracle
131
132 e = create_engine(
133 "oracle+cx_oracle://user:pass@dsn",
134 connect_args={
135 "encoding": "UTF-8",
136 "nencoding": "UTF-8",
137 "mode": cx_Oracle.SYSDBA,
138 "events": True,
139 },
140 )
141
142Note that the default driver value for ``encoding`` and ``nencoding`` was
143changed to "UTF-8" in cx_Oracle 8.0 so these parameters can be omitted when
144using that version, or later.
145
146Options consumed by the SQLAlchemy cx_Oracle dialect outside of the driver
147--------------------------------------------------------------------------
148
149There are also options that are consumed by the SQLAlchemy cx_oracle dialect
150itself. These options are always passed directly to :func:`_sa.create_engine`
151, such as::
152
153 e = create_engine(
154 "oracle+cx_oracle://user:pass@dsn", coerce_to_decimal=False
155 )
156
157The parameters accepted by the cx_oracle dialect are as follows:
158
159* ``arraysize`` - set the cx_oracle.arraysize value on cursors; defaults
160 to ``None``, indicating that the driver default should be used (typically
161 the value is 100). This setting controls how many rows are buffered when
162 fetching rows, and can have a significant effect on performance when
163 modified.
164
165 .. versionchanged:: 2.0.26 - changed the default value from 50 to None,
166 to use the default value of the driver itself.
167
168* ``auto_convert_lobs`` - defaults to True; See :ref:`cx_oracle_lob`.
169
170* ``coerce_to_decimal`` - see :ref:`cx_oracle_numeric` for detail.
171
172* ``encoding_errors`` - see :ref:`cx_oracle_unicode_encoding_errors` for detail.
173
174.. _cx_oracle_sessionpool:
175
176Using cx_Oracle SessionPool
177---------------------------
178
179The cx_Oracle driver provides its own connection pool implementation that may
180be used in place of SQLAlchemy's pooling functionality. The driver pool
181supports Oracle Database features such dead connection detection, connection
182draining for planned database downtime, support for Oracle Application
183Continuity and Transparent Application Continuity, and gives support for
184Database Resident Connection Pooling (DRCP).
185
186Using the driver pool can be achieved by using the
187:paramref:`_sa.create_engine.creator` parameter to provide a function that
188returns a new connection, along with setting
189:paramref:`_sa.create_engine.pool_class` to ``NullPool`` to disable
190SQLAlchemy's pooling::
191
192 import cx_Oracle
193 from sqlalchemy import create_engine
194 from sqlalchemy.pool import NullPool
195
196 pool = cx_Oracle.SessionPool(
197 user="scott",
198 password="tiger",
199 dsn="orclpdb",
200 min=1,
201 max=4,
202 increment=1,
203 threaded=True,
204 encoding="UTF-8",
205 nencoding="UTF-8",
206 )
207
208 engine = create_engine(
209 "oracle+cx_oracle://", creator=pool.acquire, poolclass=NullPool
210 )
211
212The above engine may then be used normally where cx_Oracle's pool handles
213connection pooling::
214
215 with engine.connect() as conn:
216 print(conn.scalar("select 1 from dual"))
217
218As well as providing a scalable solution for multi-user applications, the
219cx_Oracle session pool supports some Oracle features such as DRCP and
220`Application Continuity
221<https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#application-continuity-ac>`_.
222
223Note that the pool creation parameters ``threaded``, ``encoding`` and
224``nencoding`` were deprecated in later cx_Oracle releases.
225
226Using Oracle Database Resident Connection Pooling (DRCP)
227--------------------------------------------------------
228
229When using Oracle Database's DRCP, the best practice is to pass a connection
230class and "purity" when acquiring a connection from the SessionPool. Refer to
231the `cx_Oracle DRCP documentation
232<https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp>`_.
233
234This can be achieved by wrapping ``pool.acquire()``::
235
236 import cx_Oracle
237 from sqlalchemy import create_engine
238 from sqlalchemy.pool import NullPool
239
240 pool = cx_Oracle.SessionPool(
241 user="scott",
242 password="tiger",
243 dsn="orclpdb",
244 min=2,
245 max=5,
246 increment=1,
247 threaded=True,
248 encoding="UTF-8",
249 nencoding="UTF-8",
250 )
251
252
253 def creator():
254 return pool.acquire(
255 cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF
256 )
257
258
259 engine = create_engine(
260 "oracle+cx_oracle://", creator=creator, poolclass=NullPool
261 )
262
263The above engine may then be used normally where cx_Oracle handles session
264pooling and Oracle Database additionally uses DRCP::
265
266 with engine.connect() as conn:
267 print(conn.scalar("select 1 from dual"))
268
269.. _cx_oracle_unicode:
270
271Unicode
272-------
273
274As is the case for all DBAPIs under Python 3, all strings are inherently
275Unicode strings. In all cases however, the driver requires an explicit
276encoding configuration.
277
278Ensuring the Correct Client Encoding
279^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
280
281The long accepted standard for establishing client encoding for nearly all
282Oracle Database related software is via the `NLS_LANG
283<https://www.oracle.com/database/technologies/faq-nls-lang.html>`_ environment
284variable. Older versions of cx_Oracle use this environment variable as the
285source of its encoding configuration. The format of this variable is
286Territory_Country.CharacterSet; a typical value would be
287``AMERICAN_AMERICA.AL32UTF8``. cx_Oracle version 8 and later use the character
288set "UTF-8" by default, and ignore the character set component of NLS_LANG.
289
290The cx_Oracle driver also supported a programmatic alternative which is to pass
291the ``encoding`` and ``nencoding`` parameters directly to its ``.connect()``
292function. These can be present in the URL as follows::
293
294 engine = create_engine(
295 "oracle+cx_oracle://scott:tiger@tnsalias?encoding=UTF-8&nencoding=UTF-8"
296 )
297
298For the meaning of the ``encoding`` and ``nencoding`` parameters, please
299consult
300`Characters Sets and National Language Support (NLS) <https://cx-oracle.readthedocs.io/en/latest/user_guide/globalization.html#globalization>`_.
301
302.. seealso::
303
304 `Characters Sets and National Language Support (NLS) <https://cx-oracle.readthedocs.io/en/latest/user_guide/globalization.html#globalization>`_
305 - in the cx_Oracle documentation.
306
307
308Unicode-specific Column datatypes
309^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
310
311The Core expression language handles unicode data by use of the
312:class:`.Unicode` and :class:`.UnicodeText` datatypes. These types correspond
313to the VARCHAR2 and CLOB Oracle Database datatypes by default. When using
314these datatypes with Unicode data, it is expected that the database is
315configured with a Unicode-aware character set, as well as that the ``NLS_LANG``
316environment variable is set appropriately (this applies to older versions of
317cx_Oracle), so that the VARCHAR2 and CLOB datatypes can accommodate the data.
318
319In the case that Oracle Database is not configured with a Unicode character
320set, the two options are to use the :class:`_types.NCHAR` and
321:class:`_oracle.NCLOB` datatypes explicitly, or to pass the flag
322``use_nchar_for_unicode=True`` to :func:`_sa.create_engine`, which will cause
323the SQLAlchemy dialect to use NCHAR/NCLOB for the :class:`.Unicode` /
324:class:`.UnicodeText` datatypes instead of VARCHAR/CLOB.
325
326.. versionchanged:: 1.3 The :class:`.Unicode` and :class:`.UnicodeText`
327 datatypes now correspond to the ``VARCHAR2`` and ``CLOB`` Oracle Database
328 datatypes unless the ``use_nchar_for_unicode=True`` is passed to the dialect
329 when :func:`_sa.create_engine` is called.
330
331
332.. _cx_oracle_unicode_encoding_errors:
333
334Encoding Errors
335^^^^^^^^^^^^^^^
336
337For the unusual case that data in Oracle Database is present with a broken
338encoding, the dialect accepts a parameter ``encoding_errors`` which will be
339passed to Unicode decoding functions in order to affect how decoding errors are
340handled. The value is ultimately consumed by the Python `decode
341<https://docs.python.org/3/library/stdtypes.html#bytes.decode>`_ function, and
342is passed both via cx_Oracle's ``encodingErrors`` parameter consumed by
343``Cursor.var()``, as well as SQLAlchemy's own decoding function, as the
344cx_Oracle dialect makes use of both under different circumstances.
345
346.. versionadded:: 1.3.11
347
348
349.. _cx_oracle_setinputsizes:
350
351Fine grained control over cx_Oracle data binding performance with setinputsizes
352-------------------------------------------------------------------------------
353
354The cx_Oracle DBAPI has a deep and fundamental reliance upon the usage of the
355DBAPI ``setinputsizes()`` call. The purpose of this call is to establish the
356datatypes that are bound to a SQL statement for Python values being passed as
357parameters. While virtually no other DBAPI assigns any use to the
358``setinputsizes()`` call, the cx_Oracle DBAPI relies upon it heavily in its
359interactions with the Oracle Database client interface, and in some scenarios
360it is not possible for SQLAlchemy to know exactly how data should be bound, as
361some settings can cause profoundly different performance characteristics, while
362altering the type coercion behavior at the same time.
363
364Users of the cx_Oracle dialect are **strongly encouraged** to read through
365cx_Oracle's list of built-in datatype symbols at
366https://cx-oracle.readthedocs.io/en/latest/api_manual/module.html#database-types.
367Note that in some cases, significant performance degradation can occur when
368using these types vs. not, in particular when specifying ``cx_Oracle.CLOB``.
369
370On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event can
371be used both for runtime visibility (e.g. logging) of the setinputsizes step as
372well as to fully control how ``setinputsizes()`` is used on a per-statement
373basis.
374
375.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes`
376
377
378Example 1 - logging all setinputsizes calls
379^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
380
381The following example illustrates how to log the intermediary values from a
382SQLAlchemy perspective before they are converted to the raw ``setinputsizes()``
383parameter dictionary. The keys of the dictionary are :class:`.BindParameter`
384objects which have a ``.key`` and a ``.type`` attribute::
385
386 from sqlalchemy import create_engine, event
387
388 engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
389
390
391 @event.listens_for(engine, "do_setinputsizes")
392 def _log_setinputsizes(inputsizes, cursor, statement, parameters, context):
393 for bindparam, dbapitype in inputsizes.items():
394 log.info(
395 "Bound parameter name: %s SQLAlchemy type: %r DBAPI object: %s",
396 bindparam.key,
397 bindparam.type,
398 dbapitype,
399 )
400
401Example 2 - remove all bindings to CLOB
402^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
403
404The ``CLOB`` datatype in cx_Oracle incurs a significant performance overhead,
405however is set by default for the ``Text`` type within the SQLAlchemy 1.2
406series. This setting can be modified as follows::
407
408 from sqlalchemy import create_engine, event
409 from cx_Oracle import CLOB
410
411 engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe")
412
413
414 @event.listens_for(engine, "do_setinputsizes")
415 def _remove_clob(inputsizes, cursor, statement, parameters, context):
416 for bindparam, dbapitype in list(inputsizes.items()):
417 if dbapitype is CLOB:
418 del inputsizes[bindparam]
419
420.. _cx_oracle_lob:
421
422LOB Datatypes
423--------------
424
425LOB datatypes refer to the "large object" datatypes such as CLOB, NCLOB and
426BLOB. Modern versions of cx_Oracle is optimized for these datatypes to be
427delivered as a single buffer. As such, SQLAlchemy makes use of these newer type
428handlers by default.
429
430To disable the use of newer type handlers and deliver LOB objects as classic
431buffered objects with a ``read()`` method, the parameter
432``auto_convert_lobs=False`` may be passed to :func:`_sa.create_engine`,
433which takes place only engine-wide.
434
435.. _cx_oracle_returning:
436
437RETURNING Support
438-----------------
439
440The cx_Oracle dialect implements RETURNING using OUT parameters.
441The dialect supports RETURNING fully.
442
443Two Phase Transactions Not Supported
444------------------------------------
445
446Two phase transactions are **not supported** under cx_Oracle due to poor driver
447support. The newer :ref:`oracledb` dialect however **does** support two phase
448transactions.
449
450.. _cx_oracle_numeric:
451
452Precision Numerics
453------------------
454
455SQLAlchemy's numeric types can handle receiving and returning values as Python
456``Decimal`` objects or float objects. When a :class:`.Numeric` object, or a
457subclass such as :class:`.Float`, :class:`_oracle.DOUBLE_PRECISION` etc. is in
458use, the :paramref:`.Numeric.asdecimal` flag determines if values should be
459coerced to ``Decimal`` upon return, or returned as float objects. To make
460matters more complicated under Oracle Database, the ``NUMBER`` type can also
461represent integer values if the "scale" is zero, so the Oracle
462Database-specific :class:`_oracle.NUMBER` type takes this into account as well.
463
464The cx_Oracle dialect makes extensive use of connection- and cursor-level
465"outputtypehandler" callables in order to coerce numeric values as requested.
466These callables are specific to the specific flavor of :class:`.Numeric` in
467use, as well as if no SQLAlchemy typing objects are present. There are
468observed scenarios where Oracle Database may send incomplete or ambiguous
469information about the numeric types being returned, such as a query where the
470numeric types are buried under multiple levels of subquery. The type handlers
471do their best to make the right decision in all cases, deferring to the
472underlying cx_Oracle DBAPI for all those cases where the driver can make the
473best decision.
474
475When no typing objects are present, as when executing plain SQL strings, a
476default "outputtypehandler" is present which will generally return numeric
477values which specify precision and scale as Python ``Decimal`` objects. To
478disable this coercion to decimal for performance reasons, pass the flag
479``coerce_to_decimal=False`` to :func:`_sa.create_engine`::
480
481 engine = create_engine("oracle+cx_oracle://dsn", coerce_to_decimal=False)
482
483The ``coerce_to_decimal`` flag only impacts the results of plain string
484SQL statements that are not otherwise associated with a :class:`.Numeric`
485SQLAlchemy type (or a subclass of such).
486
487.. versionchanged:: 1.2 The numeric handling system for cx_Oracle has been
488 reworked to take advantage of newer cx_Oracle features as well
489 as better integration of outputtypehandlers.
490
491""" # noqa
492from __future__ import annotations
493
494import decimal
495import random
496import re
497
498from . import base as oracle
499from .base import OracleCompiler
500from .base import OracleDialect
501from .base import OracleExecutionContext
502from .types import _OracleDateLiteralRender
503from ... import exc
504from ... import util
505from ...engine import cursor as _cursor
506from ...engine import interfaces
507from ...engine import processors
508from ...sql import sqltypes
509from ...sql._typing import is_sql_compiler
510
511# source:
512# https://github.com/oracle/python-cx_Oracle/issues/596#issuecomment-999243649
513_CX_ORACLE_MAGIC_LOB_SIZE = 131072
514
515
516class _OracleInteger(sqltypes.Integer):
517 def get_dbapi_type(self, dbapi):
518 # see https://github.com/oracle/python-cx_Oracle/issues/
519 # 208#issuecomment-409715955
520 return int
521
522 def _cx_oracle_var(self, dialect, cursor, arraysize=None):
523 cx_Oracle = dialect.dbapi
524 return cursor.var(
525 cx_Oracle.STRING,
526 255,
527 arraysize=arraysize if arraysize is not None else cursor.arraysize,
528 outconverter=int,
529 )
530
531 def _cx_oracle_outputtypehandler(self, dialect):
532 def handler(cursor, name, default_type, size, precision, scale):
533 return self._cx_oracle_var(dialect, cursor)
534
535 return handler
536
537
538class _OracleNumeric(sqltypes.Numeric):
539 is_number = False
540
541 def bind_processor(self, dialect):
542 if self.scale == 0:
543 return None
544 elif self.asdecimal:
545 processor = processors.to_decimal_processor_factory(
546 decimal.Decimal, self._effective_decimal_return_scale
547 )
548
549 def process(value):
550 if isinstance(value, (int, float)):
551 return processor(value)
552 elif value is not None and value.is_infinite():
553 return float(value)
554 else:
555 return value
556
557 return process
558 else:
559 return processors.to_float
560
561 def result_processor(self, dialect, coltype):
562 return None
563
564 def _cx_oracle_outputtypehandler(self, dialect):
565 cx_Oracle = dialect.dbapi
566
567 def handler(cursor, name, default_type, size, precision, scale):
568 outconverter = None
569
570 if precision:
571 if self.asdecimal:
572 if default_type == cx_Oracle.NATIVE_FLOAT:
573 # receiving float and doing Decimal after the fact
574 # allows for float("inf") to be handled
575 type_ = default_type
576 outconverter = decimal.Decimal
577 else:
578 type_ = decimal.Decimal
579 else:
580 if self.is_number and scale == 0:
581 # integer. cx_Oracle is observed to handle the widest
582 # variety of ints when no directives are passed,
583 # from 5.2 to 7.0. See [ticket:4457]
584 return None
585 else:
586 type_ = cx_Oracle.NATIVE_FLOAT
587
588 else:
589 if self.asdecimal:
590 if default_type == cx_Oracle.NATIVE_FLOAT:
591 type_ = default_type
592 outconverter = decimal.Decimal
593 else:
594 type_ = decimal.Decimal
595 else:
596 if self.is_number and scale == 0:
597 # integer. cx_Oracle is observed to handle the widest
598 # variety of ints when no directives are passed,
599 # from 5.2 to 7.0. See [ticket:4457]
600 return None
601 else:
602 type_ = cx_Oracle.NATIVE_FLOAT
603
604 return cursor.var(
605 type_,
606 255,
607 arraysize=cursor.arraysize,
608 outconverter=outconverter,
609 )
610
611 return handler
612
613
614class _OracleUUID(sqltypes.Uuid):
615 def get_dbapi_type(self, dbapi):
616 return dbapi.STRING
617
618
619class _OracleBinaryFloat(_OracleNumeric):
620 def get_dbapi_type(self, dbapi):
621 return dbapi.NATIVE_FLOAT
622
623
624class _OracleBINARY_FLOAT(_OracleBinaryFloat, oracle.BINARY_FLOAT):
625 pass
626
627
628class _OracleBINARY_DOUBLE(_OracleBinaryFloat, oracle.BINARY_DOUBLE):
629 pass
630
631
632class _OracleNUMBER(_OracleNumeric):
633 is_number = True
634
635
636class _CXOracleDate(oracle._OracleDate):
637 def bind_processor(self, dialect):
638 return None
639
640 def result_processor(self, dialect, coltype):
641 def process(value):
642 if value is not None:
643 return value.date()
644 else:
645 return value
646
647 return process
648
649
650class _CXOracleTIMESTAMP(_OracleDateLiteralRender, sqltypes.TIMESTAMP):
651 def literal_processor(self, dialect):
652 return self._literal_processor_datetime(dialect)
653
654
655class _LOBDataType:
656 pass
657
658
659# TODO: the names used across CHAR / VARCHAR / NCHAR / NVARCHAR
660# here are inconsistent and not very good
661class _OracleChar(sqltypes.CHAR):
662 def get_dbapi_type(self, dbapi):
663 return dbapi.FIXED_CHAR
664
665
666class _OracleNChar(sqltypes.NCHAR):
667 def get_dbapi_type(self, dbapi):
668 return dbapi.FIXED_NCHAR
669
670
671class _OracleUnicodeStringNCHAR(oracle.NVARCHAR2):
672 def get_dbapi_type(self, dbapi):
673 return dbapi.NCHAR
674
675
676class _OracleUnicodeStringCHAR(sqltypes.Unicode):
677 def get_dbapi_type(self, dbapi):
678 return dbapi.LONG_STRING
679
680
681class _OracleUnicodeTextNCLOB(_LOBDataType, oracle.NCLOB):
682 def get_dbapi_type(self, dbapi):
683 # previously, this was dbapi.NCLOB.
684 # DB_TYPE_NVARCHAR will instead be passed to setinputsizes()
685 # when this datatype is used.
686 return dbapi.DB_TYPE_NVARCHAR
687
688
689class _OracleUnicodeTextCLOB(_LOBDataType, sqltypes.UnicodeText):
690 def get_dbapi_type(self, dbapi):
691 # previously, this was dbapi.CLOB.
692 # DB_TYPE_NVARCHAR will instead be passed to setinputsizes()
693 # when this datatype is used.
694 return dbapi.DB_TYPE_NVARCHAR
695
696
697class _OracleText(_LOBDataType, sqltypes.Text):
698 def get_dbapi_type(self, dbapi):
699 # previously, this was dbapi.CLOB.
700 # DB_TYPE_NVARCHAR will instead be passed to setinputsizes()
701 # when this datatype is used.
702 return dbapi.DB_TYPE_NVARCHAR
703
704
705class _OracleLong(_LOBDataType, oracle.LONG):
706 def get_dbapi_type(self, dbapi):
707 return dbapi.LONG_STRING
708
709
710class _OracleString(sqltypes.String):
711 pass
712
713
714class _OracleEnum(sqltypes.Enum):
715 def bind_processor(self, dialect):
716 enum_proc = sqltypes.Enum.bind_processor(self, dialect)
717
718 def process(value):
719 raw_str = enum_proc(value)
720 return raw_str
721
722 return process
723
724
725class _OracleBinary(_LOBDataType, sqltypes.LargeBinary):
726 def get_dbapi_type(self, dbapi):
727 # previously, this was dbapi.BLOB.
728 # DB_TYPE_RAW will instead be passed to setinputsizes()
729 # when this datatype is used.
730 return dbapi.DB_TYPE_RAW
731
732 def bind_processor(self, dialect):
733 return None
734
735 def result_processor(self, dialect, coltype):
736 if not dialect.auto_convert_lobs:
737 return None
738 else:
739 return super().result_processor(dialect, coltype)
740
741
742class _OracleInterval(oracle.INTERVAL):
743 def get_dbapi_type(self, dbapi):
744 return dbapi.INTERVAL
745
746
747class _OracleRaw(oracle.RAW):
748 pass
749
750
751class _OracleRowid(oracle.ROWID):
752 def get_dbapi_type(self, dbapi):
753 return dbapi.ROWID
754
755
756class OracleCompiler_cx_oracle(OracleCompiler):
757 _oracle_cx_sql_compiler = True
758
759 _oracle_returning = False
760
761 # Oracle bind names can't start with digits or underscores.
762 # currently we rely upon Oracle-specific quoting of bind names in most
763 # cases. however for expanding params, the escape chars are used.
764 # see #8708
765 bindname_escape_characters = util.immutabledict(
766 {
767 "%": "P",
768 "(": "A",
769 ")": "Z",
770 ":": "C",
771 ".": "C",
772 "[": "C",
773 "]": "C",
774 " ": "C",
775 "\\": "C",
776 "/": "C",
777 "?": "C",
778 }
779 )
780
781 def bindparam_string(self, name, **kw):
782 quote = getattr(name, "quote", None)
783 if (
784 quote is True
785 or quote is not False
786 and self.preparer._bindparam_requires_quotes(name)
787 # bind param quoting for Oracle doesn't work with post_compile
788 # params. For those, the default bindparam_string will escape
789 # special chars, and the appending of a number "_1" etc. will
790 # take care of reserved words
791 and not kw.get("post_compile", False)
792 ):
793 # interesting to note about expanding parameters - since the
794 # new parameters take the form <paramname>_<int>, at least if
795 # they are originally formed from reserved words, they no longer
796 # need quoting :). names that include illegal characters
797 # won't work however.
798 quoted_name = '"%s"' % name
799 kw["escaped_from"] = name
800 name = quoted_name
801 return OracleCompiler.bindparam_string(self, name, **kw)
802
803 # TODO: we could likely do away with quoting altogether for
804 # Oracle parameters and use the custom escaping here
805 escaped_from = kw.get("escaped_from", None)
806 if not escaped_from:
807 if self._bind_translate_re.search(name):
808 # not quite the translate use case as we want to
809 # also get a quick boolean if we even found
810 # unusual characters in the name
811 new_name = self._bind_translate_re.sub(
812 lambda m: self._bind_translate_chars[m.group(0)],
813 name,
814 )
815 if new_name[0].isdigit() or new_name[0] == "_":
816 new_name = "D" + new_name
817 kw["escaped_from"] = name
818 name = new_name
819 elif name[0].isdigit() or name[0] == "_":
820 new_name = "D" + name
821 kw["escaped_from"] = name
822 name = new_name
823
824 return OracleCompiler.bindparam_string(self, name, **kw)
825
826
827class OracleExecutionContext_cx_oracle(OracleExecutionContext):
828 out_parameters = None
829
830 def _generate_out_parameter_vars(self):
831 # check for has_out_parameters or RETURNING, create cx_Oracle.var
832 # objects if so
833 if self.compiled.has_out_parameters or self.compiled._oracle_returning:
834 out_parameters = self.out_parameters
835 assert out_parameters is not None
836
837 len_params = len(self.parameters)
838
839 quoted_bind_names = self.compiled.escaped_bind_names
840 for bindparam in self.compiled.binds.values():
841 if bindparam.isoutparam:
842 name = self.compiled.bind_names[bindparam]
843 type_impl = bindparam.type.dialect_impl(self.dialect)
844
845 if hasattr(type_impl, "_cx_oracle_var"):
846 out_parameters[name] = type_impl._cx_oracle_var(
847 self.dialect, self.cursor, arraysize=len_params
848 )
849 else:
850 dbtype = type_impl.get_dbapi_type(self.dialect.dbapi)
851
852 cx_Oracle = self.dialect.dbapi
853
854 assert cx_Oracle is not None
855
856 if dbtype is None:
857 raise exc.InvalidRequestError(
858 "Cannot create out parameter for "
859 "parameter "
860 "%r - its type %r is not supported by"
861 " cx_oracle" % (bindparam.key, bindparam.type)
862 )
863
864 # note this is an OUT parameter. Using
865 # non-LOB datavalues with large unicode-holding
866 # values causes the failure (both cx_Oracle and
867 # oracledb):
868 # ORA-22835: Buffer too small for CLOB to CHAR or
869 # BLOB to RAW conversion (actual: 16507,
870 # maximum: 4000)
871 # [SQL: INSERT INTO long_text (x, y, z) VALUES
872 # (:x, :y, :z) RETURNING long_text.x, long_text.y,
873 # long_text.z INTO :ret_0, :ret_1, :ret_2]
874 # so even for DB_TYPE_NVARCHAR we convert to a LOB
875
876 if isinstance(type_impl, _LOBDataType):
877 if dbtype == cx_Oracle.DB_TYPE_NVARCHAR:
878 dbtype = cx_Oracle.NCLOB
879 elif dbtype == cx_Oracle.DB_TYPE_RAW:
880 dbtype = cx_Oracle.BLOB
881 # other LOB types go in directly
882
883 out_parameters[name] = self.cursor.var(
884 dbtype,
885 # this is fine also in oracledb_async since
886 # the driver will await the read coroutine
887 outconverter=lambda value: value.read(),
888 arraysize=len_params,
889 )
890 elif (
891 isinstance(type_impl, _OracleNumeric)
892 and type_impl.asdecimal
893 ):
894 out_parameters[name] = self.cursor.var(
895 decimal.Decimal,
896 arraysize=len_params,
897 )
898
899 else:
900 out_parameters[name] = self.cursor.var(
901 dbtype, arraysize=len_params
902 )
903
904 for param in self.parameters:
905 param[quoted_bind_names.get(name, name)] = (
906 out_parameters[name]
907 )
908
909 def _generate_cursor_outputtype_handler(self):
910 output_handlers = {}
911
912 for keyname, name, objects, type_ in self.compiled._result_columns:
913 handler = type_._cached_custom_processor(
914 self.dialect,
915 "cx_oracle_outputtypehandler",
916 self._get_cx_oracle_type_handler,
917 )
918
919 if handler:
920 denormalized_name = self.dialect.denormalize_name(keyname)
921 output_handlers[denormalized_name] = handler
922
923 if output_handlers:
924 default_handler = self._dbapi_connection.outputtypehandler
925
926 def output_type_handler(
927 cursor, name, default_type, size, precision, scale
928 ):
929 if name in output_handlers:
930 return output_handlers[name](
931 cursor, name, default_type, size, precision, scale
932 )
933 else:
934 return default_handler(
935 cursor, name, default_type, size, precision, scale
936 )
937
938 self.cursor.outputtypehandler = output_type_handler
939
940 def _get_cx_oracle_type_handler(self, impl):
941 if hasattr(impl, "_cx_oracle_outputtypehandler"):
942 return impl._cx_oracle_outputtypehandler(self.dialect)
943 else:
944 return None
945
946 def pre_exec(self):
947 super().pre_exec()
948 if not getattr(self.compiled, "_oracle_cx_sql_compiler", False):
949 return
950
951 self.out_parameters = {}
952
953 self._generate_out_parameter_vars()
954
955 self._generate_cursor_outputtype_handler()
956
957 def post_exec(self):
958 if (
959 self.compiled
960 and is_sql_compiler(self.compiled)
961 and self.compiled._oracle_returning
962 ):
963 initial_buffer = self.fetchall_for_returning(
964 self.cursor, _internal=True
965 )
966
967 fetch_strategy = _cursor.FullyBufferedCursorFetchStrategy(
968 self.cursor,
969 [
970 (entry.keyname, None)
971 for entry in self.compiled._result_columns
972 ],
973 initial_buffer=initial_buffer,
974 )
975
976 self.cursor_fetch_strategy = fetch_strategy
977
978 def create_cursor(self):
979 c = self._dbapi_connection.cursor()
980 if self.dialect.arraysize:
981 c.arraysize = self.dialect.arraysize
982
983 return c
984
985 def fetchall_for_returning(self, cursor, *, _internal=False):
986 compiled = self.compiled
987 if (
988 not _internal
989 and compiled is None
990 or not is_sql_compiler(compiled)
991 or not compiled._oracle_returning
992 ):
993 raise NotImplementedError(
994 "execution context was not prepared for Oracle RETURNING"
995 )
996
997 # create a fake cursor result from the out parameters. unlike
998 # get_out_parameter_values(), the result-row handlers here will be
999 # applied at the Result level
1000
1001 numcols = len(self.out_parameters)
1002
1003 # [stmt_result for stmt_result in outparam.values] == each
1004 # statement in executemany
1005 # [val for val in stmt_result] == each row for a particular
1006 # statement
1007 return list(
1008 zip(
1009 *[
1010 [
1011 val
1012 for stmt_result in self.out_parameters[
1013 f"ret_{j}"
1014 ].values
1015 for val in (stmt_result or ())
1016 ]
1017 for j in range(numcols)
1018 ]
1019 )
1020 )
1021
1022 def get_out_parameter_values(self, out_param_names):
1023 # this method should not be called when the compiler has
1024 # RETURNING as we've turned the has_out_parameters flag set to
1025 # False.
1026 assert not self.compiled.returning
1027
1028 return [
1029 self.dialect._paramval(self.out_parameters[name])
1030 for name in out_param_names
1031 ]
1032
1033
1034class OracleDialect_cx_oracle(OracleDialect):
1035 supports_statement_cache = True
1036 execution_ctx_cls = OracleExecutionContext_cx_oracle
1037 statement_compiler = OracleCompiler_cx_oracle
1038
1039 supports_sane_rowcount = True
1040 supports_sane_multi_rowcount = True
1041
1042 insert_executemany_returning = True
1043 insert_executemany_returning_sort_by_parameter_order = True
1044 update_executemany_returning = True
1045 delete_executemany_returning = True
1046
1047 bind_typing = interfaces.BindTyping.SETINPUTSIZES
1048
1049 driver = "cx_oracle"
1050
1051 colspecs = util.update_copy(
1052 OracleDialect.colspecs,
1053 {
1054 sqltypes.TIMESTAMP: _CXOracleTIMESTAMP,
1055 sqltypes.Numeric: _OracleNumeric,
1056 sqltypes.Float: _OracleNumeric,
1057 oracle.BINARY_FLOAT: _OracleBINARY_FLOAT,
1058 oracle.BINARY_DOUBLE: _OracleBINARY_DOUBLE,
1059 sqltypes.Integer: _OracleInteger,
1060 oracle.NUMBER: _OracleNUMBER,
1061 sqltypes.Date: _CXOracleDate,
1062 sqltypes.LargeBinary: _OracleBinary,
1063 sqltypes.Boolean: oracle._OracleBoolean,
1064 sqltypes.Interval: _OracleInterval,
1065 oracle.INTERVAL: _OracleInterval,
1066 sqltypes.Text: _OracleText,
1067 sqltypes.String: _OracleString,
1068 sqltypes.UnicodeText: _OracleUnicodeTextCLOB,
1069 sqltypes.CHAR: _OracleChar,
1070 sqltypes.NCHAR: _OracleNChar,
1071 sqltypes.Enum: _OracleEnum,
1072 oracle.LONG: _OracleLong,
1073 oracle.RAW: _OracleRaw,
1074 sqltypes.Unicode: _OracleUnicodeStringCHAR,
1075 sqltypes.NVARCHAR: _OracleUnicodeStringNCHAR,
1076 sqltypes.Uuid: _OracleUUID,
1077 oracle.NCLOB: _OracleUnicodeTextNCLOB,
1078 oracle.ROWID: _OracleRowid,
1079 },
1080 )
1081
1082 execute_sequence_format = list
1083
1084 _cx_oracle_threaded = None
1085
1086 _cursor_var_unicode_kwargs = util.immutabledict()
1087
1088 @util.deprecated_params(
1089 threaded=(
1090 "1.3",
1091 "The 'threaded' parameter to the cx_oracle/oracledb dialect "
1092 "is deprecated as a dialect-level argument, and will be removed "
1093 "in a future release. As of version 1.3, it defaults to False "
1094 "rather than True. The 'threaded' option can be passed to "
1095 "cx_Oracle directly in the URL query string passed to "
1096 ":func:`_sa.create_engine`.",
1097 )
1098 )
1099 def __init__(
1100 self,
1101 auto_convert_lobs=True,
1102 coerce_to_decimal=True,
1103 arraysize=None,
1104 encoding_errors=None,
1105 threaded=None,
1106 **kwargs,
1107 ):
1108 OracleDialect.__init__(self, **kwargs)
1109 self.arraysize = arraysize
1110 self.encoding_errors = encoding_errors
1111 if encoding_errors:
1112 self._cursor_var_unicode_kwargs = {
1113 "encodingErrors": encoding_errors
1114 }
1115 if threaded is not None:
1116 self._cx_oracle_threaded = threaded
1117 self.auto_convert_lobs = auto_convert_lobs
1118 self.coerce_to_decimal = coerce_to_decimal
1119 if self._use_nchar_for_unicode:
1120 self.colspecs = self.colspecs.copy()
1121 self.colspecs[sqltypes.Unicode] = _OracleUnicodeStringNCHAR
1122 self.colspecs[sqltypes.UnicodeText] = _OracleUnicodeTextNCLOB
1123
1124 dbapi_module = self.dbapi
1125 self._load_version(dbapi_module)
1126
1127 if dbapi_module is not None:
1128 # these constants will first be seen in SQLAlchemy datatypes
1129 # coming from the get_dbapi_type() method. We then
1130 # will place the following types into setinputsizes() calls
1131 # on each statement. Oracle constants that are not in this
1132 # list will not be put into setinputsizes().
1133 self.include_set_input_sizes = {
1134 dbapi_module.DATETIME,
1135 dbapi_module.DB_TYPE_NVARCHAR, # used for CLOB, NCLOB
1136 dbapi_module.DB_TYPE_RAW, # used for BLOB
1137 dbapi_module.NCLOB, # not currently used except for OUT param
1138 dbapi_module.CLOB, # not currently used except for OUT param
1139 dbapi_module.LOB, # not currently used
1140 dbapi_module.BLOB, # not currently used except for OUT param
1141 dbapi_module.NCHAR,
1142 dbapi_module.FIXED_NCHAR,
1143 dbapi_module.FIXED_CHAR,
1144 dbapi_module.TIMESTAMP,
1145 int, # _OracleInteger,
1146 # _OracleBINARY_FLOAT, _OracleBINARY_DOUBLE,
1147 dbapi_module.NATIVE_FLOAT,
1148 }
1149
1150 self._paramval = lambda value: value.getvalue()
1151
1152 def _load_version(self, dbapi_module):
1153 version = (0, 0, 0)
1154 if dbapi_module is not None:
1155 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", dbapi_module.version)
1156 if m:
1157 version = tuple(
1158 int(x) for x in m.group(1, 2, 3) if x is not None
1159 )
1160 self.cx_oracle_ver = version
1161 if self.cx_oracle_ver < (8,) and self.cx_oracle_ver > (0, 0, 0):
1162 raise exc.InvalidRequestError(
1163 "cx_Oracle version 8 and above are supported"
1164 )
1165
1166 @classmethod
1167 def import_dbapi(cls):
1168 import cx_Oracle
1169
1170 return cx_Oracle
1171
1172 def initialize(self, connection):
1173 super().initialize(connection)
1174 self._detect_decimal_char(connection)
1175
1176 def get_isolation_level(self, dbapi_connection):
1177 # sources:
1178
1179 # general idea of transaction id, have to start one, etc.
1180 # https://stackoverflow.com/questions/10711204/how-to-check-isoloation-level
1181
1182 # how to decode xid cols from v$transaction to match
1183 # https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532779900346079444
1184
1185 # Oracle tuple comparison without using IN:
1186 # https://www.sql-workbench.eu/comparison/tuple_comparison.html
1187
1188 with dbapi_connection.cursor() as cursor:
1189 # this is the only way to ensure a transaction is started without
1190 # actually running DML. There's no way to see the configured
1191 # isolation level without getting it from v$transaction which
1192 # means transaction has to be started.
1193 outval = cursor.var(str)
1194 cursor.execute(
1195 """
1196 begin
1197 :trans_id := dbms_transaction.local_transaction_id( TRUE );
1198 end;
1199 """,
1200 {"trans_id": outval},