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