1# Copyright (C) 2005-2021 the SQLAlchemy authors and contributors 2# <see AUTHORS file> 3# 4# This module is part of SQLAlchemy and is released under 5# the MIT License: http://www.opensource.org/licenses/mit-license.php 6 7r""" 8.. dialect:: oracle+cx_oracle 9 :name: cx-Oracle 10 :dbapi: cx_oracle 11 :connectstring: oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 12 :url: https://oracle.github.io/python-cx_Oracle/ 13 14DSN vs. Hostname connections 15----------------------------- 16 17The dialect will connect to a DSN if no database name portion is presented, 18such as:: 19 20 engine = create_engine("oracle+cx_oracle://scott:tiger@oracle1120/?encoding=UTF-8&nencoding=UTF-8") 21 22Above, ``oracle1120`` is passed to cx_Oracle as an Oracle datasource name. 23 24Alternatively, if a database name is present, the ``cx_Oracle.makedsn()`` 25function is used to create an ad-hoc "datasource" name assuming host 26and port:: 27 28 engine = create_engine("oracle+cx_oracle://scott:tiger@hostname:1521/dbname?encoding=UTF-8&nencoding=UTF-8") 29 30Above, the DSN would be created as follows:: 31 32 >>> import cx_Oracle 33 >>> cx_Oracle.makedsn("hostname", 1521, sid="dbname") 34 '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=dbname)))' 35 36The ``service_name`` parameter, also consumed by ``cx_Oracle.makedsn()``, may 37be specified in the URL query string, e.g. ``?service_name=my_service``. 38 39 40Passing cx_Oracle connect arguments 41----------------------------------- 42 43Additional connection arguments can usually be passed via the URL 44query string; particular symbols like ``cx_Oracle.SYSDBA`` are intercepted 45and converted to the correct symbol:: 46 47 e = create_engine( 48 "oracle+cx_oracle://user:pass@dsn?encoding=UTF-8&nencoding=UTF-8&mode=SYSDBA&events=true") 49 50.. versionchanged:: 1.3 the cx_oracle dialect now accepts all argument names 51 within the URL string itself, to be passed to the cx_Oracle DBAPI. As 52 was the case earlier but not correctly documented, the 53 :paramref:`_sa.create_engine.connect_args` parameter also accepts all 54 cx_Oracle DBAPI connect arguments. 55 56To pass arguments directly to ``.connect()`` wihtout using the query 57string, use the :paramref:`_sa.create_engine.connect_args` dictionary. 58Any cx_Oracle parameter value and/or constant may be passed, such as:: 59 60 import cx_Oracle 61 e = create_engine( 62 "oracle+cx_oracle://user:pass@dsn", 63 connect_args={ 64 "encoding": "UTF-8", 65 "nencoding": "UTF-8", 66 "mode": cx_Oracle.SYSDBA, 67 "events": True 68 } 69 ) 70 71Options consumed by the SQLAlchemy cx_Oracle dialect outside of the driver 72-------------------------------------------------------------------------- 73 74There are also options that are consumed by the SQLAlchemy cx_oracle dialect 75itself. These options are always passed directly to :func:`_sa.create_engine` 76, such as:: 77 78 e = create_engine( 79 "oracle+cx_oracle://user:pass@dsn", coerce_to_unicode=False) 80 81The parameters accepted by the cx_oracle dialect are as follows: 82 83* ``arraysize`` - set the cx_oracle.arraysize value on cursors, defaulted 84 to 50. This setting is significant with cx_Oracle as the contents of LOB 85 objects are only readable within a "live" row (e.g. within a batch of 86 50 rows). 87 88* ``auto_convert_lobs`` - defaults to True; See :ref:`cx_oracle_lob`. 89 90* ``coerce_to_unicode`` - see :ref:`cx_oracle_unicode` for detail. 91 92* ``coerce_to_decimal`` - see :ref:`cx_oracle_numeric` for detail. 93 94* ``encoding_errors`` - see :ref:`cx_oracle_unicode_encoding_errors` for detail. 95 96.. _cx_oracle_unicode: 97 98Unicode 99------- 100 101As is the case for all DBAPIs under Python 3, all strings are inherently 102Unicode strings. Under Python 2, cx_Oracle also supports Python Unicode 103objects directly. In all cases however, the driver requires an explicit 104encoding configuration. 105 106Ensuring the Correct Client Encoding 107^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 108 109The long accepted standard for establishing client encoding for nearly all 110Oracle related software is via the `NLS_LANG <https://www.oracle.com/database/technologies/faq-nls-lang.html>`_ 111environment variable. cx_Oracle like most other Oracle drivers will use 112this environment variable as the source of its encoding configuration. The 113format of this variable is idiosyncratic; a typical value would be 114``AMERICAN_AMERICA.AL32UTF8``. 115 116The cx_Oracle driver also supports a programmatic alternative which is to 117pass the ``encoding`` and ``nencoding`` parameters directly to its 118``.connect()`` function. These can be present in the URL as follows:: 119 120 engine = create_engine("oracle+cx_oracle://scott:tiger@oracle1120/?encoding=UTF-8&nencoding=UTF-8") 121 122For the meaning of the ``encoding`` and ``nencoding`` parameters, please 123consult 124`Characters Sets and National Language Support (NLS) <https://cx-oracle.readthedocs.io/en/latest/user_guide/globalization.html#globalization>`_. 125 126.. seealso:: 127 128 `Characters Sets and National Language Support (NLS) <https://cx-oracle.readthedocs.io/en/latest/user_guide/globalization.html#globalization>`_ 129 - in the cx_Oracle documentation. 130 131 132Unicode-specific Column datatypes 133^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 134 135The Core expression language handles unicode data by use of the :class:`.Unicode` 136and :class:`.UnicodeText` 137datatypes. These types correspond to the VARCHAR2 and CLOB Oracle datatypes by 138default. When using these datatypes with Unicode data, it is expected that 139the Oracle database is configured with a Unicode-aware character set, as well 140as that the ``NLS_LANG`` environment variable is set appropriately, so that 141the VARCHAR2 and CLOB datatypes can accommodate the data. 142 143In the case that the Oracle database is not configured with a Unicode character 144set, the two options are to use the :class:`_types.NCHAR` and 145:class:`_oracle.NCLOB` datatypes explicitly, or to pass the flag 146``use_nchar_for_unicode=True`` to :func:`_sa.create_engine`, 147which will cause the 148SQLAlchemy dialect to use NCHAR/NCLOB for the :class:`.Unicode` / 149:class:`.UnicodeText` datatypes instead of VARCHAR/CLOB. 150 151.. versionchanged:: 1.3 The :class:`.Unicode` and :class:`.UnicodeText` 152 datatypes now correspond to the ``VARCHAR2`` and ``CLOB`` Oracle datatypes 153 unless the ``use_nchar_for_unicode=True`` is passed to the dialect 154 when :func:`_sa.create_engine` is called. 155 156Unicode Coercion of result rows under Python 2 157^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 158 159When result sets are fetched that include strings, under Python 3 the cx_Oracle 160DBAPI returns all strings as Python Unicode objects, since Python 3 only has a 161Unicode string type. This occurs for data fetched from datatypes such as 162VARCHAR2, CHAR, CLOB, NCHAR, NCLOB, etc. In order to provide cross- 163compatibility under Python 2, the SQLAlchemy cx_Oracle dialect will add 164Unicode-conversion to string data under Python 2 as well. Historically, this 165made use of converters that were supplied by cx_Oracle but were found to be 166non-performant; SQLAlchemy's own converters are used for the string to Unicode 167conversion under Python 2. To disable the Python 2 Unicode conversion for 168VARCHAR2, CHAR, and CLOB, the flag ``coerce_to_unicode=False`` can be passed to 169:func:`_sa.create_engine`. 170 171.. versionchanged:: 1.3 Unicode conversion is applied to all string values 172 by default under python 2. The ``coerce_to_unicode`` now defaults to True 173 and can be set to False to disable the Unicode coercion of strings that are 174 delivered as VARCHAR2/CHAR/CLOB data. 175 176.. _cx_oracle_unicode_encoding_errors: 177 178Encoding Errors 179^^^^^^^^^^^^^^^ 180 181For the unusual case that data in the Oracle database is present with a broken 182encoding, the dialect accepts a parameter ``encoding_errors`` which will be 183passed to Unicode decoding functions in order to affect how decoding errors are 184handled. The value is ultimately consumed by the Python `decode 185<https://docs.python.org/3/library/stdtypes.html#bytes.decode>`_ function, and 186is passed both via cx_Oracle's ``encodingErrors`` parameter consumed by 187``Cursor.var()``, as well as SQLAlchemy's own decoding function, as the 188cx_Oracle dialect makes use of both under different circumstances. 189 190.. versionadded:: 1.3.11 191 192 193.. _cx_oracle_setinputsizes: 194 195Fine grained control over cx_Oracle data binding performance with setinputsizes 196------------------------------------------------------------------------------- 197 198The cx_Oracle DBAPI has a deep and fundamental reliance upon the usage of the 199DBAPI ``setinputsizes()`` call. The purpose of this call is to establish the 200datatypes that are bound to a SQL statement for Python values being passed as 201parameters. While virtually no other DBAPI assigns any use to the 202``setinputsizes()`` call, the cx_Oracle DBAPI relies upon it heavily in its 203interactions with the Oracle client interface, and in some scenarios it is not 204possible for SQLAlchemy to know exactly how data should be bound, as some 205settings can cause profoundly different performance characteristics, while 206altering the type coercion behavior at the same time. 207 208Users of the cx_Oracle dialect are **strongly encouraged** to read through 209cx_Oracle's list of built-in datatype symbols at 210http://cx-oracle.readthedocs.io/en/latest/module.html#database-types. 211Note that in some cases, significant performance degradation can occur when 212using these types vs. not, in particular when specifying ``cx_Oracle.CLOB``. 213 214On the SQLAlchemy side, the :meth:`.DialectEvents.do_setinputsizes` event can 215be used both for runtime visibility (e.g. logging) of the setinputsizes step as 216well as to fully control how ``setinputsizes()`` is used on a per-statement 217basis. 218 219.. versionadded:: 1.2.9 Added :meth:`.DialectEvents.setinputsizes` 220 221 222Example 1 - logging all setinputsizes calls 223^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 224 225The following example illustrates how to log the intermediary values from a 226SQLAlchemy perspective before they are converted to the raw ``setinputsizes()`` 227parameter dictionary. The keys of the dictionary are :class:`.BindParameter` 228objects which have a ``.key`` and a ``.type`` attribute:: 229 230 from sqlalchemy import create_engine, event 231 232 engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe") 233 234 @event.listens_for(engine, "do_setinputsizes") 235 def _log_setinputsizes(inputsizes, cursor, statement, parameters, context): 236 for bindparam, dbapitype in inputsizes.items(): 237 log.info( 238 "Bound parameter name: %s SQLAlchemy type: %r " 239 "DBAPI object: %s", 240 bindparam.key, bindparam.type, dbapitype) 241 242Example 2 - remove all bindings to CLOB 243^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 244 245The ``CLOB`` datatype in cx_Oracle incurs a significant performance overhead, 246however is set by default for the ``Text`` type within the SQLAlchemy 1.2 247series. This setting can be modified as follows:: 248 249 from sqlalchemy import create_engine, event 250 from cx_Oracle import CLOB 251 252 engine = create_engine("oracle+cx_oracle://scott:tiger@host/xe") 253 254 @event.listens_for(engine, "do_setinputsizes") 255 def _remove_clob(inputsizes, cursor, statement, parameters, context): 256 for bindparam, dbapitype in list(inputsizes.items()): 257 if dbapitype is CLOB: 258 del inputsizes[bindparam] 259 260.. _cx_oracle_returning: 261 262RETURNING Support 263----------------- 264 265The cx_Oracle dialect implements RETURNING using OUT parameters. 266The dialect supports RETURNING fully, however cx_Oracle 6 is recommended 267for complete support. 268 269.. _cx_oracle_lob: 270 271LOB Objects 272----------- 273 274cx_oracle returns oracle LOBs using the cx_oracle.LOB object. SQLAlchemy 275converts these to strings so that the interface of the Binary type is 276consistent with that of other backends, which takes place within a cx_Oracle 277outputtypehandler. 278 279cx_Oracle prior to version 6 would require that LOB objects be read before 280a new batch of rows would be read, as determined by the ``cursor.arraysize``. 281As of the 6 series, this limitation has been lifted. Nevertheless, because 282SQLAlchemy pre-reads these LOBs up front, this issue is avoided in any case. 283 284To disable the auto "read()" feature of the dialect, the flag 285``auto_convert_lobs=False`` may be passed to :func:`_sa.create_engine`. Under 286the cx_Oracle 5 series, having this flag turned off means there is the chance 287of reading from a stale LOB object if not read as it is fetched. With 288cx_Oracle 6, this issue is resolved. 289 290.. versionchanged:: 1.2 the LOB handling system has been greatly simplified 291 internally to make use of outputtypehandlers, and no longer makes use 292 of alternate "buffered" result set objects. 293 294Two Phase Transactions Not Supported 295------------------------------------- 296 297Two phase transactions are **not supported** under cx_Oracle due to poor 298driver support. As of cx_Oracle 6.0b1, the interface for 299two phase transactions has been changed to be more of a direct pass-through 300to the underlying OCI layer with less automation. The additional logic 301to support this system is not implemented in SQLAlchemy. 302 303.. _cx_oracle_numeric: 304 305Precision Numerics 306------------------ 307 308SQLAlchemy's numeric types can handle receiving and returning values as Python 309``Decimal`` objects or float objects. When a :class:`.Numeric` object, or a 310subclass such as :class:`.Float`, :class:`_oracle.DOUBLE_PRECISION` etc. is in 311use, the :paramref:`.Numeric.asdecimal` flag determines if values should be 312coerced to ``Decimal`` upon return, or returned as float objects. To make 313matters more complicated under Oracle, Oracle's ``NUMBER`` type can also 314represent integer values if the "scale" is zero, so the Oracle-specific 315:class:`_oracle.NUMBER` type takes this into account as well. 316 317The cx_Oracle dialect makes extensive use of connection- and cursor-level 318"outputtypehandler" callables in order to coerce numeric values as requested. 319These callables are specific to the specific flavor of :class:`.Numeric` in 320use, as well as if no SQLAlchemy typing objects are present. There are 321observed scenarios where Oracle may sends incomplete or ambiguous information 322about the numeric types being returned, such as a query where the numeric types 323are buried under multiple levels of subquery. The type handlers do their best 324to make the right decision in all cases, deferring to the underlying cx_Oracle 325DBAPI for all those cases where the driver can make the best decision. 326 327When no typing objects are present, as when executing plain SQL strings, a 328default "outputtypehandler" is present which will generally return numeric 329values which specify precision and scale as Python ``Decimal`` objects. To 330disable this coercion to decimal for performance reasons, pass the flag 331``coerce_to_decimal=False`` to :func:`_sa.create_engine`:: 332 333 engine = create_engine("oracle+cx_oracle://dsn", coerce_to_decimal=False) 334 335The ``coerce_to_decimal`` flag only impacts the results of plain string 336SQL staements that are not otherwise associated with a :class:`.Numeric` 337SQLAlchemy type (or a subclass of such). 338 339.. versionchanged:: 1.2 The numeric handling system for cx_Oracle has been 340 reworked to take advantage of newer cx_Oracle features as well 341 as better integration of outputtypehandlers. 342 343""" # noqa 344 345from __future__ import absolute_import 346 347import collections 348import decimal 349import random 350import re 351 352from . import base as oracle 353from .base import OracleCompiler 354from .base import OracleDialect 355from .base import OracleExecutionContext 356from ... import exc 357from ... import processors 358from ... import types as sqltypes 359from ... import util 360from ...engine import result as _result 361from ...util import compat 362 363 364class _OracleInteger(sqltypes.Integer): 365 def get_dbapi_type(self, dbapi): 366 # see https://github.com/oracle/python-cx_Oracle/issues/ 367 # 208#issuecomment-409715955 368 return int 369 370 def _cx_oracle_var(self, dialect, cursor): 371 cx_Oracle = dialect.dbapi 372 return cursor.var( 373 cx_Oracle.STRING, 255, arraysize=cursor.arraysize, outconverter=int 374 ) 375 376 def _cx_oracle_outputtypehandler(self, dialect): 377 def handler(cursor, name, default_type, size, precision, scale): 378 return self._cx_oracle_var(dialect, cursor) 379 380 return handler 381 382 383class _OracleNumeric(sqltypes.Numeric): 384 is_number = False 385 386 def bind_processor(self, dialect): 387 if self.scale == 0: 388 return None 389 elif self.asdecimal: 390 processor = processors.to_decimal_processor_factory( 391 decimal.Decimal, self._effective_decimal_return_scale 392 ) 393 394 def process(value): 395 if isinstance(value, (int, float)): 396 return processor(value) 397 elif value is not None and value.is_infinite(): 398 return float(value) 399 else: 400 return value 401 402 return process 403 else: 404 return processors.to_float 405 406 def result_processor(self, dialect, coltype): 407 return None 408 409 def _cx_oracle_outputtypehandler(self, dialect): 410 cx_Oracle = dialect.dbapi 411 412 is_cx_oracle_6 = dialect._is_cx_oracle_6 413 414 def handler(cursor, name, default_type, size, precision, scale): 415 outconverter = None 416 417 if precision: 418 if self.asdecimal: 419 if default_type == cx_Oracle.NATIVE_FLOAT: 420 # receiving float and doing Decimal after the fact 421 # allows for float("inf") to be handled 422 type_ = default_type 423 outconverter = decimal.Decimal 424 elif is_cx_oracle_6: 425 type_ = decimal.Decimal 426 else: 427 type_ = cx_Oracle.STRING 428 outconverter = dialect._to_decimal 429 else: 430 if self.is_number and scale == 0: 431 # integer. cx_Oracle is observed to handle the widest 432 # variety of ints when no directives are passed, 433 # from 5.2 to 7.0. See [ticket:4457] 434 return None 435 else: 436 type_ = cx_Oracle.NATIVE_FLOAT 437 438 else: 439 if self.asdecimal: 440 if default_type == cx_Oracle.NATIVE_FLOAT: 441 type_ = default_type 442 outconverter = decimal.Decimal 443 elif is_cx_oracle_6: 444 type_ = decimal.Decimal 445 else: 446 type_ = cx_Oracle.STRING 447 outconverter = dialect._to_decimal 448 else: 449 if self.is_number and scale == 0: 450 # integer. cx_Oracle is observed to handle the widest 451 # variety of ints when no directives are passed, 452 # from 5.2 to 7.0. See [ticket:4457] 453 return None 454 else: 455 type_ = cx_Oracle.NATIVE_FLOAT 456 457 return cursor.var( 458 type_, 459 255, 460 arraysize=cursor.arraysize, 461 outconverter=outconverter, 462 ) 463 464 return handler 465 466 467class _OracleBinaryFloat(_OracleNumeric): 468 def get_dbapi_type(self, dbapi): 469 return dbapi.NATIVE_FLOAT 470 471 472class _OracleBINARY_FLOAT(_OracleBinaryFloat, oracle.BINARY_FLOAT): 473 pass 474 475 476class _OracleBINARY_DOUBLE(_OracleBinaryFloat, oracle.BINARY_DOUBLE): 477 pass 478 479 480class _OracleNUMBER(_OracleNumeric): 481 is_number = True 482 483 484class _OracleDate(sqltypes.Date): 485 def bind_processor(self, dialect): 486 return None 487 488 def result_processor(self, dialect, coltype): 489 def process(value): 490 if value is not None: 491 return value.date() 492 else: 493 return value 494 495 return process 496 497 498# TODO: the names used across CHAR / VARCHAR / NCHAR / NVARCHAR 499# here are inconsistent and not very good 500class _OracleChar(sqltypes.CHAR): 501 def get_dbapi_type(self, dbapi): 502 return dbapi.FIXED_CHAR 503 504 505class _OracleNChar(sqltypes.NCHAR): 506 def get_dbapi_type(self, dbapi): 507 return dbapi.FIXED_NCHAR 508 509 510class _OracleUnicodeStringNCHAR(oracle.NVARCHAR2): 511 def get_dbapi_type(self, dbapi): 512 return dbapi.NCHAR 513 514 515class _OracleUnicodeStringCHAR(sqltypes.Unicode): 516 def get_dbapi_type(self, dbapi): 517 return dbapi.LONG_STRING 518 519 520class _OracleUnicodeTextNCLOB(oracle.NCLOB): 521 def get_dbapi_type(self, dbapi): 522 return dbapi.NCLOB 523 524 525class _OracleUnicodeTextCLOB(sqltypes.UnicodeText): 526 def get_dbapi_type(self, dbapi): 527 return dbapi.CLOB 528 529 530class _OracleText(sqltypes.Text): 531 def get_dbapi_type(self, dbapi): 532 return dbapi.CLOB 533 534 535class _OracleLong(oracle.LONG): 536 def get_dbapi_type(self, dbapi): 537 return dbapi.LONG_STRING 538 539 540class _OracleString(sqltypes.String): 541 pass 542 543 544class _OracleEnum(sqltypes.Enum): 545 def bind_processor(self, dialect): 546 enum_proc = sqltypes.Enum.bind_processor(self, dialect) 547 548 def process(value): 549 raw_str = enum_proc(value) 550 return raw_str 551 552 return process 553 554 555class _OracleBinary(sqltypes.LargeBinary): 556 def get_dbapi_type(self, dbapi): 557 return dbapi.BLOB 558 559 def bind_processor(self, dialect): 560 return None 561 562 def result_processor(self, dialect, coltype): 563 if not dialect.auto_convert_lobs: 564 return None 565 else: 566 return super(_OracleBinary, self).result_processor( 567 dialect, coltype 568 ) 569 570 571class _OracleInterval(oracle.INTERVAL): 572 def get_dbapi_type(self, dbapi): 573 return dbapi.INTERVAL 574 575 576class _OracleRaw(oracle.RAW): 577 pass 578 579 580class _OracleRowid(oracle.ROWID): 581 def get_dbapi_type(self, dbapi): 582 return dbapi.ROWID 583 584 585class OracleCompiler_cx_oracle(OracleCompiler): 586 _oracle_cx_sql_compiler = True 587 588 def bindparam_string(self, name, **kw): 589 quote = getattr(name, "quote", None) 590 if ( 591 quote is True 592 or quote is not False 593 and self.preparer._bindparam_requires_quotes(name) 594 ): 595 if kw.get("expanding", False): 596 raise exc.CompileError( 597 "Can't use expanding feature with parameter name " 598 "%r on Oracle; it requires quoting which is not supported " 599 "in this context." % name 600 ) 601 quoted_name = '"%s"' % name 602 self._quoted_bind_names[name] = quoted_name 603 return OracleCompiler.bindparam_string(self, quoted_name, **kw) 604 else: 605 return OracleCompiler.bindparam_string(self, name, **kw) 606 607 608class OracleExecutionContext_cx_oracle(OracleExecutionContext): 609 out_parameters = None 610 611 def _setup_quoted_bind_names(self): 612 quoted_bind_names = self.compiled._quoted_bind_names 613 if quoted_bind_names: 614 for param in self.parameters: 615 for fromname, toname in quoted_bind_names.items(): 616 param[toname] = param[fromname] 617 del param[fromname] 618 619 def _handle_out_parameters(self): 620 # if a single execute, check for outparams 621 if len(self.compiled_parameters) == 1: 622 quoted_bind_names = self.compiled._quoted_bind_names 623 for bindparam in self.compiled.binds.values(): 624 if bindparam.isoutparam: 625 name = self.compiled.bind_names[bindparam] 626 type_impl = bindparam.type.dialect_impl(self.dialect) 627 628 if hasattr(type_impl, "_cx_oracle_var"): 629 self.out_parameters[name] = type_impl._cx_oracle_var( 630 self.dialect, self.cursor 631 ) 632 else: 633 dbtype = type_impl.get_dbapi_type(self.dialect.dbapi) 634 635 cx_Oracle = self.dialect.dbapi 636 637 if dbtype is None: 638 raise exc.InvalidRequestError( 639 "Cannot create out parameter for " 640 "parameter " 641 "%r - its type %r is not supported by" 642 " cx_oracle" % (bindparam.key, bindparam.type) 643 ) 644 645 if compat.py2k and dbtype in ( 646 cx_Oracle.CLOB, 647 cx_Oracle.NCLOB, 648 ): 649 outconverter = ( 650 processors.to_unicode_processor_factory( 651 self.dialect.encoding, 652 errors=self.dialect.encoding_errors, 653 ) 654 ) 655 self.out_parameters[name] = self.cursor.var( 656 dbtype, 657 outconverter=lambda value: outconverter( 658 value.read() 659 ), 660 ) 661 662 elif dbtype in ( 663 cx_Oracle.BLOB, 664 cx_Oracle.CLOB, 665 cx_Oracle.NCLOB, 666 ): 667 self.out_parameters[name] = self.cursor.var( 668 dbtype, outconverter=lambda value: value.read() 669 ) 670 elif compat.py2k and isinstance( 671 type_impl, sqltypes.Unicode 672 ): 673 outconverter = ( 674 processors.to_unicode_processor_factory( 675 self.dialect.encoding, 676 errors=self.dialect.encoding_errors, 677 ) 678 ) 679 self.out_parameters[name] = self.cursor.var( 680 dbtype, outconverter=outconverter 681 ) 682 else: 683 self.out_parameters[name] = self.cursor.var(dbtype) 684 self.parameters[0][ 685 quoted_bind_names.get(name, name) 686 ] = self.out_parameters[name] 687 688 def _generate_cursor_outputtype_handler(self): 689 output_handlers = {} 690 691 for (keyname, name, objects, type_) in self.compiled._result_columns: 692 handler = type_._cached_custom_processor( 693 self.dialect, 694 "cx_oracle_outputtypehandler", 695 self._get_cx_oracle_type_handler, 696 ) 697 698 if handler: 699 denormalized_name = self.dialect.denormalize_name(keyname) 700 output_handlers[denormalized_name] = handler 701 702 if output_handlers: 703 default_handler = self._dbapi_connection.outputtypehandler 704 705 def output_type_handler( 706 cursor, name, default_type, size, precision, scale 707 ): 708 if name in output_handlers: 709 return output_handlers[name]( 710 cursor, name, default_type, size, precision, scale 711 ) 712 else: 713 return default_handler( 714 cursor, name, default_type, size, precision, scale 715 ) 716 717 self.cursor.outputtypehandler = output_type_handler 718 719 def _get_cx_oracle_type_handler(self, impl): 720 if hasattr(impl, "_cx_oracle_outputtypehandler"): 721 return impl._cx_oracle_outputtypehandler(self.dialect) 722 else: 723 return None 724 725 def pre_exec(self): 726 if not getattr(self.compiled, "_oracle_cx_sql_compiler", False): 727 return 728 729 self.out_parameters = {} 730 731 if self.compiled._quoted_bind_names: 732 self._setup_quoted_bind_names() 733 734 self.set_input_sizes( 735 self.compiled._quoted_bind_names, 736 include_types=self.dialect._include_setinputsizes, 737 ) 738 739 self._handle_out_parameters() 740 741 self._generate_cursor_outputtype_handler() 742 743 def create_cursor(self): 744 c = self._dbapi_connection.cursor() 745 if self.dialect.arraysize: 746 c.arraysize = self.dialect.arraysize 747 748 return c 749 750 def get_result_proxy(self): 751 if self.out_parameters and self.compiled.returning: 752 returning_params = [ 753 self.dialect._returningval(self.out_parameters["ret_%d" % i]) 754 for i in range(len(self.out_parameters)) 755 ] 756 return ReturningResultProxy(self, returning_params) 757 758 result = _result.ResultProxy(self) 759 760 if self.out_parameters: 761 if ( 762 self.compiled_parameters is not None 763 and len(self.compiled_parameters) == 1 764 ): 765 result.out_parameters = out_parameters = {} 766 767 for bind, name in self.compiled.bind_names.items(): 768 if name in self.out_parameters: 769 type_ = bind.type 770 impl_type = type_.dialect_impl(self.dialect) 771 dbapi_type = impl_type.get_dbapi_type( 772 self.dialect.dbapi 773 ) 774 result_processor = impl_type.result_processor( 775 self.dialect, dbapi_type 776 ) 777 if result_processor is not None: 778 out_parameters[name] = result_processor( 779 self.dialect._paramval( 780 self.out_parameters[name] 781 ) 782 ) 783 else: 784 out_parameters[name] = self.dialect._paramval( 785 self.out_parameters[name] 786 ) 787 else: 788 result.out_parameters = dict( 789 (k, self._dialect._paramval(v)) 790 for k, v in self.out_parameters.items() 791 ) 792 793 return result 794 795 796class ReturningResultProxy(_result.FullyBufferedResultProxy): 797 """Result proxy which stuffs the _returning clause + outparams 798 into the fetch.""" 799 800 def __init__(self, context, returning_params): 801 self._returning_params = returning_params 802 super(ReturningResultProxy, self).__init__(context) 803 804 def _cursor_description(self): 805 returning = self.context.compiled.returning 806 return [ 807 (getattr(col, "name", col.anon_label), None) for col in returning 808 ] 809 810 def _buffer_rows(self): 811 return collections.deque([tuple(self._returning_params)]) 812 813 814class OracleDialect_cx_oracle(OracleDialect): 815 execution_ctx_cls = OracleExecutionContext_cx_oracle 816 statement_compiler = OracleCompiler_cx_oracle 817 818 supports_sane_rowcount = True 819 supports_sane_multi_rowcount = True 820 821 supports_unicode_statements = True 822 supports_unicode_binds = True 823 824 driver = "cx_oracle" 825 826 colspecs = { 827 sqltypes.Numeric: _OracleNumeric, 828 sqltypes.Float: _OracleNumeric, 829 oracle.BINARY_FLOAT: _OracleBINARY_FLOAT, 830 oracle.BINARY_DOUBLE: _OracleBINARY_DOUBLE, 831 sqltypes.Integer: _OracleInteger, 832 oracle.NUMBER: _OracleNUMBER, 833 sqltypes.Date: _OracleDate, 834 sqltypes.LargeBinary: _OracleBinary, 835 sqltypes.Boolean: oracle._OracleBoolean, 836 sqltypes.Interval: _OracleInterval, 837 oracle.INTERVAL: _OracleInterval, 838 sqltypes.Text: _OracleText, 839 sqltypes.String: _OracleString, 840 sqltypes.UnicodeText: _OracleUnicodeTextCLOB, 841 sqltypes.CHAR: _OracleChar, 842 sqltypes.NCHAR: _OracleNChar, 843 sqltypes.Enum: _OracleEnum, 844 oracle.LONG: _OracleLong, 845 oracle.RAW: _OracleRaw, 846 sqltypes.Unicode: _OracleUnicodeStringCHAR, 847 sqltypes.NVARCHAR: _OracleUnicodeStringNCHAR, 848 oracle.NCLOB: _OracleUnicodeTextNCLOB, 849 oracle.ROWID: _OracleRowid, 850 } 851 852 execute_sequence_format = list 853 854 _cx_oracle_threaded = None 855 856 @util.deprecated_params( 857 threaded=( 858 "1.3", 859 "The 'threaded' parameter to the cx_oracle dialect " 860 "is deprecated as a dialect-level argument, and will be removed " 861 "in a future release. As of version 1.3, it defaults to False " 862 "rather than True. The 'threaded' option can be passed to " 863 "cx_Oracle directly in the URL query string passed to " 864 ":func:`_sa.create_engine`.", 865 ) 866 ) 867 def __init__( 868 self, 869 auto_convert_lobs=True, 870 coerce_to_unicode=True, 871 coerce_to_decimal=True, 872 arraysize=50, 873 encoding_errors=None, 874 threaded=None, 875 **kwargs 876 ): 877 878 OracleDialect.__init__(self, **kwargs) 879 self.arraysize = arraysize 880 self.encoding_errors = encoding_errors 881 if threaded is not None: 882 self._cx_oracle_threaded = threaded 883 self.auto_convert_lobs = auto_convert_lobs 884 self.coerce_to_unicode = coerce_to_unicode 885 self.coerce_to_decimal = coerce_to_decimal 886 if self._use_nchar_for_unicode: 887 self.colspecs = self.colspecs.copy() 888 self.colspecs[sqltypes.Unicode] = _OracleUnicodeStringNCHAR 889 self.colspecs[sqltypes.UnicodeText] = _OracleUnicodeTextNCLOB 890 891 cx_Oracle = self.dbapi 892 893 if cx_Oracle is None: 894 self._include_setinputsizes = {} 895 self.cx_oracle_ver = (0, 0, 0) 896 else: 897 self.cx_oracle_ver = self._parse_cx_oracle_ver(cx_Oracle.version) 898 if self.cx_oracle_ver < (5, 2) and self.cx_oracle_ver > (0, 0, 0): 899 raise exc.InvalidRequestError( 900 "cx_Oracle version 5.2 and above are supported" 901 ) 902 903 self._include_setinputsizes = { 904 cx_Oracle.DATETIME, 905 cx_Oracle.NCLOB, 906 cx_Oracle.CLOB, 907 cx_Oracle.LOB, 908 cx_Oracle.NCHAR, 909 cx_Oracle.FIXED_NCHAR, 910 cx_Oracle.BLOB, 911 cx_Oracle.FIXED_CHAR, 912 cx_Oracle.TIMESTAMP, 913 _OracleInteger, 914 _OracleBINARY_FLOAT, 915 _OracleBINARY_DOUBLE, 916 } 917 918 self._paramval = lambda value: value.getvalue() 919 920 # https://github.com/oracle/python-cx_Oracle/issues/176#issuecomment-386821291 921 # https://github.com/oracle/python-cx_Oracle/issues/224 922 self._values_are_lists = self.cx_oracle_ver >= (6, 3) 923 if self._values_are_lists: 924 cx_Oracle.__future__.dml_ret_array_val = True 925 926 def _returningval(value): 927 try: 928 return value.values[0][0] 929 except IndexError: 930 return None 931 932 self._returningval = _returningval 933 else: 934 self._returningval = self._paramval 935 936 self._is_cx_oracle_6 = self.cx_oracle_ver >= (6,) 937 938 @property 939 def _cursor_var_unicode_kwargs(self): 940 if self.encoding_errors: 941 if self.cx_oracle_ver >= (6, 4): 942 return {"encodingErrors": self.encoding_errors} 943 else: 944 util.warn( 945 "cx_oracle version %r does not support encodingErrors" 946 % (self.cx_oracle_ver,) 947 ) 948 949 return {} 950 951 def _parse_cx_oracle_ver(self, version): 952 m = re.match(r"(\d+)\.(\d+)(?:\.(\d+))?", version) 953 if m: 954 return tuple(int(x) for x in m.group(1, 2, 3) if x is not None) 955 else: 956 return (0, 0, 0) 957 958 @classmethod 959 def dbapi(cls): 960 import cx_Oracle 961 962 return cx_Oracle 963 964 def initialize(self, connection): 965 super(OracleDialect_cx_oracle, self).initialize(connection) 966 if self._is_oracle_8: 967 self.supports_unicode_binds = False 968 969 self._detect_decimal_char(connection) 970 971 def get_isolation_level(self, connection): 972 # sources: 973 974 # general idea of transaction id, have to start one, etc. 975 # https://stackoverflow.com/questions/10711204/how-to-check-isoloation-level 976 977 # how to decode xid cols from v$transaction to match 978 # https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532779900346079444 979 980 # Oracle tuple comparison without using IN: 981 # https://www.sql-workbench.eu/comparison/tuple_comparison.html 982 983 with connection.cursor() as cursor: 984 # this is the only way to ensure a transaction is started without 985 # actually running DML. There's no way to see the configured 986 # isolation level without getting it from v$transaction which 987 # means transaction has to be started. 988 outval = cursor.var(str) 989 cursor.execute( 990 """ 991 begin 992 :trans_id := dbms_transaction.local_transaction_id( TRUE ); 993 end; 994 """, 995 {"trans_id": outval}, 996 ) 997 trans_id = outval.getvalue() 998 xidusn, xidslot, xidsqn = trans_id.split(".", 2) 999 1000 cursor.execute( 1001 "SELECT CASE BITAND(t.flag, POWER(2, 28)) " 1002 "WHEN 0 THEN 'READ COMMITTED' " 1003 "ELSE 'SERIALIZABLE' END AS isolation_level " 1004 "FROM v$transaction t WHERE " 1005 "(t.xidusn, t.xidslot, t.xidsqn) = " 1006 "((:xidusn, :xidslot, :xidsqn))", 1007 {"xidusn": xidusn, "xidslot": xidslot, "xidsqn": xidsqn}, 1008 ) 1009 row = cursor.fetchone() 1010 if row is None: 1011 raise exc.InvalidRequestError( 1012 "could not retrieve isolation level" 1013 ) 1014 result = row[0] 1015 1016 return result 1017 1018 def set_isolation_level(self, connection, level): 1019 if hasattr(connection, "connection"): 1020 dbapi_connection = connection.connection 1021 else: 1022 dbapi_connection = connection 1023 if level == "AUTOCOMMIT": 1024 dbapi_connection.autocommit = True 1025 else: 1026 dbapi_connection.autocommit = False 1027 connection.rollback() 1028 with connection.cursor() as cursor: 1029 cursor.execute("ALTER SESSION SET ISOLATION_LEVEL=%s" % level) 1030 1031 def _detect_decimal_char(self, connection): 1032 # we have the option to change this setting upon connect, 1033 # or just look at what it is upon connect and convert. 1034 # to minimize the chance of interference with changes to 1035 # NLS_TERRITORY or formatting behavior of the DB, we opt 1036 # to just look at it 1037 1038 self._decimal_char = connection.scalar( 1039 "select value from nls_session_parameters " 1040 "where parameter = 'NLS_NUMERIC_CHARACTERS'" 1041 )[0] 1042 if self._decimal_char != ".": 1043 _detect_decimal = self._detect_decimal 1044 _to_decimal = self._to_decimal 1045 1046 self._detect_decimal = lambda value: _detect_decimal( 1047 value.replace(self._decimal_char, ".") 1048 ) 1049 self._to_decimal = lambda value: _to_decimal( 1050 value.replace(self._decimal_char, ".") 1051 ) 1052 1053 def _detect_decimal(self, value): 1054 if "." in value: 1055 return self._to_decimal(value) 1056 else: 1057 return int(value) 1058 1059 _to_decimal = decimal.Decimal 1060 1061 def _generate_connection_outputtype_handler(self): 1062 """establish the default outputtypehandler established at the 1063 connection level. 1064 1065 """ 1066 1067 dialect = self 1068 cx_Oracle = dialect.dbapi 1069 1070 number_handler = _OracleNUMBER( 1071 asdecimal=True 1072 )._cx_oracle_outputtypehandler(dialect) 1073 float_handler = _OracleNUMBER( 1074 asdecimal=False 1075 )._cx_oracle_outputtypehandler(dialect) 1076 1077 def output_type_handler( 1078 cursor, name, default_type, size, precision, scale 1079 ): 1080 1081 if ( 1082 default_type == cx_Oracle.NUMBER 1083 and default_type is not cx_Oracle.NATIVE_FLOAT 1084 ): 1085 if not dialect.coerce_to_decimal: 1086 return None 1087 elif precision == 0 and scale in (0, -127): 1088 # ambiguous type, this occurs when selecting 1089 # numbers from deep subqueries 1090 return cursor.var( 1091 cx_Oracle.STRING, 1092 255, 1093 outconverter=dialect._detect_decimal, 1094 arraysize=cursor.arraysize, 1095 ) 1096 elif precision and scale > 0: 1097 return number_handler( 1098 cursor, name, default_type, size, precision, scale 1099 ) 1100 else: 1101 return float_handler( 1102 cursor, name, default_type, size, precision, scale 1103 ) 1104 1105 # allow all strings to come back natively as Unicode 1106 elif ( 1107 dialect.coerce_to_unicode 1108 and default_type 1109 in ( 1110 cx_Oracle.STRING, 1111 cx_Oracle.FIXED_CHAR, 1112 ) 1113 and default_type is not cx_Oracle.CLOB 1114 and default_type is not cx_Oracle.NCLOB 1115 ): 1116 if compat.py2k: 1117 outconverter = processors.to_unicode_processor_factory( 1118 dialect.encoding, errors=dialect.encoding_errors 1119 ) 1120 return cursor.var( 1121 cx_Oracle.STRING, 1122 size, 1123 cursor.arraysize, 1124 outconverter=outconverter, 1125 ) 1126 else: 1127 return cursor.var( 1128 util.text_type, 1129 size, 1130 cursor.arraysize, 1131 **dialect._cursor_var_unicode_kwargs 1132 ) 1133 1134 elif dialect.auto_convert_lobs and default_type in ( 1135 cx_Oracle.CLOB, 1136 cx_Oracle.NCLOB, 1137 ): 1138 if compat.py2k: 1139 outconverter = processors.to_unicode_processor_factory( 1140 dialect.encoding, errors=dialect.encoding_errors 1141 ) 1142 return cursor.var( 1143 cx_Oracle.LONG_STRING, 1144 size, 1145 cursor.arraysize, 1146 outconverter=outconverter, 1147 ) 1148 else: 1149 return cursor.var( 1150 cx_Oracle.LONG_STRING, 1151 size, 1152 cursor.arraysize, 1153 **dialect._cursor_var_unicode_kwargs 1154 ) 1155 1156 elif dialect.auto_convert_lobs and default_type in ( 1157 cx_Oracle.BLOB, 1158 ): 1159 return cursor.var( 1160 cx_Oracle.LONG_BINARY, 1161 size, 1162 cursor.arraysize, 1163 ) 1164 1165 return output_type_handler 1166 1167 def on_connect(self): 1168 1169 output_type_handler = self._generate_connection_outputtype_handler() 1170 1171 def on_connect(conn): 1172 conn.outputtypehandler = output_type_handler 1173 1174 return on_connect 1175 1176 def create_connect_args(self, url): 1177 opts = dict(url.query) 1178 1179 # deprecated in 1.3 1180 for opt in ("use_ansi", "auto_convert_lobs"): 1181 if opt in opts: 1182 util.warn_deprecated( 1183 "cx_oracle dialect option %r should only be passed to " 1184 "create_engine directly, not within the URL string" % opt 1185 ) 1186 util.coerce_kw_type(opts, opt, bool) 1187 setattr(self, opt, opts.pop(opt)) 1188 1189 database = url.database 1190 service_name = opts.pop("service_name", None) 1191 if database or service_name: 1192 # if we have a database, then we have a remote host 1193 port = url.port 1194 if port: 1195 port = int(port) 1196 else: 1197 port = 1521 1198 1199 if database and service_name: 1200 raise exc.InvalidRequestError( 1201 '"service_name" option shouldn\'t ' 1202 'be used with a "database" part of the url' 1203 ) 1204 if database: 1205 makedsn_kwargs = {"sid": database} 1206 if service_name: 1207 makedsn_kwargs = {"service_name": service_name} 1208 1209 dsn = self.dbapi.makedsn(url.host, port, **makedsn_kwargs) 1210 else: 1211 # we have a local tnsname 1212 dsn = url.host 1213 1214 if dsn is not None: 1215 opts["dsn"] = dsn 1216 if url.password is not None: 1217 opts["password"] = url.password 1218 if url.username is not None: 1219 opts["user"] = url.username 1220 1221 if self._cx_oracle_threaded is not None: 1222 opts.setdefault("threaded", self._cx_oracle_threaded) 1223 1224 def convert_cx_oracle_constant(value): 1225 if isinstance(value, util.string_types): 1226 try: 1227 int_val = int(value) 1228 except ValueError: 1229 value = value.upper() 1230 return getattr(self.dbapi, value) 1231 else: 1232 return int_val 1233 else: 1234 return value 1235 1236 util.coerce_kw_type(opts, "mode", convert_cx_oracle_constant) 1237 util.coerce_kw_type(opts, "threaded", bool) 1238 util.coerce_kw_type(opts, "events", bool) 1239 util.coerce_kw_type(opts, "purity", convert_cx_oracle_constant) 1240 return ([], opts) 1241 1242 def _get_server_version_info(self, connection): 1243 return tuple(int(x) for x in connection.connection.version.split(".")) 1244 1245 def is_disconnect(self, e, connection, cursor): 1246 (error,) = e.args 1247 if isinstance( 1248 e, (self.dbapi.InterfaceError, self.dbapi.DatabaseError) 1249 ) and "not connected" in str(e): 1250 return True 1251 1252 if hasattr(error, "code"): 1253 # ORA-00028: your session has been killed 1254 # ORA-03114: not connected to ORACLE 1255 # ORA-03113: end-of-file on communication channel 1256 # ORA-03135: connection lost contact 1257 # ORA-01033: ORACLE initialization or shutdown in progress 1258 # ORA-02396: exceeded maximum idle time, please connect again 1259 # TODO: Others ? 1260 return error.code in (28, 3114, 3113, 3135, 1033, 2396) 1261 else: 1262 return False 1263 1264 @util.deprecated( 1265 "1.2", 1266 "The create_xid() method of the cx_Oracle dialect is deprecated and " 1267 "will be removed in a future release. " 1268 "Two-phase transaction support is no longer functional " 1269 "in SQLAlchemy's cx_Oracle dialect as of cx_Oracle 6.0b1, which no " 1270 "longer supports the API that SQLAlchemy relied upon.", 1271 ) 1272 def create_xid(self): 1273 """create a two-phase transaction ID. 1274 1275 this id will be passed to do_begin_twophase(), do_rollback_twophase(), 1276 do_commit_twophase(). its format is unspecified. 1277 1278 """ 1279 1280 id_ = random.randint(0, 2 ** 128) 1281 return (0x1234, "%032x" % id_, "%032x" % 9) 1282 1283 def do_executemany(self, cursor, statement, parameters, context=None): 1284 if isinstance(parameters, tuple): 1285 parameters = list(parameters) 1286 cursor.executemany(statement, parameters) 1287 1288 def do_begin_twophase(self, connection, xid): 1289 connection.connection.begin(*xid) 1290 1291 def do_prepare_twophase(self, connection, xid): 1292 result = connection.connection.prepare() 1293 connection.info["cx_oracle_prepared"] = result 1294 1295 def do_rollback_twophase( 1296 self, connection, xid, is_prepared=True, recover=False 1297 ): 1298 self.do_rollback(connection.connection) 1299 1300 def do_commit_twophase( 1301 self, connection, xid, is_prepared=True, recover=False 1302 ): 1303 if not is_prepared: 1304 self.do_commit(connection.connection) 1305 else: 1306 oci_prepared = connection.info["cx_oracle_prepared"] 1307 if oci_prepared: 1308 self.do_commit(connection.connection) 1309 1310 def do_recover_twophase(self, connection): 1311 connection.info.pop("cx_oracle_prepared", None) 1312 1313 1314dialect = OracleDialect_cx_oracle 1315