diff --git a/doc/src/aq.rst b/doc/src/api_manual/aq.rst similarity index 98% rename from doc/src/aq.rst rename to doc/src/api_manual/aq.rst index d1f0863d..33c8ef73 100644 --- a/doc/src/aq.rst +++ b/doc/src/api_manual/aq.rst @@ -1,8 +1,10 @@ .. _aq: -**************** -Advanced Queuing -**************** +********************* +Advanced Queuing (AQ) +********************* + +See :ref:`aqusermanual` for more information about using AQ in cx_Oracle. .. note:: @@ -307,4 +309,3 @@ Message Properties the dequeue. It will be one of the values :data:`~cx_Oracle.MSG_WAITING`, :data:`~cx_Oracle.MSG_READY`, :data:`~cx_Oracle.MSG_PROCESSED` or :data:`~cx_Oracle.MSG_EXPIRED`. - diff --git a/doc/src/connection.rst b/doc/src/api_manual/connection.rst similarity index 99% rename from doc/src/connection.rst rename to doc/src/api_manual/connection.rst index 627b012e..deb3de54 100644 --- a/doc/src/connection.rst +++ b/doc/src/api_manual/connection.rst @@ -23,7 +23,7 @@ Connection Object .. method:: Connection.__exit__() The exit point for the connection as a context manager. This will close - the connection and roll back any uncomitted transaction. + the connection and roll back any uncommitted transaction. .. note:: @@ -622,7 +622,7 @@ Connection Object The subscription can be deregistered in the database by calling the function :meth:`~Connection.unsubscribe()`. If this method is not called and the connection that was used to create the subscription is - explictly closed using the function :meth:`~Connection.close()`, the + explicitly closed using the function :meth:`~Connection.close()`, the subscription will not be deregistered in the database. @@ -690,4 +690,3 @@ Connection Object If you connect to Oracle Database 18 or higher with client libraries 12.2 or lower that you will only receive the base version (such as 18.0.0.0.0) instead of the full version (18.3.0.0.0). - diff --git a/doc/src/cursor.rst b/doc/src/api_manual/cursor.rst similarity index 91% rename from doc/src/cursor.rst rename to doc/src/api_manual/cursor.rst index f345740d..b913e630 100644 --- a/doc/src/cursor.rst +++ b/doc/src/api_manual/cursor.rst @@ -58,6 +58,12 @@ Cursor Object the list might be empty and the type cannot be determined automatically) or returning arrays from PL/SQL. + Array variables can only be used for PL/SQL associative arrays with + contiguous keys. For PL/SQL associative arrays with sparsely populated keys + or for varrays and nested tables, the approach shown in this + `example `__ needs to be used. + .. note:: The DB API definition does not define this method. @@ -96,6 +102,8 @@ Cursor Object positional parameters. The result of the call is the return value of the function. + See :ref:`plsqlfunc` for an example. + .. note:: The DB API definition does not define this method. @@ -116,6 +124,8 @@ Cursor Object possibly new values. Keyword parameters will be included after the positional parameters and are not returned as part of the output sequence. + See :ref:`plsqlproc` for an example. + .. note:: The DB API definition does not allow for keyword parameters. @@ -152,14 +162,16 @@ Cursor Object .. method:: Cursor.execute(statement, [parameters], \*\*keywordParameters) - Execute a statement against the database. Parameters may be passed as a - dictionary or sequence or as keyword parameters. If the parameters are a - dictionary, the values will be bound by name and if the parameters are a - sequence the values will be bound by position. Note that if the values are - bound by position, the order of the variables is from left to right as they - are encountered in the statement and SQL statements are processed - differently than PL/SQL statements. For this reason, it is generally - recommended to bind parameters by name instead of by position. + Execute a statement against the database. See :ref:`sqlexecution`. + + Parameters may be passed as a dictionary or sequence or as keyword + parameters. If the parameters are a dictionary, the values will be bound by + name and if the parameters are a sequence the values will be bound by + position. Note that if the values are bound by position, the order of the + variables is from left to right as they are encountered in the statement + and SQL statements are processed differently than PL/SQL statements. For + this reason, it is generally recommended to bind parameters by name instead + of by position. Parameters passed as a dictionary are name and value pairs. The name maps to the bind variable name used by the statement and the value maps to the @@ -193,12 +205,14 @@ Cursor Object Prepare a statement for execution against a database and then execute it against all parameter mappings or sequences found in the sequence - parameters. The statement is managed in the same way as the - :meth:`~Cursor.execute()` method manages it. If the size of the buffers - allocated for any of the parameters exceeds 2 GB, you will receive the - error "DPI-1015: array size of is too large", where varies with the - size of each element being allocated in the buffer. If you receive this - error, decrease the number of elements in the sequence parameters. + parameters. See :ref:`batchstmnt`. + + The statement is managed in the same way as the :meth:`~Cursor.execute()` + method manages it. If the size of the buffers allocated for any of the + parameters exceeds 2 GB, you will receive the error "DPI-1015: array size + of is too large", where varies with the size of each element being + allocated in the buffer. If you receive this error, decrease the number of + elements in the sequence parameters. If there are no parameters, or parameters have previously been bound, the number of iterations can be specified as an integer instead of needing to @@ -252,6 +266,8 @@ Cursor Object An exception is raised if the previous call to :meth:`~Cursor.execute()` did not produce any result set or no call was issued yet. + See :ref:`fetching` for an example. + .. method:: Cursor.fetchmany([numRows=cursor.arraysize]) @@ -260,13 +276,14 @@ Cursor Object cursor's arraysize attribute can affect the performance of this operation. The number of rows to fetch is specified by the parameter. If it is not - given, the cursor's arrysize attribute determines the number of rows to be + given, the cursor's arraysize attribute determines the number of rows to be fetched. If the number of rows available to be fetched is fewer than the amount requested, fewer rows will be returned. An exception is raised if the previous call to :meth:`~Cursor.execute()` did not produce any result set or no call was issued yet. + See :ref:`fetching` for an example. .. method:: Cursor.fetchone() @@ -276,6 +293,7 @@ Cursor Object An exception is raised if the previous call to :meth:`~Cursor.execute()` did not produce any result set or no call was issued yet. + See :ref:`fetching` for an example. .. method:: Cursor.fetchraw([numRows=cursor.arraysize]) @@ -321,7 +339,7 @@ Cursor Object .. method:: Cursor.getbatcherrors() Retrieve the exceptions that took place after a call to - :meth:`~Cursor.executemany()` with batcherors enabled. This will return a + :meth:`~Cursor.executemany()` with batcherrors enabled. This will return a list of Error objects, one error for each iteration that failed. The offset can be determined by looking at the offset attribute of the error object. @@ -387,6 +405,8 @@ Cursor Object variable object will be created. If this attribute is None, the value of the attribute with the same name on the connection is used instead. + See :ref:`outputtypehandlers`. + .. note:: This attribute is an extension to the DB API definition. @@ -519,7 +539,7 @@ Cursor Object .. method:: Cursor.var(dataType, [size, arraysize, inconverter, outconverter, \ typename, encodingErrors]) - Create a variable with the specified charactistics. This method was + Create a variable with the specified characteristics. This method was designed for use with PL/SQL in/out variables where the length or type cannot be determined automatically from the Python object passed in or for use in input and output type handlers defined on cursors or connections. @@ -556,4 +576,3 @@ Cursor Object .. note:: The DB API definition does not define this method. - diff --git a/doc/src/lob.rst b/doc/src/api_manual/lob.rst similarity index 97% rename from doc/src/lob.rst rename to doc/src/api_manual/lob.rst index 72bac982..54c4fbfd 100644 --- a/doc/src/lob.rst +++ b/doc/src/api_manual/lob.rst @@ -4,6 +4,8 @@ LOB Objects *********** +See :ref:`lobdata` for more information about using LOBs. + .. note:: This object is an extension the DB API. It is returned whenever Oracle @@ -86,4 +88,3 @@ LOB Objects offset will have to be chosen carefully to avoid splitting a character. Note that if you want to make the LOB value smaller, you must use the :meth:`~LOB.trim()` function. - diff --git a/doc/src/module.rst b/doc/src/api_manual/module.rst similarity index 98% rename from doc/src/module.rst rename to doc/src/api_manual/module.rst index 13af4edc..120112cb 100644 --- a/doc/src/module.rst +++ b/doc/src/api_manual/module.rst @@ -36,27 +36,29 @@ Module Interface This method is an extension to the DB API definition. -.. function:: Connection(user=None, password=None, dsn=None, \ +.. function:: connect(user=None, password=None, dsn=None, mode=cx_Oracle.DEFAULT_AUTH, \ + handle=0, pool=None, threaded=False, events=False, cclass=None, \ + purity=cx_Oracle.ATTR_PURITY_DEFAULT, newpassword=None, \ + encoding=None, nencoding=None, edition=None, appcontext=[], tag=None, \ + matchanytag=None, shardingkey=[], supershardingkey=[]) + Connection(user=None, password=None, dsn=None, \ mode=cx_Oracle.DEFAULT_AUTH, handle=0, pool=None, threaded=False, \ events=False, cclass=None, purity=cx_Oracle.ATTR_PURITY_DEFAULT, \ newpassword=None, encoding=None, nencoding=None, edition=None, \ appcontext=[], tag=None, matchanytag=False, shardingkey=[], \ supershardingkey=[]) - connect(user=None, password=None, dsn=None, mode=cx_Oracle.DEFAULT_AUTH, \ - handle=0, pool=None, threaded=False, events=False, cclass=None, \ - purity=cx_Oracle.ATTR_PURITY_DEFAULT, newpassword=None, \ - encoding=None, nencoding=None, edition=None, appcontext=[], tag=None, \ - matchanytag=None, shardingkey=[], supershardingkey=[]) Constructor for creating a connection to the database. Return a :ref:`connection object `. All parameters are optional and can be - specified as keyword parameters. + specified as keyword parameters. See :ref:`connhandling` information about + connections. The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file) or is a string like the one returned from :meth:`~cx_Oracle.makedsn()`. If only one parameter is passed, a connect string is assumed which is to be of the format ``user/password@dsn``, the - same format accepted by Oracle applications such as SQL\*Plus. + same format accepted by Oracle applications such as SQL\*Plus. See + :ref:`connstr` for more information. If the mode is specified, it must be one of the :ref:`connection authorization modes` @@ -69,7 +71,7 @@ Module Interface The pool parameter is expected to be a :ref:`session pool object ` and the use of this parameter is the equivalent of calling :meth:`SessionPool.acquire()`. Parameters not - acecpted by that method are ignored. + accepted by that method are ignored. The threaded parameter is expected to be a boolean expression which indicates whether or not Oracle should wrap accesses to connections with a @@ -78,7 +80,7 @@ Module Interface The events parameter is expected to be a boolean expression which indicates whether or not to initialize Oracle in events mode. This is required for - continuous query notification and high availablity event notifications. + continuous query notification and high availability event notifications. The cclass parameter is expected to be a string and defines the connection class for database resident connection pooling (DRCP). @@ -140,7 +142,7 @@ Module Interface .. function:: Cursor(connection) - Constructor for creating a cursor. Return a new + Constructor for creating a cursor. Return a new :ref:`cursor object ` using the connection. .. note:: @@ -187,6 +189,8 @@ Module Interface like `Application Continuity `__. + See :ref:`connpool` for information on connection pooling. + Session pooling creates a pool of available connections to the database, allowing applications to acquire a connection very quickly. It is of primary use in a server where connections are requested @@ -412,7 +416,7 @@ parameter for the :meth:`Connection.deq()` method. .. data:: DEQ_BROWSE This constant is used to specify that dequeue should read the message - without acquiring any lock on the message (eqivalent to a select + without acquiring any lock on the message (equivalent to a select statement). @@ -747,6 +751,8 @@ for subscriptions created by the :meth:`Connection.subscribe()` method. being started up. +.. _cqn-operation-codes: + Operation Codes --------------- @@ -894,6 +900,8 @@ method. events. +.. _subscr-namespaces: + Subscription Namespaces ----------------------- @@ -912,6 +920,8 @@ method. change notification messages are to be sent. This is the default value. +.. _subscr-protocols: + Subscription Protocols ---------------------- @@ -947,6 +957,8 @@ values for the protocol parameter of the :meth:`Connection.subscribe()` method. supported. +.. _subscr-qos: + Subscription Quality of Service ------------------------------- @@ -986,6 +998,8 @@ or more of these values can be OR'ed together. or deleted rows should be included in the message objects that are sent. +.. _types: + Types ===== @@ -1231,7 +1245,7 @@ Exceptions .. exception:: OperationalError Exception raised for errors that are related to the operation of the - database but are not necessarily under the control of the progammer. It is + database but are not necessarily under the control of the programmer. It is a subclass of DatabaseError. @@ -1258,6 +1272,8 @@ Exceptions supported by the database. It is a subclass of DatabaseError. +.. _exchandling: + Exception handling ================== @@ -1308,7 +1324,7 @@ This allows you to use the exceptions for example in the following way: import cx_Oracle - connection = cx_Oracle.connect("cx_Oracle/dev@localhost/orclpdb") + connection = cx_Oracle.connect("cx_Oracle/dev@localhost/orclpdb1") cursor = connection.cursor() try: @@ -1317,4 +1333,3 @@ This allows you to use the exceptions for example in the following way: error, = exc.args print("Oracle-Error-Code:", error.code) print("Oracle-Error-Message:", error.message) - diff --git a/doc/src/objecttype.rst b/doc/src/api_manual/object_type.rst similarity index 99% rename from doc/src/objecttype.rst rename to doc/src/api_manual/object_type.rst index a95de922..3e720fdd 100644 --- a/doc/src/objecttype.rst +++ b/doc/src/api_manual/object_type.rst @@ -49,7 +49,7 @@ Object Type Objects Object Objects -============== +-------------- .. note:: @@ -71,6 +71,8 @@ Object Objects Return a dictionary where the collection's indexes are the keys and the elements are its values. + .. versionadded:: 7.0 + .. method:: Object.aslist() @@ -149,4 +151,3 @@ Object Objects .. method:: Object.trim(num) Remove the specified number of elements from the end of the collection. - diff --git a/doc/src/session_pool.rst b/doc/src/api_manual/session_pool.rst similarity index 98% rename from doc/src/session_pool.rst rename to doc/src/api_manual/session_pool.rst index 387aa8bc..82e93f5d 100644 --- a/doc/src/session_pool.rst +++ b/doc/src/api_manual/session_pool.rst @@ -10,6 +10,8 @@ SessionPool Object Connection pooling in cx_Oracle is handled by SessionPool objects. + See :ref:`connpool` for information on connection pooling. + .. method:: SessionPool.acquire(user=None, password=None, cclass=None, \ purity=cx_Oracle.ATTR_PURITY_DEFAULT, tag=None, matchanytag=False, \ @@ -50,7 +52,7 @@ SessionPool Object .. method:: SessionPool.close(force=False) Close the session pool now, rather than when the last reference to it is - released, which makes it unsable for further work. + released, which makes it unusable for further work. If any connections have been acquired and not released back to the pool this method will fail unless the force parameter is set to True. @@ -185,4 +187,3 @@ SessionPool Object :data:`cx_Oracle.SPOOL_ATTRVAL_TIMEDWAIT`. .. versionadded:: 6.4 - diff --git a/doc/src/soda.rst b/doc/src/api_manual/soda.rst similarity index 97% rename from doc/src/soda.rst rename to doc/src/api_manual/soda.rst index 9c70e55c..643843f5 100644 --- a/doc/src/soda.rst +++ b/doc/src/api_manual/soda.rst @@ -4,9 +4,12 @@ SODA **** -An overview of Simple Oracle Document Access (SODA) can be found -`here -`__. +`Oracle Database Simple Oracle Document Access (SODA) +`__ +allows documents to be inserted, queried, and retrieved from Oracle Database +using a set of NoSQL-style cx_Oracle methods. + +See :ref:`sodausermanual` for a cx_Oracle example. SODA requires Oracle Client 18.3 or higher and Oracle Database 18.1 and higher. The role SODA_APP must be granted to the user. @@ -110,7 +113,7 @@ SODA Collection Object .. note:: This object is an extension the DB API. It is used to represent SODA - collections and is created by methods + collections and is created by methods :meth:`SodaDatabase.createCollection()` and :meth:`SodaDatabase.openCollection()`. @@ -228,7 +231,7 @@ SODA Collection Object .. attribute:: SodaCollection.metadata - This read-only attribute returns a dicationary containing the metadata that + This read-only attribute returns a dictionary containing the metadata that was used to create the collection. See this `collection metadata reference `__ @@ -520,4 +523,3 @@ SODA Operation Object criteria can be specified by chaining methods together. .. versionadded:: 7.0 - diff --git a/doc/src/subscription.rst b/doc/src/api_manual/subscription.rst similarity index 98% rename from doc/src/subscription.rst rename to doc/src/api_manual/subscription.rst index 6b75c4cb..fd089fbc 100644 --- a/doc/src/subscription.rst +++ b/doc/src/api_manual/subscription.rst @@ -98,7 +98,7 @@ Subscription Object .. _msgobjects: Message Objects -=============== +--------------- .. note:: @@ -177,7 +177,7 @@ Message Objects Message Table Objects -===================== +--------------------- .. note:: @@ -206,7 +206,7 @@ Message Table Objects Message Row Objects -=================== +------------------- .. note:: @@ -227,19 +227,19 @@ Message Row Objects Message Query Objects -===================== +--------------------- .. note:: This object is created internally for each query result set changed when notification is received and is found in the queries attribute of message objects. - + .. attribute:: MessageQuery.id This read-only attribute returns the query id of the query for which the - result set changed. The value will match the value returned by + result set changed. The value will match the value returned by Subscription.registerquery when the related query was registered. @@ -255,4 +255,3 @@ Message Query Objects This read-only attribute returns a list of message table objects that give information about the table changes that caused the query result set to change for this notification. - diff --git a/doc/src/variable.rst b/doc/src/api_manual/variable.rst similarity index 99% rename from doc/src/variable.rst rename to doc/src/api_manual/variable.rst index 663d9214..be8b2eed 100644 --- a/doc/src/variable.rst +++ b/doc/src/api_manual/variable.rst @@ -81,4 +81,3 @@ Variable Objects positions in the variable as a list. This is the equivalent of calling :meth:`~Variable.getvalue()` for each valid position and the length will correspond to the value of the :attr:`~Variable.actualElements` attribute. - diff --git a/doc/src/conf.py b/doc/src/conf.py index 29b368a0..e4994cd8 100644 --- a/doc/src/conf.py +++ b/doc/src/conf.py @@ -42,7 +42,7 @@ # The short X.Y version. version = '7.2' # The full version, including alpha/beta/rc tags. -release = '7.2.0' +release = '7.2.3' # There are two options for replacing |today|: either, you set today to some # non-false value, then it is used: @@ -108,6 +108,7 @@ # Output file base name for HTML help builder. htmlhelp_basename = 'cx_Oracledoc' +numfig = True # Options for LaTeX output # ------------------------ diff --git a/doc/src/images/cx_Oracle_arch.png b/doc/src/images/cx_Oracle_arch.png new file mode 100755 index 00000000..7459e7de Binary files /dev/null and b/doc/src/images/cx_Oracle_arch.png differ diff --git a/doc/src/index.rst b/doc/src/index.rst index 5016c806..78a5441d 100644 --- a/doc/src/index.rst +++ b/doc/src/index.rst @@ -8,27 +8,52 @@ against Oracle Client 19, 18, 12, and 11.2, and Python 2.7, 3.5, 3.6 and 3.7. **cx_Oracle** is distributed under an open-source :ref:`license ` -(the BSD license). +(the BSD license). A detailed description of cx_Oracle changes can be found in +the :ref:`release notes `. Contents: +User Guide +========== + +.. toctree:: + :maxdepth: 3 + + user_guide/introduction.rst + user_guide/installation.rst + user_guide/connection_handling.rst + user_guide/sql_execution.rst + user_guide/plsql_execution.rst + user_guide/bind.rst + user_guide/lob_data.rst + user_guide/json_data_type.rst + user_guide/soda.rst + user_guide/xml_data_type.rst + user_guide/batch_statement.rst + user_guide/exception_handling.rst + user_guide/aq.rst + user_guide/cqn.rst + user_guide/txn_management.rst + user_guide/globalization.rst + user_guide/ha.rst + user_guide/tracing_sql.rst + +API Manual +========== + .. toctree:: - :maxdepth: 2 - - installation.rst - module.rst - connection.rst - cursor.rst - variable.rst - session_pool.rst - subscription.rst - lob.rst - objecttype.rst - aq.rst - soda.rst - whatsnew.rst - releasenotes.rst - license.rst + :maxdepth: 3 + + api_manual/module.rst + api_manual/connection.rst + api_manual/cursor.rst + api_manual/variable.rst + api_manual/session_pool.rst + api_manual/subscription.rst + api_manual/lob.rst + api_manual/object_type.rst + api_manual/aq.rst + Soda Document Class Indices and tables diff --git a/doc/src/license.rst b/doc/src/license.rst index a95758c3..71fe67c2 100644 --- a/doc/src/license.rst +++ b/doc/src/license.rst @@ -1,3 +1,5 @@ +:orphan: + .. _license: ******* diff --git a/doc/src/releasenotes.rst b/doc/src/release_notes.rst similarity index 98% rename from doc/src/releasenotes.rst rename to doc/src/release_notes.rst index d9960ec1..f882efe1 100644 --- a/doc/src/releasenotes.rst +++ b/doc/src/release_notes.rst @@ -1,12 +1,52 @@ +:orphan: + .. _releasenotes: cx_Oracle Release Notes ======================= -7.x releases -############ +Version 7.2.3 (October 2019) +---------------------------- + +#) Updated embedded ODPI-C to `version 3.2.2 + `__. +#) Restored support for setting numeric bind variables with boolean values. +#) Ensured that sharding keys are dedicated to the connection that is acquired + using them in order to avoid possible hangs, crashes or unusual errors. +#) Corrected support for PLS_INTEGER and BINARY_INTEGER types when used in + PL/SQL records + (`ODPI-C issue 112 `__). +#) Improved documentation. + + +Version 7.2.2 (August 2019) +--------------------------- + +#) Updated embedded ODPI-C to `version 3.2.1 + `__. +#) A more meaningful error is now returned when calling + :meth:`SodaCollection.insertMany()` with an empty list. +#) A more meaningful error is now returned when calling + :meth:`Subscription.registerquery()` with SQL that is not a SELECT + statement. +#) Eliminated segfault when a connection is closed after being created by a + call to :meth:`cx_Oracle.connect()` with the parameter ``cclass`` set to + a non-empty string. +#) Added user guide documentation. +#) Updated default connect strings to use 19c and XE 18c defaults. + + +Version 7.2.1 (July 2019) +------------------------- + +#) Resolved ``MemoryError`` exception on Windows when using an output type + handler + (`issue 330 `__). +#) Improved test suite and samples. +#) Improved documentation. -.. _releasenotes70: Version 7.2 (July 2019) ----------------------- @@ -247,12 +287,6 @@ Version 7.0 (September 2018) #) Improved documentation. - -6.x releases -############ - -.. _releasenotes60: - Version 6.4.1 (July 2018) ------------------------- @@ -349,7 +383,7 @@ Version 6.3.1 (May 2018) - Ensure that a call to unregister a subscription only occurs if the subscription is still registered. - Ensure that before a statement is executed any buffers used for DML - returning statments are reset. + returning statements are reset. #) Ensure that behavior with cx_Oracle.__future__.dml_ret_array_val not set or False is the same as the behavior in cx_Oracle 6.2 @@ -548,7 +582,7 @@ Version 6.0.3 (November 2017) `__. - - Prevent use of unitialized data in certain cases (`issue 77 + - Prevent use of uninitialized data in certain cases (`issue 77 `__). - Attempting to ping a database earlier than 10g results in error "ORA-1010: invalid OCI operation", but that implies a response from the @@ -620,9 +654,6 @@ Version 6.0.1 (August 2017) Version 6.0 (August 2017) ------------------------- -See :ref:`What's New ` for a summary of the changes between -cx_Oracle 5.3 and cx_Oracle 6.0. - #) Update to `ODPI-C 2.0 `__. @@ -815,10 +846,6 @@ Version 6.0 beta 1 (April 2017) master/samples/CQN.py>`__. -5.x releases -############ - - Version 5.3 (March 2017) ------------------------ @@ -1132,9 +1159,6 @@ Version 5.0 (December 2008) session pools to fetch objects without exceptions taking place. -Older releases -############## - Version 4.4.1 (October 2008) ---------------------------- @@ -1252,7 +1276,7 @@ Version 4.3.2 (August 2007) NATIVE_FLOAT to allow specification of a variable of that specific type where desired. Thanks to D.R. Boxhoorn for pointing out the fact that this was not working properly when the arraysize was anything other than 1. -#) When calling connection.begin(), only create a new tranasction handle if +#) When calling connection.begin(), only create a new transaction handle if one is not already associated with the connection. Thanks to Andreas Mock for discovering this and for Amaury Forgeot d'Arc for diagnosing the problem and pointing the way to a solution. @@ -1399,7 +1423,7 @@ Version 4.1.1 (December 2005) can drastically affect performance of queries since this seems to be a common misunderstanding of first time users of cx_Oracle. #) Add a comment indicating that on HP-UX Itanium with Oracle 10g the library - ttsh10 must alos be linked against. Thanks to Bernard Delmee for the + ttsh10 must also be linked against. Thanks to Bernard Delmee for the information. @@ -1767,4 +1791,3 @@ Version 2.2 (July 2001) installation as suggested by Steve Holden. #) Added simple usage example as requested by many people. #) Added HISTORY file to the distribution. - diff --git a/doc/src/user_guide/aq.rst b/doc/src/user_guide/aq.rst new file mode 100644 index 00000000..f4be3cf0 --- /dev/null +++ b/doc/src/user_guide/aq.rst @@ -0,0 +1,208 @@ +.. _aqusermanual: + +*********************** +Oracle Advanced Queuing +*********************** + +`Oracle Advanced Queuing +`__ is a highly +configurable and scalable messaging feature of Oracle Database. It has +interfaces in various languages, letting you integrate multiple tools in your +architecture. + +cx_Oracle 7.2 introduced an updated interface for Oracle Advanced +Queuing. + +There are Advanced Queuing examples in the `GitHub examples +`__ directory. + + +Creating a Queue +================ + +Before being used, queues need to be created in the database, for example in +SQL*Plus: + +.. code-block:: sql + + begin + dbms_aqadm.create_queue_table('MY_QUEUE_TABLE', 'RAW'); + dbms_aqadm.create_queue('DEMO_RAW_QUEUE', 'MY_QUEUE_TABLE'); + dbms_aqadm.start_queue('DEMO_RAW_QUEUE'); + end; + / + +This examples creates a RAW queue suitable for sending string or raw bytes +messages. + + +Enqueuing Messages +================== + +To send messages in Python you connect and get a :ref:`queue `. The +queue can be used for enqueuing, dequeuing, or both as needed. + +.. code-block:: python + + queue = connection.queue("DEMO_RAW_QUEUE") + +Now messages can be queued using :meth:`Queue.enqOne()`. To send three +messages: + +.. code-block:: python + + PAYLOAD_DATA = [ + "The first message", + "The second message", + "The third message" + ] + for data in PAYLOAD_DATA: + queue.enqOne(connection.msgproperties(payload=data)) + connection.commit() + +Since the queue sending the messages is a RAW queue, the strings in this +example will be internally encoded to bytes using :attr:`Connection.encoding` +before being enqueued. + + +Dequeuing Messages +================== + +Dequeuing is performed similarly. To dequeue a message call the method +:meth:`Queue.deqOne()` as shown. Note that if the message is expected to be a +string, the bytes must be decoded using :attr:`Connection.encoding`. + +.. code-block:: python + + queue = connection.queue("DEMO_RAW_QUEUE") + msg = queue.deqOne() + connection.commit() + print(msg.payload.decode(connection.encoding)) + + +Using Object Queues +=================== + +Named Oracle objects can be enqueued and dequeued as well. Given an object +type called ``UDT_BOOK``: + +.. code-block:: sql + + CREATE OR REPLACE TYPE udt_book AS OBJECT ( + Title VARCHAR2(100), + Authors VARCHAR2(100), + Price NUMBER(5,2) + ); + / + +And a queue that accepts this type: + +.. code-block:: sql + + begin + dbms_aqadm.create_queue_table('BOOK_QUEUE_TAB', 'UDT_BOOK'); + dbms_aqadm.create_queue('DEMO_BOOK_QUEUE', 'BOOK_QUEUE_TAB'); + dbms_aqadm.start_queue('DEMO_BOOK_QUEUE'); + end; + / + +You can queue messages: + +.. code-block:: python + + booksType = connection.gettype("UDT_BOOK") + queue = connection.queue("DEMO_BOOK_QUEUE", booksType) + + book = booksType.newobject() + book.TITLE = "Quick Brown Fox" + book.AUTHORS = "The Dog" + book.PRICE = 123 + + queue.enqOne(connection.msgproperties(payload=book)) + connection.commit() + +Dequeuing is done like this: + +.. code-block:: python + + booksType = connection.gettype("UDT_BOOK") + queue = connection.queue("DEMO_BOOK_QUEUE", booksType) + + msg = queue.deqOne() + connection.commit() + print(msg.payload.TITLE) # will print Quick Brown Fox + + +Changing Queue and Message Options +================================== + +Refer to the :ref:`cx_Oracle AQ API ` and +`Oracle Advanced Queuing documentation +`__ for details +on all of the enqueue and dequeue options available. + +Enqueue options can be set. For example, to make it so that an explicit +call to :meth:`~Connection.commit()` on the connection is not needed to commit +messages: + +.. code-block:: python + + queue = connection.queue("DEMO_RAW_QUEUE") + queue.enqOptions.visibility = cx_Oracle.ENQ_IMMEDIATE + +Dequeue options can also be set. For example, to specify not to block on +dequeuing if no messages are available: + +.. code-block:: python + + queue = connection.queue("DEMO_RAW_QUEUE") + queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT + +Message properties can be set when enqueuing. For example, to set an +expiration of 60 seconds on a message: + +.. code-block:: python + + queue.enqOne(connection.msgproperties(payload="Message", expiration=60)) + +This means that if no dequeue operation occurs within 60 seconds that the +message will be dropped from the queue. + + +Bulk Enqueue and Dequeue +======================== + +The :meth:`Queue.enqMany()` and :meth:`Queue.deqMany()` methods can be used for +efficient bulk message handling. + +:meth:`Queue.enqMany()` is similar to :meth:`Queue.enqOne()` but accepts an +array of messages: + +.. code-block:: python + + messages = [ + "The first message", + "The second message", + "The third message", + ] + queue = connection.queue("DEMO_RAW_QUEUE") + queue.enqMany(connection.msgproperties(payload=m) for m in messages) + connection.commit() + +Warning: calling :meth:`Queue.enqMany()` in parallel on different connections +acquired from the same pool may fail due to Oracle bug 29928074. Ensure that +this function is not run in parallel, use standalone connections or connections +from different pools, or make multiple calls to :meth:`Queue.enqOne()` instead. +The function :meth:`Queue.deqMany()` call is not affected. + +To dequeue multiple messages at one time, use :meth:`Queue.deqMany()`. This +takes an argument specifying the maximum number of messages to dequeue at one +time: + +.. code-block:: python + + for m in queue.deqMany(maxMessages=10): + print(m.payload.decode(connection.encoding)) + +Depending on the queue properties and the number of messages available to +dequeue, this code will print out from zero to ten messages. diff --git a/doc/src/user_guide/batch_statement.rst b/doc/src/user_guide/batch_statement.rst new file mode 100644 index 00000000..82d5c0ab --- /dev/null +++ b/doc/src/user_guide/batch_statement.rst @@ -0,0 +1,286 @@ +.. _batchstmnt: + +****************************************** +Batch Statement Execution and Bulk Loading +****************************************** + +Inserting or updating multiple rows can be performed efficiently with +:meth:`Cursor.executemany()`, making it easy to work with large data sets with +cx_Oracle. This method can significantly outperform repeated calls to +:meth:`Cursor.execute()` by reducing network transfer costs and database load. +The :meth:`~Cursor.executemany()` method can also be used to execute PL/SQL +statements multiple times at once. + +There are examples in the `GitHub examples +`__ +directory. + +The following tables will be used in the samples that follow: + +.. code-block:: sql + + create table ParentTable ( + ParentId number(9) not null, + Description varchar2(60) not null, + constraint ParentTable_pk primary key (ParentId) + ); + + create table ChildTable ( + ChildId number(9) not null, + ParentId number(9) not null, + Description varchar2(60) not null, + constraint ChildTable_pk primary key (ChildId), + constraint ChildTable_fk foreign key (ParentId) + references ParentTable + ); + + +Batch Execution of SQL +====================== + +The following example inserts five rows into the table ``ParentTable``: + +.. code-block:: python + + dataToInsert = [ + (10, 'Parent 10'), + (20, 'Parent 20'), + (30, 'Parent 30'), + (40, 'Parent 40'), + (50, 'Parent 50') + ] + cursor.executemany("insert into ParentTable values (:1, :2)", dataToInsert) + +This code requires only one round-trip from the client to the database instead +of the five round-trips that would be required for repeated calls to +:meth:`~Cursor.execute()`. For very large data sets there may be an external +buffer or network limits to how many rows can be processed, so repeated calls +to ``executemany()`` may be required. The limits are based on both the number +of rows being processed as well as the "size" of each row that is being +processed. Repeated calls to :meth:`~Cursor.executemany()` are still +better than repeated calls to :meth:`~Cursor.execute()`. + + +Batch Execution of PL/SQL +========================= + +PL/SQL functions and procedures and anonymous PL/SQL blocks can also be called +using :meth:`~Cursor.executemany()` in order to improve performance. For +example: + +.. code-block:: python + + dataToInsert = [ + (10, 'Parent 10'), + (20, 'Parent 20'), + (30, 'Parent 30'), + (40, 'Parent 40'), + (50, 'Parent 50') + ] + cursor.executemany("begin mypkg.create_parent(:1, :2); end;", dataToInsert) + +Note that the ``batcherrors`` parameter (discussed below) cannot be used with +PL/SQL block execution. + + +Handling Data Errors +==================== + +Large datasets may contain some invalid data. When using batch execution as +discussed above, the entire batch will be discarded if a single error is +detected, potentially eliminating the performance benefits of batch execution +and increasing the complexity of the code required to handle those errors. If +the parameter ``batchErrors`` is set to the value ``True`` when calling +:meth:`~Cursor.executemany()`, however, processing will continue even if there +are data errors in some rows, and the rows containing errors can be examined +afterwards to determine what course the application should take. Note that if +any errors are detected, a transaction will be started but not committed, even +if :attr:`Connection.autocommit` is set to ``True``. After examining the errors +and deciding what to do with them, the application needs to explicitly commit +or roll back the transaction with :meth:`Connection.commit()` or +:meth:`Connection.rollback()`, as needed. + +This example shows how data errors can be identified: + +.. code-block:: python + + dataToInsert = [ + (60, 'Parent 60'), + (70, 'Parent 70'), + (70, 'Parent 70 (duplicate)'), + (80, 'Parent 80'), + (80, 'Parent 80 (duplicate)'), + (90, 'Parent 90') + ] + cursor.executemany("insert into ParentTable values (:1, :2)", dataToInsert, + batcherrors=True) + for error in cursor.getbatcherrors(): + print("Error", error.message, "at row offset", error.offset) + +The output is:: + + Error ORA-00001: unique constraint (PYTHONDEMO.PARENTTABLE_PK) violated at row offset 2 + Error ORA-00001: unique constraint (PYTHONDEMO.PARENTTABLE_PK) violated at row offset 4 + +The row offset is the index into the array of the data that could not be +inserted due to errors. The application could choose to commit or rollback the +other rows that were successfully inserted. Alternatively, it could correct +the data for the two invalid rows and attempt to insert them again before +committing. + + +Identifying Affected Rows +========================= + +When executing a DML statement using :meth:`~Cursor.execute()`, the number of +rows affected can be examined by looking at the attribute +:attr:`~Cursor.rowcount`. When performing batch executing with +:meth:`Cursor.executemany()`, however, the row count will return the *total* +number of rows that were affected. If you want to know the total number of rows +affected by each row of data that is bound you must set the parameter +``arraydmlrowcounts`` to ``True``, as shown: + +.. code-block:: python + + parentIdsToDelete = [20, 30, 50] + cursor.executemany("delete from ChildTable where ParentId = :1", + [(i,) for i in parentIdsToDelete], + arraydmlrowcounts=True) + rowCounts = cursor.getarraydmlrowcounts() + for parentId, count in zip(parentIdsToDelete, rowCounts): + print("Parent ID:", parentId, "deleted", count, "rows.") + +Using the data found in the `GitHub samples +`__ the output +is as follows:: + + Parent ID: 20 deleted 3 rows. + Parent ID: 30 deleted 2 rows. + Parent ID: 50 deleted 4 rows. + + +DML RETURNING +============= + +DML statements like INSERT, UPDATE, DELETE and MERGE can return values by using +the DML RETURNING syntax. A bind variable can be created to accept this data. +See :ref:`bind` for more information. + +If, instead of merely deleting the rows as shown in the previous example, you +also wanted to know some information about each of the rows that were deleted, +you could use the following code: + +.. code-block:: python + + parentIdsToDelete = [20, 30, 50] + childIdVar = cursor.var(int, arraysize=len(parentIdsToDelete)) + cursor.setinputsizes(None, childIdVar) + cursor.executemany(""" + delete from ChildTable + where ParentId = :1 + returning ChildId into :2""", + [(i,) for i in parentIdsToDelete]) + for ix, parentId in enumerate(parentIdsToDelete): + print("Child IDs deleted for parent ID", parentId, "are", + childIdVar.getvalue(ix)) + +The output would then be:: + + Child IDs deleted for parent ID 20 are [1002, 1003, 1004] + Child IDs deleted for parent ID 30 are [1005, 1006] + Child IDs deleted for parent ID 50 are [1012, 1013, 1014, 1015] + +Note that the bind variable created to accept the returned data must have an +arraysize large enough to hold data for each row that is processed. Also, +the call to :meth:`Cursor.setinputsizes()` binds this variable immediately so +that it does not have to be passed in each row of data. + + +Predefining Memory Areas +======================== + +When multiple rows of data are being processed there is the possibility that +the data is not uniform in type and size. In such cases, cx_Oracle makes some +effort to accommodate such differences. Type determination for each column is +deferred until a value that is not ``None`` is found in the column's data. If +all values in a particular column are ``None``, then cx_Oracle assumes the type +is a string and has a length of 1. cx_Oracle will also adjust the size of the +buffers used to store strings and bytes when a longer value is encountered in +the data. These sorts of operations incur overhead as memory has to be +reallocated and data copied. To eliminate this overhead, using +:meth:`~Cursor.setinputsizes()` tells cx_Oracle about the type and size of the +data that is going to be used. + +Consider the following code: + +.. code-block:: python + + data = [ + ( 110, "Parent 110"), + ( 2000, "Parent 2000"), + ( 30000, "Parent 30000"), + ( 400000, "Parent 400000"), + (5000000, "Parent 5000000") + ] + cursor.setinputsizes(None, 20) + cursor.executemany(""" + insert into ParentTable (ParentId, Description) + values (:1, :2)""", data) + +In this example, without the call to :meth:`~Cursor.setinputsizes()`, cx_Oracle +would perform five allocations of increasing size as it discovered each new, +longer string. However ``cursor.setinputsizes(None, 20)`` tells cx_Oracle that +the maximum size of the strings that will be processed is 20 characters. Since +cx_Oracle allocates memory for each row based on this value, it is best not to +oversize it. The first parameter of ``None`` tells cx_Oracle that its default +processing will be sufficient. + +Loading CSV Files into Oracle Database +====================================== + +The :meth:`Cursor.executemany()` method and `csv module +`__ can be used to +efficiently load CSV (Comma Separated Values) files. For example, consider the +file ``data.csv``:: + + 101,Abel + 154,Baker + 132,Charlie + 199,Delta + . . . + +And the schema: + +.. code-block:: sql + + create table test (id number, name varchar2(25)); + +Instead of looping through each line of the CSV file and inserting it +individually, you can insert batches of records using +:meth:`Cursor.executemany()`: + +.. code-block:: python + + import cx_Oracle + import csv + + . . . + + # Predefine the memory areas to match the table definition + cursor.setinputsizes(None, 25) + + # Adjust the batch size to meet your memory and performance requirements + batch_size = 10000 + + with open('testsp.csv', 'r') as csv_file: + csv_reader = csv.reader(csv_file, delimiter=',') + sql = "insert into test (id,name) values (:1, :2)" + data = [] + for line in csv_reader: + data.append((line[0], line[1])) + if len(data) % batch_size == 0: + cursor.executemany(sql, data) + data = [] + if data: + cursor.executemany(sql, data) + con.commit() diff --git a/doc/src/user_guide/bind.rst b/doc/src/user_guide/bind.rst new file mode 100644 index 00000000..5c18d798 --- /dev/null +++ b/doc/src/user_guide/bind.rst @@ -0,0 +1,768 @@ +.. _bind: + +******************** +Using Bind Variables +******************** + +SQL and PL/SQL statements that pass data to and from Oracle Database should use +placeholders in SQL and PL/SQL statements that mark where data is supplied or +returned. These placeholders are referred to as bind variables or bind +parameters A bind variable is a colon-prefixed identifier or numeral. For +example, there are two bind variables (``dept_id`` and ``dept_name``) in this +SQL statement: + +.. code-block:: python + + sql = """insert into departments (department_id, department_name) + values (:dept_id, :dept_name)""" + cursor.execute(sql, [280, "Facility"]) + +Using bind variables is important for scalability and security. They help avoid +SQL Injection security problems because data is never treated as part of an +executable statement. Never concatenate or interpolate user data into SQL +statements: + +.. code-block:: python + + did = 280 + dnm = "Facility" + + # !! Never do this !! + sql = f"""insert into departments (department_id, department_name) + values ({did}, {dnm})""" + cursor.execute(sql) + +Bind variables reduce parsing and execution costs when statements are executed +more than once with different data values. If you do not use bind variables, +Oracle must reparse and cache multiple statements. When using bind variables, +Oracle Database may be able to reuse the statement execution plan and context. + +Bind variables can be used to substitute data, but cannot be used to substitute +the text of the statement. You cannot, for example, use a bind variable where +a column name or a table name is required. Bind variables also cannot be used +in Data Definition Language (DDL) statements, such as CREATE TABLE or ALTER +statements. + +Binding By Name or Position +=========================== + +Binding can be done by name or by position. A named bind is performed when the +bind variables in a statement are associated with a name. For example: + +.. code-block:: python + + cursor.execute(""" + insert into departments (department_id, department_name) + values (:dept_id, :dept_name)""", dept_id=280, + dept_name="Facility") + + # alternatively, the parameters can be passed as a dictionary instead of as + # keyword parameters + data = { dept_id=280, dept_name="Facility" } + cursor.execute(""" + insert into departments (department_id, department_name) + values (:dept_id, :dept_name)""", data) + +In the above example, the keyword parameter names or the keys of the dictionary +must match the bind variable names. The advantages of this approach are that +the location of the bind variables in the statement is not important, the +names can be meaningful and the names can be repeated while still only +supplying the value once. + +A positional bind is performed when a list of bind values are passed to the +execute() call. For example: + +.. code-block:: python + + cursor.execute(""" + insert into departments (department_id, department_name) + values (:dept_id, :dept_name)""", [280, "Facility"]) + +Note that for SQL statements, the order of the bind values must exactly match +the order of each bind variable and duplicated names must have their values +repeated. For PL/SQL statements, however, the order of the bind values must +exactly match the order of each **unique** bind variable found in the PL/SQL +block and values should not be repeated. In order to avoid this difference, +binding by name is recommended when bind variable names are repeated. + + +Bind Direction +============== + +The caller can supply data to the database (IN), the database can return +data to the caller (OUT) or the caller can supply initial data to the +database and the database can supply the modified data back to the caller +(IN/OUT). This is known as the bind direction. + +The examples shown above have all supplied data to the database and are +therefore classified as IN bind variables. In order to have the database return +data to the caller, a variable must be created. This is done by calling the +method :func:`Cursor.var()`, which identifies the type of data that will be +found in that bind variable and its maximum size among other things. + +Here is an example showing how to use OUT binds. It calculates the sum of the +integers 8 and 7 and stores the result in an OUT bind variable of type integer: + +.. code-block:: python + + outVal = cursor.var(int) + cursor.execute(""" + begin + :outVal := :inBindVar1 + :inBindVar2; + end;""", outVal=outVal, inBindVar1=8, inBindVar2=7) + print(outVal.getvalue()) # will print 15 + +If instead of simply getting data back you wish to supply an initial value to +the database, you can set the variable's initial value. This example is the +same as the previous one but it sets the initial value first: + +.. code-block:: python + + inOutVal = cursor.var(int) + inOutVal.setvalue(0, 25) + cursor.execute(""" + begin + :inOutBindVar := :inOutBindVar + :inBindVar1 + :inBindVar2; + end;""", inOutBindVar=inOutVal, inBindVar1=8, inBindVar2=7) + print(inOutVal.getvalue()) # will print 40 + +When binding data to parameters of PL/SQL procedures that are declared as OUT +parameters, it is worth noting that any value that is set in the bind variable +will be ignored. In addition, any parameters declared as IN/OUT that do not +have a value set will start out with a value of ``null``. + + +Binding Null Values +=================== + +In cx_Oracle, null values are represented by the Python singleton ``None``. + +For example: + +.. code-block:: python + + cursor.execute(""" + insert into departments (department_id, department_name) + values (:dept_id, :dept_name)""", dept_id=280, dept_name=None) + +In this specific case, because the ``DEPARTMENT_NAME`` column is defined as a +``NOT NULL`` column, an error will occur:: + + cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_NAME") + + +If this value is bound directly, cx_Oracle assumes it to be a string +(equivalent to a VARCHAR2 column). If you need to use a different Oracle type +you will need to make a call to :func:`Cursor.setinputsizes()` or create a bind +variable with the correct type by calling :func:`Cursor.var()`. + + +Binding ROWID Values +==================== + +The pseudo-column ``ROWID`` uniquely identifies a row within a table. In +cx_Oracle, ROWID values are represented as strings. The example below shows +fetching a row and then updating that row by binding its rowid: + +.. code-block:: python + + # fetch the row + cursor.execute(""" + select rowid, manager_id + from departments + where department_id = :dept_id""", dept_id=280) + rowid, manager_id = cursor.fetchone() + + # update the row by binding ROWID + cursor.execute(""" + update departments set + manager_id = :manager_id + where rowid = :rid""", manager_id=205, rid=rowid) + + +DML RETURNING Bind Variables +============================ + +When a RETURNING clause is used with a DML statement like UPDATE, +INSERT, or DELETE, the values are returned to the application through +the use of OUT bind variables. Consider the following example: + +.. code-block:: python + + # The RETURNING INTO bind variable is a string + dept_name = cursor.var(str) + + cursor.execute(""" + update departments set + location_id = :loc_id + where department_id = :dept_id + returning department_name into :dept_name""", + loc_id=1700, dept_id=50, dept_name=dept_name) + print(dept_name.getvalue()) # will print ['Shipping'] + +In the above example, since the WHERE clause matches only one row, the output +contains a single item in the list. If the WHERE clause matched multiple rows, +however, the output would contain as many items as there were rows that were +updated. + +No duplicate binds are allowed in a DML statement with a RETURNING clause, and +no duplication is allowed between bind variables in the DML section and the +RETURNING section of the statement. + + +LOB Bind Variables +================== + +Database CLOBs, NCLOBS, BLOBs and BFILEs can be bound with types +:attr:`cx_Oracle.CLOB`, :attr:`cx_Oracle.NCLOB`, :attr:`cx_Oracle.BLOB` +and :attr:`cx_Oracle.BFILE` respectively. LOBs fetched from the database or +created with :meth:`Connection.createlob()` can also be bound. + +LOBs may represent Oracle Database persistent LOBs (those stored in tables) or +temporary LOBs (such as those created with :meth:`Connection.createlob()` or +returned by some SQL and PL/SQL operations). + +LOBs can be used as IN, OUT or IN/OUT bind variables. + +See :ref:`lobdata` for examples. + +.. _refcur: + +REF CURSOR Bind Variables +========================= + +cx_Oracle provides the ability to bind and define PL/SQL REF cursors. As an +example, consider the PL/SQL procedure: + +.. code-block:: sql + + CREATE OR REPLACE PROCEDURE find_employees ( + p_query IN VARCHAR2, + p_results OUT SYS_REFCURSOR + ) AS + BEGIN + OPEN p_results FOR + SELECT employee_id, first_name, last_name + FROM employees + WHERE UPPER(first_name || ' ' || last_name || ' ' || email) + LIKE '%' || UPPER(p_query) || '%'; + END; + / + +A newly opened cursor can be bound to the REF CURSOR parameter, as shown in the +following Python code. After the PL/SQL procedure has been called with +:meth:`Cursor.callproc()`, the cursor can then be fetched just like any other +cursor which had executed a SQL query: + +.. code-block:: python + + refCursor = connection.cursor() + cursor.callproc("find_employees", ['Smith', refCursor]) + for row in refCursor: + print(row) + +With Oracle's `sample HR schema +`__ there are two +employees with the last name 'Smith' so the result is:: + + (159, 'Lindsey', 'Smith') + (171, 'William', 'Smith') + +To return a REF CURSOR from a PL/SQL function, use ``cx_Oracle.CURSOR`` for the +return type of :meth:`Cursor.callfunc()`: + +.. code-block:: python + + refCursor = cursor.callfunc('example_package.f_get_cursor', cx_Oracle.CURSOR) + for row in refCursor: + print(row) + +Binding PL/SQL Collections +========================== + +PL/SQL Collections like Associative Arrays can be bound as IN, OUT, and IN/OUT +variables. When binding IN values, an array can be passed directly as shown in +this example, which sums up the lengths of all of the strings in the provided +array. First the PL/SQL package definition: + +.. code-block:: sql + + create or replace package mypkg as + + type udt_StringList is table of varchar2(100) index by binary_integer; + + function DemoCollectionIn ( + a_Values udt_StringList + ) return number; + + end; + / + + create or replace package body mypkg as + + function DemoCollectionIn ( + a_Values udt_StringList + ) return number is + t_ReturnValue number := 0; + begin + for i in 1..a_Values.count loop + t_ReturnValue := t_ReturnValue + length(a_Values(i)); + end loop; + return t_ReturnValue; + end; + + end; + / + +Then the Python code: + +.. code-block:: python + + values = ["String One", "String Two", "String Three"] + returnVal = cursor.callfunc("mypkg.DemoCollectionIn", int, [values]) + print(returnVal) # will print 32 + +In order get values back from the database, a bind variable must be created +using :meth:`Cursor.arrayvar()`. The first parameter to this method is a Python +type that cx_Oracle knows how to handle or one of the cx_Oracle :ref:`types`. +The second parameter is the maximum number of elements that the array can hold +or an array providing the value (and indirectly the maximum length). The final +parameter is optional and only used for strings and bytes. It identifies the +maximum length of the strings and bytes that can be stored in the array. If not +specified, the length defaults to 4000 bytes. + +Consider the following PL/SQL package: + +.. code-block:: sql + + create or replace package mypkg as + + type udt_StringList is table of varchar2(100) index by binary_integer; + + procedure DemoCollectionOut ( + a_NumElements number, + a_Values out nocopy udt_StringList + ); + + procedure DemoCollectionInOut ( + a_Values in out nocopy udt_StringList + ); + + end; + / + + create or replace package body mypkg as + + procedure DemoCollectionOut ( + a_NumElements number, + a_Values out nocopy udt_StringList + ) is + begin + for i in 1..a_NumElements loop + a_Values(i) := 'Demo out element #' || to_char(i); + end loop; + end; + + procedure DemoCollectionInOut ( + a_Values in out nocopy udt_StringList + ) is + begin + for i in 1..a_Values.count loop + a_Values(i) := 'Converted element #' || to_char(i) || + ' originally had length ' || length(a_Values(i)); + end loop; + end; + + end; + / + +The Python code to process an OUT collection would look as follows. Note the +call to :meth:`Cursor.arrayvar()` which creates space for an array of strings. +Each string would permit up to 100 bytes and only 10 strings would be +permitted. If the PL/SQL block exceeds the maximum number of strings allowed +the error ``ORA-06513: PL/SQL: index for PL/SQL table out of range for host +language array`` would be raised. + +.. code-block:: python + + outArrayVar = cursor.arrayvar(str, 10, 100) + cursor.callproc("mypkg.DemoCollectionOut", [5, outArrayVar]) + for val in outArrayVar.getvalue(): + print(val) + +This would produce the following output:: + + Demo out element #1 + Demo out element #2 + Demo out element #3 + Demo out element #4 + Demo out element #5 + +The Python code to process an IN/OUT collections is similar. Note the different +call to :meth:`Cursor.arrayvar()` which creates space for an array of strings, +but uses an array to determine both the maximum length of the array and its +initial value. + +.. code-block:: python + + inValues = ["String One", "String Two", "String Three", "String Four"] + inOutArrayVar = cursor.arrayvar(str, inValues) + cursor.callproc("mypkg.DemoCollectionInOut", [inOutArrayVar]) + for val in inOutArrayVar.getvalue(): + print(val) + +This would produce the following output:: + + Converted element #1 originally had length 10 + Converted element #2 originally had length 10 + Converted element #3 originally had length 12 + Converted element #4 originally had length 11 + +If an array variable needs to have an initial value but also needs to allow +for more elements than the initial value contains, the following code can be +used instead: + +.. code-block:: python + + inOutArrayVar = cursor.arrayvar(str, 10, 100) + inOutArrayVar.setvalue(0, ["String One", "String Two"]) + +All of the collections that have been bound in preceding examples have used +contiguous array elements. If an associative array with sparse array elements +is needed, a different approach is required. Consider the following PL/SQL +code: + +.. code-block:: sql + + create or replace package mypkg as + + type udt_StringList is table of varchar2(100) index by binary_integer; + + procedure DemoCollectionOut ( + a_Value out nocopy udt_StringList + ); + + end; + / + + create or replace package body mypkg as + + procedure DemoCollectionOut ( + a_Value out nocopy udt_StringList + ) is + begin + a_Value(-1048576) := 'First element'; + a_Value(-576) := 'Second element'; + a_Value(284) := 'Third element'; + a_Value(8388608) := 'Fourth element'; + end; + + end; + / + +Note that the collection element indices are separated by large values. The +technique used above would fail with the exception ``ORA-06513: PL/SQL: index +for PL/SQL table out of range for host language array``. The code required to +process this collection looks like this instead: + +.. code-block:: python + + collectionType = connection.gettype("MYPKG.UDT_STRINGLIST") + collection = collectionType.newobject() + cursor.callproc("mypkg.DemoCollectionOut", [collection]) + print(collection.aslist()) + +This produces the output:: + + ['First element', 'Second element', 'Third element', 'Fourth element'] + +Note the use of :meth:`Object.aslist()` which returns the collection element +values in index order as a simple Python list. The indices themselves are lost +in this approach. Starting from cx_Oracle 7.0, the associative array can be +turned into a Python dictionary using :meth:`Object.asdict()`. If that value +was printed in the previous example instead, the output would be:: + + {-1048576: 'First element', -576: 'Second element', 284: 'Third element', 8388608: 'Fourth element'} + +If the elements need to be traversed in index order, the methods +:meth:`Object.first()` and :meth:`Object.next()` can be used. The method +:meth:`Object.getelement()` can be used to acquire the element at a particular +index. This is shown in the following code: + +.. code-block:: python + + ix = collection.first() + while ix is not None: + print(ix, "->", collection.getelement(ix)) + ix = collection.next(ix) + +This produces the output:: + + -1048576 -> First element + -576 -> Second element + 284 -> Third element + 8388608 -> Fourth element + +Similarly, the elements can be traversed in reverse index order using the +methods :meth:`Object.last()` and :meth:`Object.prev()` as shown in the +following code: + +.. code-block:: python + + ix = collection.last() + while ix is not None: + print(ix, "->", collection.getelement(ix)) + ix = collection.prev(ix) + +This produces the output:: + + 8388608 -> Fourth element + 284 -> Third element + -576 -> Second element + -1048576 -> First element + + +Binding PL/SQL Records +====================== + +PL/SQL record type objects can also be bound for IN, OUT and IN/OUT +bind variables. For example: + +.. code-block:: sql + + create or replace package mypkg as + + type udt_DemoRecord is record ( + NumberValue number, + StringValue varchar2(30), + DateValue date, + BooleanValue boolean + ); + + procedure DemoRecordsInOut ( + a_Value in out nocopy udt_DemoRecord + ); + + end; + / + + create or replace package body mypkg as + + procedure DemoRecordsInOut ( + a_Value in out nocopy udt_DemoRecord + ) is + begin + a_Value.NumberValue := a_Value.NumberValue * 2; + a_Value.StringValue := a_Value.StringValue || ' (Modified)'; + a_Value.DateValue := a_Value.DateValue + 5; + a_Value.BooleanValue := not a_Value.BooleanValue; + end; + + end; + / + +Then this Python code can be used to call the stored procedure which will +update the record: + +.. code-block:: python + + # create and populate a record + recordType = connection.gettype("MYPKG.UDT_DEMORECORD") + record = recordType.newobject() + record.NUMBERVALUE = 6 + record.STRINGVALUE = "Test String" + record.DATEVALUE = datetime.datetime(2016, 5, 28) + record.BOOLEANVALUE = False + + # show the original values + print("NUMBERVALUE ->", record.NUMBERVALUE) + print("STRINGVALUE ->", record.STRINGVALUE) + print("DATEVALUE ->", record.DATEVALUE) + print("BOOLEANVALUE ->", record.BOOLEANVALUE) + print() + + # call the stored procedure which will modify the record + cursor.callproc("mypkg.DemoRecordsInOut", [record]) + + # show the modified values + print("NUMBERVALUE ->", record.NUMBERVALUE) + print("STRINGVALUE ->", record.STRINGVALUE) + print("DATEVALUE ->", record.DATEVALUE) + print("BOOLEANVALUE ->", record.BOOLEANVALUE) + +This will produce the following output:: + + NUMBERVALUE -> 6 + STRINGVALUE -> Test String + DATEVALUE -> 2016-05-28 00:00:00 + BOOLEANVALUE -> False + + NUMBERVALUE -> 12 + STRINGVALUE -> Test String (Modified) + DATEVALUE -> 2016-06-02 00:00:00 + BOOLEANVALUE -> True + +Note that when manipulating records, all of the attributes must be set by the +Python program in order to avoid an Oracle Client bug which will result in +unexpected values or the Python application segfaulting. + +.. _spatial: + +Binding Spatial Datatypes +========================= + +Oracle Spatial datatypes objects can be represented by Python objects +and its attribute values can be read and updated. The objects can +further be bound and committed to database. See the `GitHub sample +`__ for an example. + +.. _inputtypehandlers: + +Changing Bind Data Types using an Input Type Handler +==================================================== + +Input Type Handlers allow applications to change how data is bound to +statements, or even to enable new types to be bound directly. + +An input type handler is enabled by setting the attribute +:attr:`Cursor.inputtypehandler` or :attr:`Connection.inputtypehandler`. + +Input type handlers can be combined with variable converters to bind Python +objects seamlessly: + +.. code-block:: python + + # A standard Python object + class Building(object): + def __init__(self, buildingId, description, numFloors, dateBuilt): + self.buildingId = buildingId + self.description = description + self.numFloors = numFloors + self.dateBuilt = dateBuilt + + building = Building(1, "Skyscraper 1", 5, datetime.date(2001, 5, 24)) + + # Get Python representation of the Oracle user defined type UDT_BUILDING + objType = con.gettype("UDT_BUILDING") + + # convert a Python Building object to the Oracle user defined type UDT_BUILDING + def BuildingInConverter(value): + obj = objType.newobject() + obj.BUILDINGID = value.buildingId + obj.DESCRIPTION = value.description + obj.NUMFLOORS = value.numFloors + obj.DATEBUILT = value.dateBuilt + return obj + + def InputTypeHandler(cursor, value, numElements): + if isinstance(value, Building): + return cursor.var(cx_Oracle.OBJECT, arraysize = numElements, + inconverter = BuildingInConverter, typename = objType.name) + + + # With the input type handler, the bound Python object is converted + # to the required Oracle object before being inserted + cur.inputtypehandler = InputTypeHandler + cur.execute("insert into myTable values (:1, :2)", (1, building)) + + +Binding Multiple Values to a SQL WHERE IN Clause +================================================ + +To use an IN clause with multiple values in a WHERE clause, you must define and +bind multiple values. You cannot bind an array of values. For example: + +.. code-block:: python + + cursor.execute(""" + select employee_id, first_name, last_name + from employees + where last_name in (:name1, :name2)""", + name1="Smith", name2="Taylor") + for row in cursor: + print(row) + +This will produce the following output:: + + (159, 'Lindsey', 'Smith') + (171, 'William', 'Smith') + (176, 'Jonathon', 'Taylor') + (180, 'Winston', 'Taylor') + +If this sort of query is executed multiple times with differing numbers of +values, a bind variable should be included for each possible value up to the +maximum number of values that can be provided. Missing values can be bound with +the value ``None``. For example, if the query above is used for up to 5 values, +the code should be adjusted as follows: + +.. code-block:: python + + cursor.execute(""" + select employee_id, first_name, last_name + from employees + where last_name in (:name1, :name2, :name3, :name4, :name5)""", + name1="Smith", name2="Taylor", name3=None, name4=None, name5=None) + for row in cursor: + print(row) + +This will produce the same output as the original example. + +If the number of values is only going to be known at runtime, then a SQL +statement can be built up as follows: + +.. code-block:: python + + bindValues = ["Gates", "Marvin", "Fay"] + bindNames = [":" + str(i + 1) for i in range(len(bindValues))] + sql = "select employee_id, first_name, last_name from employees " + \ + "where last_name in (%s)" % (",".join(bindNames)) + cursor.execute(sql, bindValues) + for row in cursor: + print(row) + +Another solution for a larger number of values is to construct a SQL +statement like:: + + SELECT ... WHERE col IN ( ) + +The easiest way to do the '' +will depend on how the data is initially represented and the number of +items. You might look at using CONNECT BY or nested tables. Or, +for really large numbers of items, you might prefer to use a global +temporary table. + +Binding Column and Table Names +============================== + +Column and table names cannot be bound in SQL queries. You can concatenate +text to build up a SQL statement, but make sure you use a white-list or other +means to validate the data in order to avoid SQL Injection security issues: + +.. code-block:: python + + tableWhiteList = ['employees', 'departments'] + tableName = getTableName() # get the table name from user input + if tableName not in tableWhiteList: + raise Exception('Invalid table name') + sql = 'select * from ' + tableName + +Binding column names can be done either by using the above method or by using a +CASE statement. The example below demonstrates binding a column name in an +ORDER BY clause: + +.. code-block:: python + + sql = """ + SELECT * FROM departments + ORDER BY + CASE :bindvar + WHEN 'department_id' THEN DEPARTMENT_ID + ELSE MANAGER_ID + END""" + + columnName = getColumnName() # Obtain a column name from the user + cursor.execute(sql, [colname]) + +Depending on the name provided by the user, the query results will be +ordered either by the column ``DEPARTMENT_ID`` or the column ``MANAGER_ID``. diff --git a/doc/src/user_guide/connection_handling.rst b/doc/src/user_guide/connection_handling.rst new file mode 100644 index 00000000..22fc9615 --- /dev/null +++ b/doc/src/user_guide/connection_handling.rst @@ -0,0 +1,1360 @@ +.. _connhandling: + +***************************** +Connecting to Oracle Database +***************************** + +This chapter covers connecting to Oracle Database using cx_Oracle. It +explains the various forms of connections and how to manage them. + +Establishing Database Connections +================================= + +There are two ways to connect to Oracle Database using cx_Oracle: + +* **Standalone connections** + + These are useful when the application maintains a single user + session to a database. Connections are created by + :meth:`cx_Oracle.connect()` or its alias + :meth:`cx_Oracle.Connection()`. + +* **Pooled connections** + + Connection pooling is important for performance when applications + frequently connect and disconnect from the database. Oracle high + availability features in the pool implementation mean that small + pools can also be useful for applications that want a few + connections available for infrequent use. Pools are created with + :meth:`cx_Oracle.SessionPool()` and then + :meth:`SessionPool.acquire()` can be called to obtain a connection + from a pool. + +Optional connection creation parameters allow you to utilize features +such as Sharding and `Database Resident Connection Pooling (DRCP)`_. + +Once a connection is established, you can use it for SQL, PL/SQL and +SODA. + +**Example: Standalone Connection to Oracle Database** + +.. code-block:: python + + import cx_Oracle + + userpwd = ". . ." # Obtain password string from a user prompt or environment variable + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", encoding="UTF-8") + +cx_Oracle also supports :ref:`external authentication ` so +passwords do not need to be in the application. + + +Closing Connections +=================== + +Connections should be released when they are no longer needed by calling +:meth:`Connection.close()`. Alternatively, you may prefer to let connections +be automatically cleaned up when references to them go out of scope. This lets +cx_Oracle close dependent resources in the correct order. One other approach is +the use of a "with" block, which ensures that a connection is closed once the +block is completed. For example: + +.. code-block:: python + + with cx_Oracle.connect(userName, password, "dbhost.example.com/orclpdb1", + encoding="UTF-8") as connection: + cursor = connection.cursor() + cursor.execute("insert into SomeTable values (:1, :2)", + (1, "Some string")) + connection.commit() + +This code ensures that, once the block is completed, the connection is closed +and resources have been reclaimed by the database. In addition, any attempt to +use the variable ``connection`` outside of the block will simply fail. + + +.. _envset: + +Oracle Environment Variables +============================ + +Before running Python, ensure that any necessary Oracle environment +variables are configured correctly. The variables needed by cx_Oracle +depend on how Python is installed, how you connect to the database, +and what optional settings are desired. + +.. list-table:: Common Oracle environment variables + :header-rows: 1 + :widths: 1 2 + :align: left + + * - Oracle Environment Variables + - Purpose + * - ORACLE_HOME + - The directory containing the Oracle Database software. The directory + and various configuration files must be readable by the Python process. + This variable should not be set if you are using Oracle Instant Client. + * - LD_LIBRARY_PATH + - The library search path for platforms like Linux should include the + Oracle libraries, for example ``$ORACLE_HOME/lib`` or + ``/opt/instantclient_19_3``. This variable is not needed if the + libraries are located by an alternative method, such as with + ``ldconfig``. On other UNIX platforms you may need to set an OS + specific equivalent, such as ``LIBPATH`` or ``SHLIB_PATH``. + * - PATH + - The library search path for Windows should include the location where + ``OCI.DLL`` is found. + * - TNS_ADMIN + - The directory of Oracle Database client configuration files such as + ``tnsnames.ora`` and ``sqlnet.ora``. Needed if the configuration files + are in a non-default location. See :ref:`optnetfiles`." + * - NLS_LANG + - Determines the 'national language support' globalization options for + cx_Oracle. If not set, a default value will be chosen by Oracle. See + :ref:`globalization`." + * - NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT + - Often set in Python applications to force a consistent date format + independent of the locale. The variables are ignored if the environment + variable ``NLS_LANG`` is not set. + +It is recommended to set Oracle variables in the environment before +invoking Python. However, they may also be set in application code with +``os.putenv()`` before the first connection is established. Note that setting +operating system variables such as ``LD_LIBRARY_PATH`` must be done +before running Python. + + +Optional Oracle Configuration Files +=================================== + +.. _optnetfiles: + +Optional Oracle Net Configuration Files +--------------------------------------- + +Optional Oracle Net configuration files affect connections and +applications. + +Common files include: + +* ``tnsnames.ora``: A configuration file that defines databases addresses + for establishing connections. See :ref:`Net Service Name for Connection + Strings `. + +* ``sqlnet.ora``: A profile configuration file that may contain information + on features such as connection failover, network encryption, logging, and + tracing. See `Oracle Net Services Reference + `__ for more information. + +* ``cwallet.sso``: an Oracle wallet for secure connection. + +The default location for these files is the ``network/admin`` +directory under the Oracle Instant Client installation directory or the +``$ORACLE_HOME`` directory (for full database or client installations). To use +a non-default location, put the files in a directory that is accessible to +Python and set the ``TNS_ADMIN`` environment variable to +that directory path. For example, if the file +``/etc/my-oracle-config/tnsnames.ora`` is being used, set the +``TNS_ADMIN`` environment variable to ``/etc/my-oracle-config``. + +.. _optclientfiles: + +Optional Oracle Client Configuration Files +------------------------------------------ + +When cx_Oracle uses Oracle Database Clients 12.1, or later, an optional client +parameter file called ``oraaccess.xml`` can be used. This file can be used to +override some application settings, which can be useful if the application +cannot be altered. The file also enables auto-tuning of the client statement +cache. + +The file is read from the same directory as the +`Optional Oracle Net Configuration Files`_. + +A sample ``oraaccess.xml`` file that sets the Oracle client ‘prefetch’ +value to 50 rows and the 'client statement cache' value to 1, is shown +below:: + + + + + 50 + + + 1 + + + + +Refer to the documentation on `oraaccess.xml +`__ +for more details. + +.. _connstr: + +Connection Strings +================== + +The data source name parameter ``dsn`` of :meth:`cx_Oracle.connect()` and +:meth:`cx_Oracle.SessionPool()` is the Oracle Database connection string +identifying which database service to connect to. The ``dsn`` string can be one +of: + +* An Oracle Easy Connect string +* An Oracle Net Connect Descriptor string +* A Net Service Name mapping to a connect descriptor + +For more information about naming methods, see `Oracle Net Service Reference `__. + +.. _easyconnect: + +Easy Connect Syntax for Connection Strings +------------------------------------------ + +An Easy Connect string is often the simplest connection string to use for the +data source name parameter ``dsn`` of :meth:`cx_Oracle.connect()` and +:meth:`cx_Oracle.SessionPool()`. This method does not need configuration files +such as ``tnsnames.ora``. + +For example, to connect to the Oracle Database service ``orclpdb1`` that is +running on the host ``dbhost.example.com`` with the default Oracle +Database port 1521, use:: + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", + encoding="UTF-8") + +If the database is using a non-default port, it must be specified:: + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com:1984/orclpdb1", + encoding="UTF-8") + +The Easy Connect syntax supports Oracle Database service names. It cannot be +used with the older System Identifiers (SID). + +The Easy Connect syntax has been extended in recent versions of Oracle Database +client since its introduction in 10g. Check the Easy Connect Naming method in +`Oracle Net Service Administrator's Guide +`__ for the syntax to use in your +version of the Oracle Client libraries. + +If you are using Oracle Client 19c, the latest `Easy Connect Plus +`__ syntax allows the use of +multiple hosts or ports, along with optional entries for the wallet location, +the distinguished name of the database server, and even lets some network +configuration options be set. This means that a :ref:`sqlnet.ora ` +file is not needed for some common connection scenarios. + +Oracle Net Connect Descriptor Strings +------------------------------------- + +The :meth:`cx_Oracle.makedsn()` function can be used to construct a connect +descriptor string for the data source name parameter ``dsn`` of +:meth:`cx_Oracle.connect()` and :meth:`cx_Oracle.SessionPool()`. The +:meth:`~cx_Oracle.makedsn()` function accepts the database hostname, the port +number, and the service name. It also supports :ref:`sharding ` +syntax. + +For example, to connect to the Oracle Database service ``orclpdb1`` that is +running on the host ``dbhost.example.com`` with the default Oracle +Database port 1521, use:: + + dsn = cx_Oracle.makedsn("dbhost.example.com", 1521, service_name="orclpdb1") + connection = cx_Oracle.connect("hr", userpwd, dsn, encoding="UTF-8") + +Note the use of the named argument ``service_name``. By default, the third +parameter of :meth:`~cx_Oracle.makedsn()` is a database System Identifier (SID), +not a service name. However, almost all current databases use service names. + +The value of ``dsn`` in this example is the connect descriptor string:: + + (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclpdb1))) + +You can manually create similar connect descriptor strings. This lets you +extend the syntax, for example to support failover. These strings can be +embedded directly in the application:: + + dsn = """(DESCRIPTION= + (FAILOVER=on) + (ADDRESS_LIST= + (ADDRESS=(PROTOCOL=tcp)(HOST=sales1-svr)(PORT=1521)) + (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-svr)(PORT=1521))) + (CONNECT_DATA=(SERVICE_NAME=sales.example.com)))""" + + connection = cx_Oracle.connect("hr", userpwd, dsn, encoding="UTF-8") + +.. _netservice: + +Net Service Names for Connection Strings +---------------------------------------- + +Connect Descriptor Strings are commonly stored in a :ref:`tnsnames.ora +` file and associated with a Net Service Name. This name can be +used directly for the data source name parameter ``dsn`` of +:meth:`cx_Oracle.connect()` and :meth:`cx_Oracle.SessionPool()`. For example, +given a ``tnsnames.ora`` file with the following contents:: + + ORCLPDB1 = + (DESCRIPTION = + (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521)) + (CONNECT_DATA = + (SERVER = DEDICATED) + (SERVICE_NAME = orclpdb1) + ) + ) + +then you could connect using the following code:: + + connection = cx_Oracle.connect("hr", userpwd, "orclpdb1", encoding="UTF-8") + +For more information about Net Service Names, see +`Database Net Services Reference +`__. + +JDBC and Oracle SQL Developer Connection Strings +------------------------------------------------ + +The cx_Oracle connection string syntax is different to Java JDBC and the common +Oracle SQL Developer syntax. If these JDBC connection strings reference a +service name like:: + + jdbc:oracle:thin:@hostname:port/service_name + +for example:: + + jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb1 + +then use Oracle's Easy Connect syntax in cx_Oracle:: + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com:1521/orclpdb1", encoding="UTF-8") + +Alternatively, if a JDBC connection string uses an old-style Oracle SID "system +identifier", and the database does not have a service name:: + + jdbc:oracle:thin:@hostname:port:sid + +for example:: + + jdbc:oracle:thin:@dbhost.example.com:1521:orcl + +then a connect descriptor string from ``makedsn()`` can be used in the +application:: + + dsn = cx_Oracle.makedsn("dbhost.example.com", 1521, sid="orcl") + connection = cx_Oracle.connect("hr", userpwd, dsn, encoding="UTF-8") + +Alternatively, create a ``tnsnames.ora`` (see :ref:`optnetfiles`) entry, for +example:: + + finance = + (DESCRIPTION = + (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521)) + (CONNECT_DATA = + (SID = ORCL) + ) + ) + +This can be referenced in cx_Oracle:: + + connection = cx_Oracle.connect("hr", userpwd, "finance", encoding="UTF-8") + +.. _connpool: + +Connection Pooling +================== + +cx_Oracle's connection pooling lets applications create and maintain a pool of +connections to the database. The internal implementation uses Oracle's +`session pool technology `__. +In general, each connection in a cx_Oracle connection pool corresponds to one +Oracle session. + +A connection pool is created by calling :meth:`~cx_Oracle.SessionPool()`. This +is generally called during application initialization. Connections can then be +obtained from a pool by calling :meth:`~SessionPool.acquire()`. The initial +pool size and the maximum pool size are provided at the time of pool creation. +When the pool needs to grow, new connections are created automatically. The +pool can shrink back to the minimum size when connections are no longer in use. +See `Connection Pooling`_ for more information. + +Connections acquired from the pool should be released back to the pool using +:meth:`SessionPool.release()` or :meth:`Connection.close()` when they are no +longer required. Otherwise, they will be released back to the pool +automatically when all of the variables referencing the connection go out of +scope. The session pool can be completely closed using +:meth:`SessionPool.close()`. + +The example below shows how to connect to Oracle Database using a +connection pool: + +.. code-block:: python + + # Create the session pool + pool = cx_Oracle.SessionPool("hr", userpwd, + "dbhost.example.com/orclpdb1", min=2, max=5, increment=1, encoding="UTF-8") + + # Acquire a connection from the pool + connection = pool.acquire() + + # Use the pooled connection + cursor = connection.cursor() + for result in cursor.execute("select * from mytab"): + print(result) + + # Release the connection to the pool + pool.release(connection) + + # Close the pool + pool.close() + +Applications that are using connections concurrently in multiple threads should +set the ``threaded`` parameter to True when creating a connection pool: + +.. code-block:: python + + # Create the session pool + pool = cx_Oracle.SessionPool("hr", userpwd, "dbhost.example.com/orclpdb1", + min=2, max=5, increment=1, threaded=True, encoding="UTF-8") + +See `Threads.py +`__ +for an example. + +The Oracle Real-World Performance Group's general recommendation for connection +pools is use a fixed sized pool. The values of `min` and `max` should be the +same (and `increment` equal to zero). the firewall, `resource manager +`__ +or user profile `IDLE_TIME +`__ +should not expire idle sessions. This avoids connection storms which can +decrease throughput. See `Guideline for Preventing Connection Storms: Use +Static Pools +`__, +which contains details about sizing of pools. + +Session CallBacks for Setting Pooled Connection State +----------------------------------------------------- + +Applications can set "session" state in each connection. Examples of session +state are NLS settings from ``ALTER SESSION`` statements. Pooled connections +will retain their session state after they have been released back to the pool. +However, because pools can grow, or connections in the pool can be recreated, +there is no guarantee a subsequent :meth:`~SessionPool.acquire()` call will +return a database connection that has any particular state. + +The :meth:`~cx_Oracle.SessionPool()` parameter ``sessionCallback`` +enables efficient setting of session state so that connections have a +known session state, without requiring that state to be explicitly set +after each :meth:`~SessionPool.acquire()` call. + +Connections can also be tagged when they are released back to the pool. The +tag is a user-defined string that represents the session state of the +connection. When acquiring connections, a particular tag can be requested. If +a connection with that tag is available, it will be returned. If not, then +another session will be returned. By comparing the actual and requested tags, +applications can determine what exact state a session has, and make any +necessary changes. + +The session callback can be a Python function or a PL/SQL procedure. + +There are three common scenarios for ``sessionCallback``: + +- When all connections in the pool should have the same state, use a + Python callback without tagging. + +- When connections in the pool require different state for different + users, use a Python callback with tagging. + +- When using :ref:`drcp`: use a PL/SQL callback with tagging. + + +**Python Callback** + +If the ``sessionCallback`` parameter is a Python procedure, it will be called +whenever :meth:`~SessionPool.acquire()` will return a newly created database +connection that has not been used before. It is also called when connection +tagging is being used and the requested tag is not identical to the tag in the +connection returned by the pool. + +An example is: + +.. code-block:: python + + # Set the NLS_DATE_FORMAT for a session + def initSession(connection, requestedTag): + cursor = connection.cursor() + cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'") + + # Create the pool with session callback defined + pool = cx_Oracle.SessionPool("hr", userpwd, "orclpdb1", + sessionCallback=initSession, encoding="UTF-8") + + # Acquire a connection from the pool (will always have the new date format) + connection = pool.acquire() + +If needed, the ``initSession()`` procedure is called internally before +``acquire()`` returns. It will not be called when previously used connections +are returned from the pool. This means that the ALTER SESSION does not need to +be executed after every ``acquire()`` call. This improves performance and +scalability. + +In this example tagging was not being used, so the ``requestedTag`` parameter +is ignored. + +**Connection Tagging** + +Connection tagging is used when connections in a pool should have differing +session states. In order to retrieve a connection with a desired state, the +``tag`` attribute in :meth:`~SessionPool.acquire()` needs to be set. + +When cx_Oracle is using Oracle Client libraries 12.2 or later, then cx_Oracle +uses 'multi-property tags' and the tag string must be of the form of one or +more "name=value" pairs separated by a semi-colon, for example +``"loc=uk;lang=cy"``. + +When a connection is requested with a given tag, and a connection with that tag +is not present in the pool, then a new connection, or an existing connection +with cleaned session state, will be chosen by the pool and the session callback +procedure will be invoked. The callback can then set desired session state and +update the connection's tag. However if the ``matchanytag`` parameter of +:meth:`~SessionPool.acquire()` is *True*, then any other tagged connection may +be chosen by the pool and the callback procedure should parse the actual and +requested tags to determine which bits of session state should be reset. + +The example below demonstrates connection tagging: + +.. code-block:: python + + def initSession(connection, requestedTag): + if requestedTag == "NLS_DATE_FORMAT=SIMPLE": + sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'" + elif requestedTag == "NLS_DATE_FORMAT=FULL": + sql = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI'" + cursor = connection.cursor() + cursor.execute(sql) + connection.tag = requestedTag + + pool = cx_Oracle.SessionPool("hr", userpwd, "orclpdb1", + sessionCallback=initSession, encoding="UTF-8") + + # Two connections with different session state: + connection1 = pool.acquire(tag = "NLS_DATE_FORMAT=SIMPLE") + connection2 = pool.acquire(tag = "NLS_DATE_FORMAT=FULL") + +See `SessionCallback.py +`__ for an example. + +**PL/SQL Callback** + +When cx_Oracle uses Oracle Client 12.2 or later, the session callback can also +be the name of a PL/SQL procedure. A PL/SQL callback will be initiated only +when the tag currently associated with a connection does not match the tag that +is requested. A PL/SQL callback is most useful when using :ref:`drcp` because +DRCP does not require a round-trip to invoke a PL/SQL session callback +procedure. + +The PL/SQL session callback should accept two VARCHAR2 arguments: + +.. code-block:: sql + + PROCEDURE myPlsqlCallback ( + requestedTag IN VARCHAR2, + actualTag IN VARCHAR2 + ); + +The logic in this procedure can parse the actual tag in the session that has +been selected by the pool and compare it with the tag requested by the +application. The procedure can then change any state required before the +connection is returned to the application from :meth:`~SessionPool.acquire()`. + +If the ``matchanytag`` attribute of :meth:`~SessionPool.acquire()` is *True*, +then a connection with any state may be chosen by the pool. + +Oracle 'multi-property tags' must be used. The tag string must be of the form +of one or more "name=value" pairs separated by a semi-colon, for example +``"loc=uk;lang=cy"``. + +In cx_Oracle set ``sessionCallback`` to the name of the PL/SQL procedure. For +example: + +.. code-block:: python + + pool = cx_Oracle.SessionPool("hr", userpwd, "dbhost.example.com/orclpdb1:pooled", + sessionCallback="myPlsqlCallback", encoding="UTF-8") + + connection = pool.acquire(tag="NLS_DATE_FORMAT=SIMPLE", + # DRCP options, if you are using DRCP + cclass='MYCLASS', purity=cx_Oracle.ATTR_PURITY_SELF) + +See `SessionCallbackPLSQL.py +`__ for an example. + +.. _connpooltypes: + +Heterogeneous and Homogeneous Connection Pools +---------------------------------------------- + +By default, connection pools are ‘homogeneous’, meaning that all connections +use the same database credentials. However, if the pool option ``homogeneous`` +is False at the time of pool creation, then a ‘heterogeneous’ pool will be +created. This allows different credentials to be used each time a connection +is acquired from the pool with :meth:`~SessionPool.acquire()`. + +**Heterogeneous Pools** + +When a heterogeneous pool is created by setting ``homogeneous`` to False and no +credentials are supplied during pool creation, then a user name and password +may be passed to :meth:`~SessionPool.acquire()` as shown in this example: + +.. code-block:: python + + pool = cx_Oracle.SessionPool(dsn="dbhost.example.com/orclpdb1", homogeneous=False, + encoding="UTF-8") + connection = pool.acquire(user="hr", password=userpwd) + +.. _drcp: + +Database Resident Connection Pooling (DRCP) +=========================================== + +`Database Resident Connection Pooling (DRCP) +`__ enables database resource +sharing for applications that run in multiple client processes, or run on +multiple middle-tier application servers. By default each connection from +Python will use one database server process. DRCP allows pooling of these +server processes. This reduces the amount of memory required on the database +host. The DRCP pool can be shared by multiple applications. + +DRCP is useful for applications which share the same database credentials, have +similar session settings (for example date format settings or PL/SQL package +state), and where the application gets a database connection, works on it for a +relatively short duration, and then releases it. + +Applications can choose whether or not to use pooled connections at runtime. + +For efficiency, it is recommended that DRCP connections should be used +in conjunction with cx_Oracle’s local :ref:`connection pool `. + +**Using DRCP in Python** + +Using DRCP with cx_Oracle applications involves the following steps: + +1. Configuring and enabling DRCP in the database +2. Configuring the application to use a DRCP connection +3. Deploying the application + +**Configuring and enabling DRCP** + +Every instance of Oracle Database uses a single, default connection +pool. The pool can be configured and administered by a DBA using the +``DBMS_CONNECTION_POOL`` package: + +.. code-block:: sql + + EXECUTE DBMS_CONNECTION_POOL.CONFIGURE_POOL( + pool_name => 'SYS_DEFAULT_CONNECTION_POOL', + minsize => 4, + maxsize => 40, + incrsize => 2, + session_cached_cursors => 20, + inactivity_timeout => 300, + max_think_time => 600, + max_use_session => 500000, + max_lifetime_session => 86400) + +Alternatively the method ``DBMS_CONNECTION_POOL.ALTER_PARAM()`` can +set a single parameter: + +.. code-block:: sql + + EXECUTE DBMS_CONNECTION_POOL.ALTER_PARAM( + pool_name => 'SYS_DEFAULT_CONNECTION_POOL', + param_name => 'MAX_THINK_TIME', + param_value => '1200') + +The ``inactivity_timeout`` setting terminates idle pooled servers, helping +optimize database resources. To avoid pooled servers permanently being held +onto by a selfish Python script, the ``max_think_time`` parameter can be set. +The parameters ``num_cbrok`` and ``maxconn_cbrok`` can be used to distribute +the persistent connections from the clients across multiple brokers. This may +be needed in cases where the operating system per-process descriptor limit is +small. Some customers have found that having several connection brokers +improves performance. The ``max_use_session`` and ``max_lifetime_session`` +parameters help protect against any unforeseen problems affecting server +processes. The default values will be suitable for most users. See the +`Oracle DRCP documentation +`__ for details on parameters. + +In general, if pool parameters are changed, the pool should be restarted, +otherwise server processes will continue to use old settings. + +There is a ``DBMS_CONNECTION_POOL.RESTORE_DEFAULTS()`` procedure to +reset all values. + +When DRCP is used with RAC, each database instance has its own connection +broker and pool of servers. Each pool has the identical configuration. For +example, all pools start with ``minsize`` server processes. A single +DBMS_CONNECTION_POOL command will alter the pool of each instance at the same +time. The pool needs to be started before connection requests begin. The +command below does this by bringing up the broker, which registers itself with +the database listener: + +.. code-block:: sql + + EXECUTE DBMS_CONNECTION_POOL.START_POOL() + +Once enabled this way, the pool automatically restarts when the database +instance restarts, unless explicitly stopped with the +``DBMS_CONNECTION_POOL.STOP_POOL()`` command: + +.. code-block:: sql + + EXECUTE DBMS_CONNECTION_POOL.STOP_POOL() + +The pool cannot be stopped while connections are open. + +**Application Deployment for DRCP** + +In order to use DRCP, the ``cclass`` and ``purity`` parameters should +be passed to :meth:`cx_Oracle.connect()` or :meth:`SessionPool.acquire()`. If +``cclass`` is not set, the pooled server sessions will not be reused optimally, +and the DRCP statistic views will record large values for NUM_MISSES. + +The DRCP ``purity`` can be one of ``ATTR_PURITY_NEW``, ``ATTR_PURITY_SELF``, +or ``ATTR_PURITY_DEFAULT``. The value ``ATTR_PURITY_SELF`` allows reuse of +both the pooled server process and session memory, giving maximum benefit from +DRCP. See the Oracle documentation on `benefiting from scalability +`__. + +The connection string used for :meth:`~cx_Oracle.connect()` or +:meth:`~SessionPool.acquire()` must request a pooled server by +following one of the syntaxes shown below: + +Using Oracle’s Easy Connect syntax, the connection would look like: + +.. code-block:: python + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orcl:pooled", + encoding="UTF-8") + +Or if you connect using a Net Service Name named ``customerpool``: + +.. code-block:: python + + connection = cx_Oracle.connect("hr", userpwd, "customerpool", encoding="UTF-8") + +Then only the Oracle Network configuration file ``tnsnames.ora`` needs +to be modified:: + + customerpool = (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) + (HOST=dbhost.example.com) + (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CUSTOMER) + (SERVER=POOLED))) + +If these changes are made and the database is not actually configured for DRCP, +or the pool is not started, then connections will not succeed and an error will +be returned to the Python application. + +Although applications can choose whether or not to use pooled connections at +runtime, care must be taken to configure the database appropriately for the +number of expected connections, and also to stop inadvertent use of non-DRCP +connections leading to a resource shortage. + +The example below shows how to connect to Oracle Database using Database +Resident Connection Pooling: + +.. code-block:: python + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orcl:pooled", + cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF, encoding="UTF-8") + +The example below shows connecting to Oracle Database using DRCP and +cx_Oracle's connection pooling: + +.. code-block:: python + + mypool = cx_Oracle.SessionPool("hr", userpwd, "dbhost.example.com/orcl:pooled", + encoding="UTF-8") + connection = mypool.acquire(cclass="MYCLASS", purity=cx_Oracle.ATTR_PURITY_SELF) + +For more information about DRCP see `Oracle Database Concepts Guide +`__, and for DRCP Configuration +see `Oracle Database Administrator's Guide +`__. + +**Closing Connections** + +Python scripts where cx_Oracle connections do not go out of scope quickly +(which releases them), or do not currently use :meth:`Connection.close()`, +should be examined to see if :meth:`~Connection.close()` can be used, which +then allows maximum use of DRCP pooled servers by the database: + +.. code-block:: python + + # Do some database operations + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1:pooled", + encoding="UTF-8") + . . . + connection.close(); + + # Do lots of non-database work + . . . + + # Do some more database operations + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1:pooled", + encoding="UTF-8") + . . . + connection.close(); + +**Monitoring DRCP** + +Data dictionary views are available to monitor the performance of DRCP. +Database administrators can check statistics such as the number of busy and +free servers, and the number of hits and misses in the pool against the total +number of requests from clients. The views are: + +* ``DBA_CPOOL_INFO`` +* ``V$PROCESS`` +* ``V$SESSION`` +* ``V$CPOOL_STATS`` +* ``V$CPOOL_CC_STATS`` +* ``V$CPOOL_CONN_INFO`` + +**DBA_CPOOL_INFO View** + +``DBA_CPOOL_INFO`` displays configuration information about the DRCP pool. The +columns are equivalent to the ``dbms_connection_pool.configure_pool()`` +settings described in the table of DRCP configuration options, with the +addition of a ``STATUS`` column. The status is ``ACTIVE`` if the pool has been +started and ``INACTIVE`` otherwise. Note the pool name column is called +``CONNECTION_POOL``. This example checks whether the pool has been started and +finds the maximum number of pooled servers:: + + SQL> SELECT connection_pool, status, maxsize FROM dba_cpool_info; + + CONNECTION_POOL STATUS MAXSIZE + ---------------------------- ---------- ---------- + SYS_DEFAULT_CONNECTION_POOL ACTIVE 40 + +**V$PROCESS and V$SESSION Views** + +The ``V$SESSION`` view shows information about the currently active DRCP +sessions. It can also be joined with ``V$PROCESS`` via +``V$SESSION.PADDR = V$PROCESS.ADDR`` to correlate the views. + +**V$CPOOL_STATS View** + +The ``V$CPOOL_STATS`` view displays information about the DRCP statistics for +an instance. The V$CPOOL_STATS view can be used to assess how efficient the +pool settings are. T his example query shows an application using the pool +effectively. The low number of misses indicates that servers and sessions were +reused. The wait count shows just over 1% of requests had to wait for a pooled +server to become available:: + + NUM_REQUESTS NUM_HITS NUM_MISSES NUM_WAITS + ------------ ---------- ---------- ---------- + 10031 99990 40 1055 + +If ``cclass`` was set (allowing pooled servers and sessions to be +reused) then NUM_MISSES will be low. If the pool maxsize is too small for +the connection load, then NUM_WAITS will be high. + +**V$CPOOL_CC_STATS View** + +The view ``V$CPOOL_CC_STATS`` displays information about the connection class +level statistics for the pool per instance:: + + SQL> SELECT cclass_name, num_requests, num_hits, num_misses + FROM v$cpool_cc_stats; + + CCLASS_NAME NUM_REQUESTS NUM_HITS NUM_MISSES + -------------------------------- ------------ ---------- ---------- + HR.MYCLASS 100031 99993 38 + +**V$CPOOL_CONN_INFO View** + +The ``V$POOL_CONN_INFO`` view gives insight into client processes that are +connected to the connection broker, making it easier to monitor and trace +applications that are currently using pooled servers or are idle. This view was +introduced in Oracle 11gR2. + +You can monitor the view ``V$CPOOL_CONN_INFO`` to, for example, identify +misconfigured machines that do not have the connection class set correctly. +This view maps the machine name to the class name:: + + SQL> SELECT cclass_name, machine FROM v$cpool_conn_info; + + CCLASS_NAME MACHINE + --------------------------------------- ------------ + CJ.OCI:SP:wshbIFDtb7rgQwMyuYvodA cjlinux + . . . + +In this example you would examine applications on ``cjlinux`` and make +sure ``cclass`` is set. + + +.. _proxyauth: + +Connecting Using Proxy Authentication +===================================== + +Proxy authentication allows a user (the "session user") to connect to Oracle +Database using the credentials of a 'proxy user'. Statements will run as the +session user. Proxy authentication is generally used in three-tier applications +where one user owns the schema while multiple end-users access the data. For +more information about proxy authentication, see the `Oracle documentation +`__. + +An alternative to using proxy users is to set +:attr:`Connection.client_identifier` after connecting and use its value in +statements and in the database, for example for :ref:`monitoring +`. + +The following proxy examples use these schemas. The ``mysessionuser`` schema is +granted access to use the password of ``myproxyuser``: + +.. code-block:: sql + + CREATE USER myproxyuser IDENTIFIED BY myproxyuserpw; + GRANT CREATE SESSION TO myproxyuser; + + CREATE USER mysessionuser IDENTIFIED BY itdoesntmatter; + GRANT CREATE SESSION TO mysessionuser; + + ALTER USER mysessionuser GRANT CONNECT THROUGH myproxyuser; + +After connecting to the database, the following query can be used to show the +session and proxy users: + +.. code-block:: sql + + SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), + SYS_CONTEXT('USERENV', 'SESSION_USER') + FROM DUAL; + +Standalone connection examples: + +.. code-block:: python + + # Basic Authentication without a proxy + connection = cx_Oracle.connect("myproxyuser", "myproxyuserpw", "dbhost.example.com/orclpdb1", + encoding="UTF-8") + # PROXY_USER: None + # SESSION_USER: MYPROXYUSER + + # Basic Authentication with a proxy + connection = cx_Oracle.connect(user="myproxyuser[mysessionuser]", "myproxyuserpw", + "dbhost.example.com/orclpdb1", encoding="UTF-8") + # PROXY_USER: MYPROXYUSER + # SESSION_USER: MYSESSIONUSER + +Pooled connection examples: + +.. code-block:: python + + # Basic Authentication without a proxy + pool = cx_Oracle.SessionPool("myproxyuser", "myproxyuser", "dbhost.example.com/orclpdb1", + encoding="UTF-8") + connection = pool.acquire() + # PROXY_USER: None + # SESSION_USER: MYPROXYUSER + + # Basic Authentication with proxy + pool = cx_Oracle.SessionPool("myproxyuser[mysessionuser]", "myproxyuser", + "dbhost.example.com/orclpdb1", homogeneous=False, encoding="UTF-8") + connection = pool.acquire() + # PROXY_USER: MYPROXYUSER + # SESSION_USER: MYSESSIONUSER + +Note the use of a :ref:`heterogeneous ` pool in the example +above. This is required in this scenario. + +.. _extauth: + +Connecting Using External Authentication +======================================== + +Instead of storing the database username and password in Python scripts or +environment variables, database access can be authenticated by an outside +system. External Authentication allows applications to validate user access by +an external password store (such as an Oracle Wallet), by the operating system, +or with an external authentication service. + +Using an Oracle Wallet for External Authentication +-------------------------------------------------- + +The following steps give an overview of using an Oracle Wallet. Wallets should +be kept securely. Wallets can be managed with `Oracle Wallet Manager +`__. + +In this example the wallet is created for the ``myuser`` schema in the directory +``/home/oracle/wallet_dir``. The ``mkstore`` command is available from a full +Oracle client or Oracle Database installation. If you have been given wallet by +your DBA, skip to step 3. + +1. First create a new wallet as the ``oracle`` user:: + + mkstore -wrl "/home/oracle/wallet_dir" -create + + This will prompt for a new password for the wallet. + +2. Create the entry for the database user name and password that are currently + hardcoded in your Python scripts. Use either of the methods shown below. + They will prompt for the wallet password that was set in the first step. + + **Method 1 - Using an Easy Connect string**:: + + mkstore -wrl "/home/oracle/wallet_dir" -createCredential dbhost.example.com/orclpdb1 myuser myuserpw + + **Method 2 - Using a connect name identifier**:: + + mkstore -wrl "/home/oracle/wallet_dir" -createCredential mynetalias myuser myuserpw + + The alias key ``mynetalias`` immediately following the + ``-createCredential`` option will be the connect name to be used in Python + scripts. If your application connects with multiple different database + users, you could create a wallet entry with different connect names for + each. + + You can see the newly created credential with:: + + mkstore -wrl "/home/oracle/wallet_dir" -listCredential + +3. Skip this step if the wallet was created using an Easy Connect String. + Otherwise, add an entry in :ref:`tnsnames.ora ` for the connect + name as follows:: + + mynetalias = + (DESCRIPTION = + (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost.example.com)(PORT = 1521)) + (CONNECT_DATA = + (SERVER = DEDICATED) + (SERVICE_NAME = orclpdb1) + ) + ) + + The file uses the description for your existing database and sets the + connect name alias to ``mynetalias``, which is the identifier used when + adding the wallet entry. + +4. Add the following wallet location entry in the :ref:`sqlnet.ora + ` file, using the ``DIRECTORY`` you created the wallet in:: + + WALLET_LOCATION = + (SOURCE = + (METHOD = FILE) + (METHOD_DATA = + (DIRECTORY = /home/oracle/wallet_dir) + ) + ) + SQLNET.WALLET_OVERRIDE = TRUE + + Examine the Oracle documentation for full settings and values. + +5. Ensure the configuration files are in a default location or set TNS_ADMIN is + set to the directory containing them. See :ref:`optnetfiles`. + +With an Oracle wallet configured, and readable by you, your scripts +can connect using:: + + connection = cx_Oracle.connect(dsn="mynetalias", encoding="UTF-8") + +or:: + + pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False, dsn="mynetalias", + encoding="UTF-8") + pool.acquire() + +The ``dsn`` must match the one used in the wallet. + +After connecting, the query:: + + SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL; + +will show:: + + MYUSER + +.. note:: + + Wallets are also used to configure TLS connections. If you are using a + wallet like this, you may need a database username and password in + :meth:`cx_Oracle.connect()` and :meth:`cx_Oracle.SessionPool()` calls. + +**External Authentication and Proxy Authentication** + +The following examples show external wallet authentication combined with +:ref:`proxy authentication `. These examples use the wallet +configuration from above, with the addition of a grant to another user:: + + ALTER USER mysessionuser GRANT CONNECT THROUGH myuser; + +After connection, you can check who the session user is with: + +.. code-block:: sql + + SELECT SYS_CONTEXT('USERENV', 'PROXY_USER'), + SYS_CONTEXT('USERENV', 'SESSION_USER') + FROM DUAL; + +Standalone connection example: + +.. code-block:: python + + # External Authentication with proxy + connection = cx_Oracle.connect(user="[mysessionuser]", dsn="mynetalias", encoding="UTF-8") + # PROXY_USER: MYUSER + # SESSION_USER: MYSESSIONUSER + +Pooled connection example: + +.. code-block:: python + + # External Authentication with proxy + pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False, dsn="mynetalias", + encoding="UTF-8") + pool.acquire(user="[mysessionuser]") + # PROXY_USER: MYUSER + # SESSION_USER: MYSESSIONUSER + +The following usage is not supported: + +.. code-block:: python + + pool = cx_Oracle.SessionPool("[mysessionuser]", externalauth=True, homogeneous=False, + dsn="mynetalias", encoding="UTF-8") + pool.acquire() + + +Operating System Authentication +------------------------------- + +With Operating System authentication, Oracle allows user authentication to be +performed by the operating system. The following steps give an overview of how +to implement OS Authentication on Linux. + +1. Login to your computer. The commands used in these steps assume the + operating system user name is "oracle". + +2. Login to SQL*Plus as the SYSTEM user and verify the value for the + ``OS_AUTHENT_PREFIX`` parameter:: + + SQL> SHOW PARAMETER os_authent_prefix + + NAME TYPE VALUE + ------------------------------------ ----------- ------------------------------ + os_authent_prefix string ops$ + +3. Create an Oracle database user using the ``os_authent_prefix`` determined in + step 2, and the operating system user name: + + .. code-block:: sql + + CREATE USER ops$oracle IDENTIFIED EXTERNALLY; + GRANT CONNECT, RESOURCE TO ops$oracle; + +In Python, connect using the following code:: + + connection = cx_Oracle.connect(dsn="mynetalias", encoding="UTF-8") + +Your session user will be ``OPS$ORACLE``. + +If your database is not on the same computer as python, you can perform testing +by setting the database configuration parameter ``remote_os_authent=true``. +Beware this is insecure. + +See `Oracle Database Security Guide +`__ for more information about +Operating System Authentication. + +Privileged Connections +====================== + +The ``mode`` parameter of the function :meth:`cx_Oracle.connect()` specifies +the database privilege that you want to associate with the user. + +The example below shows how to connect to Oracle Database as SYSDBA: + +.. code-block:: python + + connection = cx_Oracle.connect("sys", syspwd, "dbhost.example.com/orclpdb1", + mode=cx_Oracle.SYSDBA, encoding="UTF-8") + + cursor = con.cursor() + sql = "GRANT SYSOPER TO hr" + cursor.execute(sql) + +This is equivalent to executing the following in SQL*Plus: + +.. code-block:: sql + + CONNECT sys/syspwd AS SYSDBA + + GRANT SYSOPER TO hr; + + +Starting and Stopping Oracle Database +===================================== + +cx_Oracle has the capability of starting up the database using a privileged +connection. This example shows a script that could be run as the 'oracle' +operating system user who administers a local database installation on Linux. +It assumes that the environment variable ``ORACLE_SID`` has been set to the SID +of the database that should be started: + +.. code-block:: python + + # the connection must be in PRELIM_AUTH mode to perform startup + connection = cx_Oracle.connect("/", + mode = cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH) + connection.startup() + + # the following statements must be issued in normal SYSDBA mode + connection = cx_Oracle.connect("/", mode = cx_Oracle.SYSDBA, encoding="UTF-8") + cursor = connection.cursor() + cursor.execute("alter database mount") + cursor.execute("alter database open") + +Similarly, cx_Oracle has the ability to shutdown the database using a +privileged connection. This example also assumes that the environment variable +``ORACLE_SID`` has been set: + +.. code-block:: python + + # need to connect as SYSDBA or SYSOPER + connection = cx_Oracle.connect("/", mode = cx_Oracle.SYSDBA) + + # first shutdown() call must specify the mode, if DBSHUTDOWN_ABORT is used, + # there is no need for any of the other steps + connection.shutdown(mode = cx_Oracle.DBSHUTDOWN_IMMEDIATE) + + # now close and dismount the database + cursor = connection.cursor() + cursor.execute("alter database close normal") + cursor.execute("alter database dismount") + + # perform the final shutdown call + connection.shutdown(mode = cx_Oracle.DBSHUTDOWN_FINAL) + + +.. _netencrypt: + +Securely Encrypting Network Traffic to Oracle Database +====================================================== + +You can encrypt data transferred between the Oracle Database and the Oracle +client libraries used by cx_Oracle so that unauthorized parties are not able to +view plain text values as the data passes over the network. The easiest +configuration is Oracle’s native network encryption. The standard SSL protocol +can also be used if you have a PKI, but setup is necessarily more involved. + +With native network encryption, the client and database server negotiate a key +using Diffie-Hellman key exchange. This provides protection against +man-in-the-middle attacks. + +Native network encryption can be configured by editing Oracle Net’s optional +:ref:`sqlnet.ora ` configuration file, on either the database +server and/or on each cx_Oracle 'client' machine. Parameters control whether +data integrity checking and encryption is required or just allowed, and which +algorithms the client and server should consider for use. + +As an example, to ensure all connections to the database are checked for +integrity and are also encrypted, create or edit the Oracle Database +``$ORACLE_HOME/network/admin/sqlnet.ora`` file. Set the checksum negotiation +to always validate a checksum and set the checksum type to your desired value. +The network encryption settings can similarly be set. For example, to use the +SHA512 checksum and AES256 encryption use:: + + SQLNET.CRYPTO_CHECKSUM_SERVER = required + SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA512) + SQLNET.ENCRYPTION_SERVER = required + SQLNET.ENCRYPTION_TYPES_SERVER = (AES256) + +If you definitely know that the database server enforces integrity and +encryption, then you do not need to configure cx_Oracle separately. However +you can also, or alternatively, do so depending on your business needs. Create +a ``sqlnet.ora`` on your client machine and locate it with other +:ref:`optnetfiles`:: + + SQLNET.CRYPTO_CHECKSUM_CLIENT = required + SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA512) + SQLNET.ENCRYPTION_CLIENT = required + SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256) + +The client and server sides can negotiate the protocols used if the settings +indicate more than one value is accepted. + +Note that these are example settings only. You must review your security +requirements and read the documentation for your Oracle version. In particular +review the available algorithms for security and performance. + +The ``NETWORK_SERVICE_BANNER`` column of the database view +`V$SESSION_CONNECT_INFO +`__ can be used to verify the +encryption status of a connection. + +For more information on Oracle Data Network Encryption and Integrity, +configuring SSL network encryption and Transparent Data Encryption of +data-at-rest in the database, see `Oracle Database Security Guide +`__. + + +Resetting Passwords +=================== + +After connecting, passwords can be changed by calling +:meth:`Connection.changepassword()`: + +.. code-block:: python + + # Get the passwords from somewhere, such as prompting the user + oldpwd = getpass.getpass("Old Password for %s: " % username) + newpwd = getpass.getpass("New Password for %s: " % username) + + connection.changepassword(oldpwd, newpwd) + +When a password has expired and you cannot connect directly, you can connect +and change the password in one operation by using the ``newpassword`` parameter +of the function :meth:`cx_Oracle.connect()` constructor: + +.. code-block:: python + + # Get the passwords from somewhere, such as prompting the user + oldpwd = getpass.getpass("Old Password for %s: " % username) + newpwd = getpass.getpass("New Password for %s: " % username) + + connection = cx_Oracle.connect(username, oldpwd, "dbhost.example.com/orclpdb1", + newpassword=newpwd, encoding="UTF-8") + +.. _connsharding: + +Connecting to Sharded Databases +=============================== + +The :meth:`cx_Oracle.connect()` and :meth:`SessionPool.acquire()` +functions accept ``shardingkey`` and ``supershardingkey`` parameters +that are a sequence of values used to identify the database shard to +connect to. Currently only strings are supported for the key values. +See `Oracle Sharding +`__ for more information. diff --git a/doc/src/user_guide/cqn.rst b/doc/src/user_guide/cqn.rst new file mode 100644 index 00000000..362c2381 --- /dev/null +++ b/doc/src/user_guide/cqn.rst @@ -0,0 +1,140 @@ +.. _cqn: + +***************************** +Continuous Query Notification +***************************** + +`Continuous Query Notification (CQN) +`__ allows applications to receive +notifications when a table changes, such as when rows have been updated, +regardless of the user or the application that made the change. This can be +useful in many circumstances, such as near real-time monitoring, auditing +applications, or for such purposes as mid-tier cache invalidation. A cache +might hold some values that depend on data in a table. If the data in the +table changes, the cached values must then be updated with the new information. + +CQN notification behavior is widely configurable. Choices include specifying +what types of SQL should trigger a notification, whether notifications should +survive database loss, and control over unsubscription. You can also choose +whether notification messages will include ROWIDs of affected rows. + +By default, object-level (previously known as Database Change Notification) +occurs and the Python notification method is invoked whenever a database +transaction is committed that changes an object that a registered query +references, regardless of whether the actual query result changed. However if +the :meth:`subscription ` option ``qos`` is +:data:`cx_Oracle.SUBSCR_QOS_QUERY` then query-level notification occurs. In +this mode, the database notifies the application whenever a transaction changing +the result of the registered query is committed. + +CQN is best used to track infrequent data changes. + + +Requirements +============ + +Before using CQN, users must have appropriate permissions: + +.. code-block:: sql + + GRANT CHANGE NOTIFICATION TO ; + +To use CQN, connections must have ``events`` mode set to ``True``, for +example: + +.. code-block:: python + + connection = cx_Oracle.connect(userName, password, "dbhost.example.com/orclpdb1", events=True) + +For notifications to be received, the database must be able to connect back to +the application using cx_Oracle. Typically, this means that the machine +running cx_Oracle needs a fixed IP address. Note +:meth:`Connection.subscribe()` does not verify that this reverse connection is +possible. If there is any problem sending a notification, then the callback +method will not be invoked. Configuration options can include an IP address +and port on which to listen for notifications; otherwise, the database chooses +values on its own. + + +Creating a Subscription +======================= + +Subscriptions allow Python to receives notifications for events that take place +in the database that match the given parameters. + +For example, a basic CQN subscription might be created like: + +.. code-block:: python + + connection.subscribe(namespace=cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE, + callback=MyCallback) + +See :meth:`Connection.subscribe()` for details on all of the parameters. + +See :ref:`cqn-operation-codes` for the types of operations that are supported. + +See :ref:`subscr-qos` for the quality of service values that are supported. + +See :ref:`subscr-namespaces` and :ref:`subscr-protocols` for the namespaces and +protocols that are supported. + +See :ref:`subscrobj` for more details on the subscription object that is +created. + + +Registering Queries +=================== + +Once a subscription has been created, one or more queries must be registered by +calling :meth:`Subscription.registerquery()`. Registering a query behaves +similarly to :meth:`Cursor.execute()`, but only queries are permitted and the +``args`` parameter must be a sequence or dictionary. + +An example script to receive query notifications when the 'CUSTOMER' table data +changes is: + +.. code-block:: python + + def CQNCallback(message): + print("Notification:") + for query in message.queries: + for tab in query.tables: + print("Table:", tab.name) + print("Operation:", tab.operation) + for row in tab.rows: + if row.operation & cx_Oracle.OPCODE_INSERT: + print("INSERT of rowid:", row.rowid) + if row.operation & cx_Oracle.OPCODE_DELETE: + print("DELETE of rowid:", row.rowid) + + subscr = connection.subscribe(namespace=cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE, + callback=CQNCallback, + operations=cx_Oracle.OPCODE_INSERT | cx_Oracle.OPCODE_DELETE, + qos = cx_Oracle.SUBSCR_QOS_QUERY | cx_Oracle.SUBSCR_QOS_ROWIDS) + subscr.registerquery("select * from regions") + input("Hit enter to stop CQN demo\n") + +Running the above script, shows the initial output as:: + + Hit enter to stop CQN demo + +Use SQL*Plus or another tool to commit a change to the table: + +.. code-block:: sql + + insert into regions values(120, 'L'); + commit; + +When the commit is executed, a notification will be received by the callback +which should print something like the following:: + + Hit enter to stop CQN demo + Notification: + Table: HR.REGIONS + Operation: 2 + INSERT of rowid: AAA7EsAAHAAAFS/AAA + +See `GitHub Samples +`__ +for a runnable CQN example. diff --git a/doc/src/user_guide/exception_handling.rst b/doc/src/user_guide/exception_handling.rst new file mode 100644 index 00000000..8022ac20 --- /dev/null +++ b/doc/src/user_guide/exception_handling.rst @@ -0,0 +1,40 @@ +.. _exception: + +****************** +Exception Handling +****************** + +All exceptions raised by cx_Oracle are inherited from :attr:`cx_Oracle.Error`. +See :ref:`Exceptions ` for more details on the various exceptions +defined by cx_Oracle. See the exception handling section in the +:ref:`API manual ` for more details on the information available +when an exception is raised. + +Applications can catch exceptions as needed. For example, when trying to add a +customer that already exists in the database, the following could could be used +to catch the exception: + +.. code-block:: python + + try: + cursor.execute("insert into customer values (101, 'Customer A')") + except cx_Oracle.IntegrityError: + print("Customer ID already exists") + else: + print("Customer added") + + +If information about the exception needs to be processed instead, the following +code can be used: + +.. code-block:: python + + try: + cursor.execute("insert into customer values (101, 'Customer A')") + except cx_Oracle.IntegrityError as e: + errorObj, = e.args + print("Customer ID already exists") + print("Error Code:", errorObj.code) + print("Error Message:", errorObj.message) + else: + print("Customer added") diff --git a/doc/src/user_guide/globalization.rst b/doc/src/user_guide/globalization.rst new file mode 100644 index 00000000..1456e15c --- /dev/null +++ b/doc/src/user_guide/globalization.rst @@ -0,0 +1,134 @@ +.. _globalization: + +*************************************************** +Characters Sets and National Language Support (NLS) +*************************************************** + +Data fetched from, and sent to, Oracle Database will be mapped between the +database character set and the "Oracle client" character set of the Oracle +client libraries used by cx_Oracle. If data cannot be correctly mapped between +client and server character sets, then it may be corrupted or queries may fail +with :ref:`"codec can't decode byte" `. Most applications will need +to specify the client character set. + +cx_Oracle uses Oracle’s National Language Support (NLS) to assist in +globalizing applications. As well as character set support, there are many +other features that will be useful in applications. See the +`Database Globalization Support Guide +`__. + + +Setting the Client Character Set +================================ + +You can specify the Oracle client character set used by cx_Oracle by passing the +``encoding`` and ``nencoding`` parameters to the :meth:`cx_Oracle.connect` and +:meth:`cx_Oracle.SessionPool` methods. For example: + +.. code-block:: python + + import cx_Oracle + connection = cx_Oracle.connect(connectString, encoding="UTF-8", + nencoding="UTF-8") + +The ``encoding`` parameter affects character data such as VARCHAR2 and CLOB +columns. The ``nencoding`` parameter affects "National Character" data such as +NVARCHAR2 and NCLOB. If you are not using national character types, then you +can omit ``nencoding``. + +cx_Oracle will first treat the encoding parameter values as `IANA encoding names +`__. If +no name is matched, it will attempt to use `Oracle character set names +`__. For +example, for ``UTF-8`` characters you should use the IANA name "UTF-8" or the +Oracle name "AL32UTF8". Do not accidentally use "UTF8", which Oracle uses to +specify the older Unicode 3.0 Universal character set, ``CESU-8``. + +An alternative to setting the encoding parameters is to set Oracle's +``NLS_LANG`` environment variable to a value such as +``AMERICAN_AMERICA.AL32UTF8``. See :ref:`Setting environment variables +`. As well as setting the character set, the ``NLS_LANG`` environment +variable lets you specify the Language (``AMERICAN`` in this example) and +Territory (``AMERICA``) used for NLS globalization. See `Choosing a Locale with +the NLS_LANG Environment Variable +`__. + +A character set specified by an ``encoding`` parameter will override the +character set in ``NLS_LANG``. The language and territory components will still +be used by Oracle. + +If the ``NLS_LANG`` environment variable is set in the application with +``os.environ['NLS_LANG']``, it must be set before any connection pool is created, +or before any standalone connections are created. + +Other Oracle globalization variable can also be set, see `Setting NLS Parameters +`__. + +Character Set Example +===================== + +The script below tries to display data containing a Euro symbol from the +database. The ``NLS_LANG`` environment variable on the operating system is set +to ``AMERICAN_AMERICA.WE8ISO8859P1``: + +.. code-block:: python + + connection = cx_Oracle.connect(userName, password, "dbhost.example.com/orclpdb1") + cursor = connection.cursor() + for row in cursor.execute("select * from nchar_test"): + print(row) + print(connection.encoding) + print(connection.nencoding) + +Because the '€' symbol is not supported by the ``WE8ISO8859P1`` character set, +all '€' characters are replaced by '¿' in the cx_Oracle output:: + + ('¿', 'test ', 'test', 'test ') + ('¿', 'test ', '¿', 'test ') + ('"nvarchar"', '/"nchar" ', 'varchardata', 'chardata ') + ('°', 'Second ', 'Third', 'Fourth ') + ISO-8859-1 + ISO-8859-1 + +When the ``encoding`` parameter is set during connection: + +.. code-block:: python + + connection = cx_Oracle.connect(userName, password, "dbhost.example.com/orclpdb1", + encoding="UTF-8", nencoding="UTF-8") + +Then the output displays the Euro symbol as desired:: + + ('€', 'test ', 'test', 'test ') + ('€', 'test ', '€', 'test ') + ('"nvarchar"', '/"nchar" ', 'varchardata', 'chardata ') + ('°', 'Second ', 'Third', 'Fourth ') + UTF-8 + UTF-8 + + +.. _findingcharset: + +Finding the Database and Client Character Set +============================================= + +To find the database character set, execute the query: + +.. code-block:: sql + + SELECT value AS db_charset + FROM nls_database_parameters + WHERE parameter = 'NLS_CHARACTERSET'; + +To find the current "client" character set used by cx_Oracle, execute the +query: + +.. code-block:: sql + + SELECT DISTINCT client_charset AS client_charset + FROM v$session_connect_info + WHERE sid = SYS_CONTEXT('USERENV', 'SID'); + +If these character sets do not match, characters will be mapped when +transferred over Oracle Net. This may impact performance and may result in +invalid data. diff --git a/doc/src/user_guide/ha.rst b/doc/src/user_guide/ha.rst new file mode 100644 index 00000000..fd07b965 --- /dev/null +++ b/doc/src/user_guide/ha.rst @@ -0,0 +1,205 @@ +.. _highavailability: + +******************************** +High Availability with cx_Oracle +******************************** + +Applications can utilize many features for high availability (HA) during planned and +unplanned outages in order to: + +* Reduce application downtime +* Eliminate compromises between high availability and performance +* Increase operational productivity + +.. _harecommend: + +General HA Recommendations +-------------------------- + +General recommendations for creating highly available cx_Oracle programs are: + +* Tune operating system and Oracle Network parameters to avoid long TCP timeouts, to prevent firewalls killing connections, and to avoid connection storms. +* Implement application error handling and recovery. +* Use the most recent version of the Oracle client libraries. New versions have improvements to features such as dead database server detection, and make it easier to set connection options. +* Use the most recent version of Oracle Database. New database versions introduce, and enhance, features such as Application Continuity (AC) and Transparent Application Continuity (TAC). +* Utilize Oracle Database technologies such as `RAC `__ or standby databases. +* Configure database services to emit :ref:`FAN ` events. +* Use a :ref:`connection pool `, because pools can handle database events and take proactive and corrective action for draining, run time load balancing, and fail over. Set the minimum and maximum pool sizes to the same values to avoid connection storms. Remove resource manager or user profiles that prematurely close sessions. +* Test all scenarios thoroughly. + +.. _hanetwork: + +Network Configuration +--------------------- + +The operating system TCP and :ref:`Oracle Net configuration ` +should be configured for performance and availability. + +Options such as `SQLNET.CONNECT_TIMEOUT +`__, +`SQLNET.RECV_TIMEOUT +`__ +and `SQLNET.SEND_TIMEOUT +`__ +can be explored. + +`Oracle Net Services +`__ options may +also be useful for high availability and performance tuning. For example the +database's `listener.ora` file can have `RATE_LIMIT +`__ +and `QUEUESIZE +`__ +parameters that can help handle connection storms. + +When cx_Oracle uses :ref:`Oracle Client libraries 19c `, then the +:ref:`Easy Connect Plus syntax ` syntax enables some options to be +used without needing a ``sqlnet.ora`` file. For example, if your firewall times +out every 4 minutes, and you cannot alter the firewall settings, then you may +decide to use ``EXPIRE_TIME`` in your connect string to send a probe every 2 +minutes to the database to keep connections 'alive':: + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1?expire_time=2") + +.. _fan: + +Fast Application Notification (FAN) +----------------------------------- + +Users of `Oracle Database FAN +`__ +must connect to a FAN-enabled database service. The application should have +``events`` set to True when connecting. This value can also be changed via +:ref:`Oracle Client Configuration `. + +FAN support is useful for planned and unplanned outages. It provides immediate +notification to cx_Oracle following outages related to the database, computers, +and networks. Without FAN, cx_Oracle can hang until a TCP timeout occurs and an +error is returned, which might be several minutes. + +FAN allows cx_Oracle to provide high availability features without the +application being aware of an outage. Unused, idle connections in a +:ref:`connection pool ` will be automatically cleaned up. A future +:meth:`SessionPool.acquire()` call will establish a fresh connection to a +surviving database instance without the application being aware of any service +disruption. + +To handle errors that affect active connections, you can add application logic +to re-connect (this will connect to a surviving database instance) and replay +application logic without having to return an error to the application user. + +FAN benefits users of Oracle Database's clustering technology `Oracle RAC +`__ +because connections to surviving database instances can be immediately made. +Users of Oracle's Data Guard with a broker will get FAN events generated when +the standby database goes online. Standalone databases will send FAN events +when the database restarts. + +For a more information on FAN see the `white paper on Fast Application +Notification +`__. + +.. _appcont: + +Application Continuity (AC) +--------------------------- + +Oracle Application Continuity and Transparent Application Continuity are Oracle +Database technologies that record application interaction with the database and, +in the event of a database instance outage, attempt to replay the interaction on +a surviving database instance. If successful, users will be unaware of any +database issue. + +When AC or TAC are configured on the database service, they are transparently +available to cx_Oracle applications. + +You must thoroughly test your application because not all lower level calls in +the the cx_Oracle implementation can be replayed. + +See `OCI and Application Continuity +`__ +for more information. + +.. _tg: + +Transaction Guard +----------------- + +cx_Oracle supports `Transaction Guard +`__ which enables Python +application to verify the success or failure of the last transaction in the +event of an unplanned outage. This feature is available when both client and +database are 12.1 or higher. + +Using Transaction Guard helps to: + +* Preserve the commit outcome +* Ensure a known outcome for every transaction + +See `Oracle Database Development Guide +`__ for more information about +using Transaction Guard. + +When an error occurs during commit, the Python application can acquire the +logical transaction id (``ltxid``) from the connection and then call a +procedure to determine the outcome of the commit for this logical transaction +id. + +Follow the steps below to use the Transaction Guard feature in Python: + +1. Grant execute privileges to the database users who will be checking the + outcome of the commit. Login as SYSDBA and run the following command: + + .. code-block:: sql + + GRANT EXECUTE ON DBMS_APP_CONT TO ; + +2. Create a new service by executing the following PL/SQL block as SYSDBA. + Replace the ````, ```` and + ```` values with suitable values. It is important that the + ``COMMIT_OUTCOME`` parameter be set to true for Transaction Guard to + function properly. + + .. code-block:: sql + + DECLARE + t_Params dbms_service.svc_parameter_array; + BEGIN + t_Params('COMMIT_OUTCOME') := 'true'; + t_Params('RETENTION_TIMEOUT') := ; + DBMS_SERVICE.CREATE_SERVICE('', '', t_Params); + END; + / + +3. Start the service by executing the following PL/SQL block as SYSDBA: + + .. code-block:: sql + + BEGIN + DBMS_SERVICE.start_service(''); + END; + / + +Ensure the service is running by examining the output of the following query: + + .. code-block:: sql + + SELECT name, network_name FROM V$ACTIVE_SERVICES ORDER BY 1; + + +**Python Application code requirements to use Transaction Guard** + +In the Python application code: + +* Use the connection attribute :attr:`~Connection.ltxid` to determine the + logical transaction id. +* Call the ``DBMS_APP_CONT.GET_LTXID_OUTCOME`` PL/SQL procedure with the + logical transaction id acquired from the connection attribute. This returns + a boolean value indicating if the last transaction was committed and whether + the last call was completed successfully or not. + +See the `Transaction Guard Sample +`__ for further details. diff --git a/doc/src/installation.rst b/doc/src/user_guide/installation.rst similarity index 91% rename from doc/src/installation.rst rename to doc/src/user_guide/installation.rst index 79b8874b..50bda96e 100644 --- a/doc/src/installation.rst +++ b/doc/src/user_guide/installation.rst @@ -4,8 +4,6 @@ cx_Oracle 7 Installation ************************ -.. contents:: :local: - Overview ======== @@ -26,6 +24,7 @@ To use cx_Oracle 7 with Python and Oracle Database you need: interoperability allows cx_Oracle to connect to both older and newer databases. +If you are upgrading, review the :ref:`release notes `. Quick Start cx_Oracle Installation ================================== @@ -34,7 +33,7 @@ Quick Start cx_Oracle Installation needed. Python 2.7 and Python 3.5 and higher are supported by cx_Oracle 7. - Install cx_Oracle from `PyPI - `__ with:: + `__ with:: python -m pip install cx_Oracle --upgrade @@ -42,6 +41,10 @@ Quick Start cx_Oracle Installation the source package will be downloaded instead. This will be compiled and the resulting binary installed. + If you are behind a proxy, specify your proxy server:: + + python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade + - Add Oracle 19, 18, 12 or 11.2 client libraries to your operating system library search path such as ``PATH`` on Windows or ``LD_LIBRARY_PATH`` on Linux. On macOS move the files to ``~/lib`` @@ -64,10 +67,10 @@ Quick Start cx_Oracle Installation `__ release. - Version 19, 18 and 12.2 client libraries can connect to Oracle Database 11.2 or - greater. Version 12.1 client libraries can connect to Oracle Database - 10.2 or greater. Version 11.2 client libraries can connect to Oracle - Database 9.2 or greater. + Version 19, 18 and 12.2 client libraries can connect to Oracle Database 11.2 + or greater. Version 12.1 client libraries can connect to Oracle Database 10.2 + or greater. Version 11.2 client libraries can connect to Oracle Database 9.2 + or greater. The database abstraction layer in cx_Oracle is `ODPI-C `__, which means that the `ODPI-C @@ -83,8 +86,8 @@ Quick Start cx_Oracle Installation import cx_Oracle - # Connect as user "hr" with password "welcome" to the "oraclepdb" service running on this computer. - connection = cx_Oracle.connect("hr", "welcome", "localhost/orclpdb") + # Connect as user "hr" with password "welcome" to the "orclpdb1" service running on this computer. + connection = cx_Oracle.connect("hr", "welcome", "localhost/orclpdb1") cursor = connection.cursor() cursor.execute(""" @@ -185,12 +188,16 @@ Install cx_Oracle The generic way to install cx_Oracle on Linux is to use Python's `Pip `__ package to install cx_Oracle from `PyPI -`__:: +`__:: python -m pip install cx_Oracle --upgrade +If you are behind a proxy, specify your proxy server:: + + python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade + This will download and install a pre-compiled binary `if one is -available `__ for your +available `__ for your architecture. If a pre-compiled binary is not available, the source will be downloaded, compiled, and the resulting binary installed. Compiling cx_Oracle requires the `Python.h` header file. If you are @@ -371,12 +378,16 @@ Install cx_Oracle Use Python's `Pip `__ package to install cx_Oracle from `PyPI -`__:: +`__:: python -m pip install cx_Oracle --upgrade +If you are behind a proxy, specify your proxy server:: + + python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade + This will download and install a pre-compiled binary `if one is -available `__ for your +available `__ for your architecture. If a pre-compiled binary is not available, the source will be downloaded, compiled, and the resulting binary installed. @@ -433,12 +444,14 @@ To use cx_Oracle with Oracle Instant Client zip files: SET PATH=C:\oracle\instantclient_18_3;%PATH% python %* - Invoke this batch file everytime you want to run python. + Invoke this batch file every time you want to run python. Alternatively use ``SET`` to change your ``PATH`` in each command prompt window before you run python. -4. Oracle Instant Client libraries require a Visual Studio redistributable with a 64-bit or 32-bit architecture to match Instant Client's architecture. Each Instant Client version requires a different redistributable version: +4. Oracle Instant Client libraries require a Visual Studio redistributable with + a 64-bit or 32-bit architecture to match Instant Client's architecture. + Each Instant Client version requires a different redistributable version: - For Instant Client 18 or 12.2 install `VS 2013 `__ - For Instant Client 12.1 install `VS 2010 `__ @@ -501,10 +514,14 @@ Install cx_Oracle Use Python's `Pip `__ package to install cx_Oracle from `PyPI -`__:: +`__:: python -m pip install cx_Oracle --upgrade +If you are behind a proxy, specify your proxy server:: + + python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade + The source will be downloaded, compiled, and the resulting binary installed. @@ -602,39 +619,37 @@ Install Using Source from PyPI ============================== The source package can be downloaded manually from -`PyPI `__ and extracted, after +`PyPI `__ and extracted, after which the following commands should be run:: python setup.py build python setup.py install -Upgrading from cx_Oracle 6 -========================== -Review the `release notes -`__ for -deprecations and modify any affected code. +Upgrading from Older Versions +============================= -Upgrading from cx_Oracle 5 -========================== +Review the :ref:`release notes ` for deprecations and modify any +affected code. If you are upgrading from cx_Oracle 5 note these installation changes: - When using Oracle Instant Client, you should not set ``ORACLE_HOME``. - - On Linux, cx_Oracle 6 no longer uses Instant Client RPMs automatically. - You must set ``LD_LIBRARY_PATH`` or use ``ldconfig`` to locate the Oracle - client library. + - On Linux, cx_Oracle 6 and higher no longer uses Instant Client RPMs + automatically. You must set ``LD_LIBRARY_PATH`` or use ``ldconfig`` to + locate the Oracle client library. - PyPI no longer allows Windows installers or Linux RPMs to be hosted. Use the supplied cx_Oracle Wheels instead, or use RPMs from Oracle, see :ref:`oraclelinux`. + Installing cx_Oracle 5.3 ======================== If you require cx_Oracle 5.3, download a Windows installer from `PyPI -`__ or use ``python -m pip +`__ or use ``python -m pip install cx-oracle==5.3`` to install from source. Very old versions of cx_Oracle can be found in the files section at @@ -650,8 +665,18 @@ If installation fails: using a different method. **Google anything that looks like an error.** Try some potential solutions. - - Was there a network connection error? Do you need to see the environment - variables ``http_proxy`` and/or ``https_proxy``? + - Was there a network connection error? Do you need to set the + environment variables ``http_proxy`` and/or ``https_proxy``? Or + try ``pip install --proxy=http://proxy.example.com:80 cx_Oracle + --upgrade``? + + - If upgrading gave no errors but the old version is still + installed, try ``pip install cx_Oracle --upgrade + --force-reinstall`` + + - If you do not have access to modify your system version of + Python, can you use ``pip install cx_Oracle --upgrade --user`` + or venv? - Do you get the error "``No module named pip``"? The pip module is builtin to Python from version 2.7.9 but is sometimes removed by the OS. Use the @@ -659,8 +684,8 @@ If installation fails: instead. - Do you get the error "``fatal error: dpi.h: No such file or directory``" - when building from source code? Ensure that your source installation has a - subdirectory called "odpi" containing files. If missing, review the + when building from source code? Ensure that your source installation has + a subdirectory called "odpi" containing files. If missing, review the section on `Install Using GitHub`_. If using cx_Oracle fails: diff --git a/doc/src/user_guide/introduction.rst b/doc/src/user_guide/introduction.rst new file mode 100644 index 00000000..8124dcae --- /dev/null +++ b/doc/src/user_guide/introduction.rst @@ -0,0 +1,128 @@ +.. _introduction: + +************************* +Introduction to cx_Oracle +************************* + +cx_Oracle is a Python extension module that enables Python access to Oracle +Database. It conforms to the `Python Database API v2.0 Specification +`__ with a considerable number of +additions and a couple of exclusions. + +Architecture +------------ + +Python programs call cx_Oracle functions. Internally cx_Oracle dynamically +loads Oracle Client libraries to access Oracle Database. + +.. _archfig: +.. figure:: /images/cx_Oracle_arch.png + + cx_Oracle Architecture + +cx_Oracle is typically installed from `PyPI +`__ using `pip +`__. The Oracle Client +libraries need to be installed separately. The libraries can be obtained from +an installation of `Oracle Instant Client +`__, from a +full Oracle Client installation, or even from an Oracle Database installation +(if Python is running on the same machine as the database). + +Some behaviors of the Oracle Client libraries can optionally be configured with +an ``oraaccess.xml`` file, for example to enable auto-tuning of a statement +cache. See :ref:`optclientfiles`. + +The Oracle Net layer can optionally be configured with files such as +``tnsnames.ora`` and ``sqlnet.ora``, for example to enable :ref:`network +encryption `. See :ref:`optnetfiles`. + +Oracle environment variables that are set before cx_Oracle first creates a +database connection will affect cx_Oracle behavior. Optional variables include +NLS_LANG, NLS_DATE_FORMAT and TNS_ADMIN. See :ref:`envset`. + +Features +-------- + +The cx_Oracle feature highlights are: + + * Easily installed from PyPI + * Support for Python 2 and 3, and for multiple Oracle Database versions + * Execution of SQL and PL/SQL statements + * Extensive Oracle data type support, including large objects (CLOB and + BLOB) and binding of SQL objects + * Connection management, including connection pooling + * Oracle Database High Availability features + * Full use of Oracle Network Service infrastructure, including encrypted + network traffic and security features + +A complete list of supported features can be seen `here +`_. + +Getting Started +--------------- + +Install cx_Oracle using the :ref:`installation ` steps. + +Create a script ``query.py`` as shown below: + +.. code-block:: python + + # query.py + + from __future__ import print_function + import cx_Oracle + + # Establish the database connection + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1") + + # Obtain a cursor + cursor = connection.cursor() + + # Data for binding + managerId = 145 + firstName = "Peter" + + # Execute the query + sql = """SELECT first_name, last_name + FROM employees + WHERE manager_id = :mid AND first_name = :fn""" + cursor.execute(sql, mid = managerId, fn = firstName) + + # Loop over the result set + for row in cursor: + print(row) + +This uses Oracle's `sample HR schema +`__. + +Simple :ref:`connection ` to the database requires a username, +password and connection string. Locate your Oracle Database `user name and +password `_ and the database +:ref:`connection string `, and use them in ``query.py``. For +cx_Oracle the connection string is commonly of the format +``hostname/servicename``, using the host name where the database is running and +the Oracle Database service name of the database instance. + +The :ref:`cursor ` is the object that allows statements to be +executed and results (if any) fetched. + +The data values in ``managerId`` and ``firstName`` are 'bound' to the statement +placeholder 'bind variables' ``:mid`` and ``:fn`` when the statement is +executed. This separates the statement text from the data, which helps avoid +SQL Injection security risks. :ref:`Binding ` is also important for +performance and scalability. + +The cursor allows rows to be iterated over and displayed. + +Run the script:: + + python query.py + +The output is:: + + ('Peter', 'Hall') + ('Peter', 'Tucker') + +Example cx_Oracle scripts and a tutorial are in the `GitHub samples directory +`__. diff --git a/doc/src/user_guide/json_data_type.rst b/doc/src/user_guide/json_data_type.rst new file mode 100644 index 00000000..470ac3db --- /dev/null +++ b/doc/src/user_guide/json_data_type.rst @@ -0,0 +1,76 @@ +.. _jsondatatype: + +******************************* +Working with the JSON Data Type +******************************* + +Native support for JSON data was introduced in Oracle database 12c. You can use +the relational database to store and query JSON data and benefit from the easy +extensibility of JSON data while retaining the performance and structure of the +relational database. JSON data is stored in the database in BLOB, CLOB or +VARCHAR2 columns. For performance reasons, it is always a good idea to store +JSON data in BLOB columns. To ensure that only JSON data is stored in that +column, use a check constraint with the clause ``is JSON`` as shown in the +following SQL to create a table containing JSON data: + +.. code-block:: sql + + create table customers ( + id integer not null primary key, + json_data blob check (json_data is json) + ); + +The following Python code can then be used to insert some data into the +database: + +.. code-block:: python + + import json + + customerData = dict(name="Rod", dept="Sales", location="Germany") + cursor.execute("insert into customers (id, json_data) values (:1, :2)", + [1, json.dumps(customerData)]) + +The data can be retrieved in its entirety using the following code: + +.. code-block:: python + + import json + + for blob, in cursor.execute("select json_data from customers"): + data = json.loads(blob.read()) + print(data["name"]) # will print Rod + +If only the department needs to be read, the following code can be used +instead: + +.. code-block:: python + + for deptName, in cursor.execute("select c.json_data.dept from customers c"): + print(deptName) # will print Sales + +You can convert the data stored in relational tables into JSON data by using +the JSON_OBJECT SQL operator. For example: + +.. code-block:: python + + import json + cursor.execute(""" + select json_object( + 'id' value employee_id, + 'name' value (first_name || ' ' || last_name)) + from employees where rownum <= 3""") + for value, in cursor: + print(json.loads(value,)) + +The result is:: + + {'id': 100, 'name': 'Steven King'} + {'id': 101, 'name': 'Neena Kochhar'} + {'id': 102, 'name': 'Lex De Haan'} + + +See `JSON Developer's Guide +`__ for more information about +using JSON in Oracle Database. diff --git a/doc/src/user_guide/lob_data.rst b/doc/src/user_guide/lob_data.rst new file mode 100644 index 00000000..fac7e8a0 --- /dev/null +++ b/doc/src/user_guide/lob_data.rst @@ -0,0 +1,199 @@ +.. _lobdata: + +************************ +Using CLOB and BLOB Data +************************ + +Oracle Database uses :ref:`lobobj` to store large data such as text, images, +videos and other multimedia formats. The maximum size of a LOB is limited to +the size of the tablespace storing it. + +There are four types of LOB (large object): + + * BLOB - Binary Large Object, used for storing binary data. cx_Oracle uses + the type :attr:`cx_Oracle.BLOB`. + * CLOB - Character Large Object, used for string strings in the database + character set format. cx_Oracle uses the type :attr:`cx_Oracle.CLOB`. + * NCLOB - National Character Large Object, used for string strings in the + national character set format. cx_Oracle uses the type + :attr:`cx_Oracle.NCLOB`. + * BFILE - External Binary File, used for referencing a file stored on the + host operating system outside of the database. cx_Oracle uses the type + :attr:`cx_Oracle.BFILE`. + +LOBs can be streamed to, and from, Oracle Database. + +LOBs up to 1 GB in length can be also be handled directly as strings or bytes in +cx_Oracle. This makes LOBs easy to work with, and has significant performance +benefits over streaming. However it requires the entire LOB data to be present +in Python memory, which may not be possible. + +See `GitHub `__ for LOB examples. + + +Simple Insertion of LOBs +------------------------ + +Consider a table with CLOB and BLOB columns: + +.. code-block:: sql + + CREATE TABLE lob_tbl ( + id NUMBER, + c CLOB, + b BLOB + ); + +With cx_Oracle, LOB data can be inserted in the table by binding strings or +bytes as needed: + +.. code-block:: python + + with open('example.txt', 'r') as f: + textdata = f.read() + + with open('image.png', 'rb') as f: + imgdata = f.read() + + cursor.execute(""" + insert into lob_tbl (id, c, b) + values (:lobid, :clobdata, :blobdata)""", + lobid=10, clobdata=textdata, blobdata=imgdata) + +Note that with this approach, LOB data is limited to 1 GB in size. + +.. _directlobs: + +Fetching LOBs as Strings and Bytes +---------------------------------- + +CLOBs and BLOBs smaller than 1 GB can queried from the database directly as +strings and bytes. This can be much faster than streaming. + +A :attr:`Connection.outputtypehandler` or :attr:`Cursor.outputtypehandler` needs +to be used as shown in this example: + +.. code-block:: python + + def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): + if defaultType == cx_Oracle.CLOB: + return cursor.var(cx_Oracle.LONG_STRING, arraysize=cursor.arraysize) + if defaultType == cx_Oracle.BLOB: + return cursor.var(cx_Oracle.LONG_BINARY, arraysize=cursor.arraysize) + + idVal = 1 + textData = "The quick brown fox jumps over the lazy dog" + bytesData = b"Some binary data" + cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)", + [idVal, textData, bytesData]) + + connection.outputtypehandler = OutputTypeHandler + cursor.execute("select c, b from lob_tbl where id = :1", [idVal]) + clobData, blobData = cursor.fetchone() + print("CLOB length:", len(clobData)) + print("CLOB data:", clobData) + print("BLOB length:", len(blobData)) + print("BLOB data:", blobData) + +This displays:: + + CLOB length: 43 + CLOB data: The quick brown fox jumps over the lazy dog + BLOB length: 16 + BLOB data: b'Some binary data' + + +Streaming LOBs (Read) +--------------------- + +Without the output type handler, the CLOB and BLOB values are fetched as +:ref:`LOB objects`. The size of the LOB object can be obtained by +calling :meth:`LOB.size()` and the data can be read by calling +:meth:`LOB.read()`: + +.. code-block:: python + + idVal = 1 + textData = "The quick brown fox jumps over the lazy dog" + bytesData = b"Some binary data" + cursor.execute("insert into lob_tbl (id, c, b) values (:1, :2, :3)", + [idVal, textData, bytesData]) + + cursor.execute("select b, c from lob_tbl where id = :1", [idVal]) + b, c = cursor.fetchone() + print("CLOB length:", c.size()) + print("CLOB data:", c.read()) + print("BLOB length:", b.size()) + print("BLOB data:", b.read()) + +This approach produces the same results as the previous example but it will +perform more slowly because it requires more round-trips to Oracle Database and +has higher overhead. It is needed, however, if the LOB data cannot be fetched as +one block of data from the server. + +To stream the BLOB column, the :meth:`LOB.read()` method can be called +repeatedly until all of the data has been read, as shown below: + +.. code-block:: python + + cursor.execute("select b from lob_tbl where id = :1", [10]) + blob, = cursor.fetchone() + offset = 1 + numBytesInChunk = 65536 + with open("image.png", "wb") as f: + while True: + data = blob.read(offset, numBytesInChunk) + if data: + f.write(data) + if len(data) < numBytesInChunk: + break + offset += len(data) + + +Streaming LOBs (Write) +---------------------- + +If a row containing a LOB is being inserted or updated, and the quantity of +data that is to be inserted or updated cannot fit in a single block of data, +the data can be streamed using the method :meth:`LOB.write()` instead as shown +in the following code: + +.. code-block:: python + + idVal = 9 + lobVar = cursor.var(cx_Oracle.BLOB) + cursor.execute(""" + insert into lob_tbl (id, b) + values (:1, empty_blob()) + returning b into :2""", [idVal, lobVar]) + blob, = lobVar.getvalue() + offset = 1 + numBytesInChunk = 65536 + with open("image.png", "rb") as f: + while True: + data = f.read(numBytesInChunk) + if data: + blob.write(data, offset) + if len(data) < numBytesInChunk: + break + offset += len(data) + connection.commit() + + +Temporary LOBs +-------------- + +All of the examples shown thus far have made use of permanent LOBs. These are +LOBs that are stored in the database. Oracle also supports temporary LOBs that +are not stored in the database but can be used to pass large quantities of +data. These LOBs use space in the temporary tablespace until all variables +referencing them go out of scope or the connection in which they are created is +explicitly closed. + +When calling PL/SQL procedures with data that exceeds 32,767 bytes in length, +cx_Oracle automatically creates a temporary LOB internally and passes that +value through to the procedure. If the data that is to be passed to the +procedure exceeds that which can fit in a single block of data, however, you +can use the method :meth:`Connection.createlob()` to create a temporary LOB. +This LOB can then be read and written just like in the examples shown above for +persistent LOBs. diff --git a/doc/src/user_guide/plsql_execution.rst b/doc/src/user_guide/plsql_execution.rst new file mode 100644 index 00000000..210c8a45 --- /dev/null +++ b/doc/src/user_guide/plsql_execution.rst @@ -0,0 +1,315 @@ +.. _plsqlexecution: + +**************** +PL/SQL Execution +**************** + +PL/SQL stored procedures, functions and anonymous blocks can be called from +cx_Oracle. + +.. _plsqlproc: + +PL/SQL Stored Procedures +------------------------ + +The :meth:`Cursor.callproc()` method is used to call PL/SQL procedures. + +If a procedure with the following definition exists: + +.. code-block:: sql + + create or replace procedure myproc ( + a_Value1 number, + a_Value2 out number + ) as + begin + a_Value2 := a_Value1 * 2; + end; + +then the following Python code can be used to call it: + +.. code-block:: python + + outVal = cursor.var(int) + cursor.callproc('myproc', [123, outVal]) + print(outVal.getvalue()) # will print 246 + +Calling :meth:`Cursor.callproc()` actually generates an anonymous PL/SQL block +as shown below, which is then executed: + +.. code-block:: python + + cursor.execute("begin myproc(:1,:2); end;", [123, outval]) + +See :ref:`bind` for information on binding. + + +.. _plsqlfunc: + +PL/SQL Stored Functions +----------------------- + +The :meth:`Cursor.callfunc()` method is used to call PL/SQL functions. + +The ``returnType`` parameter for :meth:`~Cursor.callfunc()` is +expected to be a Python type, one of the :ref:`cx_Oracle types ` or +an :ref:`Object Type `. + +If a function with the following definition exists: + +.. code-block:: sql + + create or replace function myfunc ( + a_StrVal varchar2, + a_NumVal number + ) return number as + begin + return length(a_StrVal) + a_NumVal * 2; + end; + +then the following Python code can be used to call it: + +.. code-block:: python + + returnVal = cursor.callfunc("myfunc", int, ["a string", 15]) + print(returnVal) # will print 38 + +A more complex example that returns a spatial (SDO) object can be seen below. +First, the SQL statements necessary to set up the example: + +.. code-block:: sql + + create table MyPoints ( + id number(9) not null, + point sdo_point_type not null + ); + + insert into MyPoints values (1, sdo_point_type(125, 375, 0)); + + create or replace function spatial_queryfn ( + a_Id number + ) return sdo_point_type is + t_Result sdo_point_type; + begin + select point + into t_Result + from MyPoints + where Id = a_Id; + + return t_Result; + end; + / + +The Python code that will call this procedure looks as follows: + +.. code-block:: python + + objType = connection.gettype("SDO_POINT_TYPE") + cursor = connection.cursor() + returnVal = cursor.callfunc("spatial_queryfn", objType, [1]) + print("(%d, %d, %d)" % (returnVal.X, returnVal.Y, returnVal.Z)) + # will print (125, 375, 0) + +See :ref:`bind` for information on binding. + + +Anonymous PL/SQL Blocks +----------------------- + +An anonymous PL/SQL block can be called as shown: + +.. code-block:: python + + var = cursor.var(int) + cursor.execute(""" + begin + :outVal := length(:inVal); + end;""", inVal="A sample string", outVal=var) + print(var.getvalue()) # will print 15 + +See :ref:`bind` for information on binding. + + +Using DBMS_OUTPUT +----------------- + +The standard way to print output from PL/SQL is using the package +`DBMS_OUTPUT `__. In order to use this package +from Python use the following steps: + +* Call the PL/SQL procedure ``DBMS_OUTPUT.ENABLE()`` to enable output to be + buffered for the connection. +* Execute some PL/SQL procedure that puts text in the buffer by calling + ``DBMS_OUTPUT.PUT_LINE()``. +* Call ``DBMS_OUTPUT.GET_LINE()`` repeatedly to fetch the text from the buffer + until the status returned is non-zero. + +For example: + +.. code-block:: python + + # enable DBMS_OUTPUT + cursor.callproc("dbms_output.enable") + + # execute some PL/SQL that calls DBMS_OUTPUT.PUT_LINE + cursor.execute(""" + begin + dbms_output.put_line('This is the cx_Oracle manual'); + dbms_output.put_line('Demonstrating use of DBMS_OUTPUT'); + end;""") + + # perform loop to fetch the text that was added by PL/SQL + textVar = cursor.var(str) + statusVar = cursor.var(int) + while True: + cursor.callproc("dbms_output.get_line", (textVar, statusVar)) + if statusVar.getvalue() != 0: + break + print(textVar.getvalue()) + +This will produce the following output:: + + This is the cx_Oracle manual + Demonstrating use of DBMS_OUTPUT + + +Implicit results +---------------- + +Implicit results permit a Python program to consume cursors returned by a +PL/SQL block without the requirement to use OUT REF CURSOR parameters. The +method :meth:`Cursor.getimplicitresults()` can be used for this purpose. It +requires both the Oracle Client and Oracle Database to be 12.1 or higher. + +An example using implicit results is as shown: + +.. code-block:: python + + cursor.execute(""" + declare + cust_cur sys_refcursor; + sales_cur sys_refcursor; + begin + open cust_cur for SELECT * FROM cust_table; + dbms_sql.return_result(cust_cur); + + open sales_cur for SELECT * FROM sales_table; + dbms_sql.return_result(sales_cur); + end;""") + + for implicitCursor in cursor.getimplicitresults(): + for row in implicitCursor: + print(row) + +Data from both the result sets are returned:: + + (1, 'Tom') + (2, 'Julia') + (1000, 1, 'BOOKS') + (2000, 2, 'FURNITURE') + + +Edition-Based Redefinition (EBR) +-------------------------------- + +Oracle Database's `Edition-Based Redefinition +`__ feature enables upgrading of +the database component of an application while it is in use, thereby minimizing +or eliminating down time. This feature allows multiple versions of views, +synonyms, PL/SQL objects and SQL Translation profiles to be used concurrently. +Different versions of the database objects are associated with an "edition". + +The simplest way to set an edition is to pass the ``edition`` parameter to +:meth:`cx_Oracle.connect()` or :meth:`cx_Oracle.SessionPool()`: + +.. code-block:: python + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", + edition="newsales", encoding="UTF-8") + + +The edition could also be set by setting the environment variable +``ORA_EDITION`` or by executing the SQL statement: + +.. code-block:: sql + + alter session set edition = ; + +Regardless of which method is used to set the edition, the value that is in use +can be seen by examining the attribute :attr:`Connection.edition`. If no value +has been set, the value will be None. This corresponds to the database default +edition ``ORA$BASE``. + +Consider an example where one version of a PL/SQL function ``Discount`` is +defined in the database default edition ``ORA$BASE`` and the other version of +the same function is defined in a user created edition ``DEMO``. + +.. code-block:: sql + + connect / + + -- create function using the database default edition + CREATE OR REPLACE FUNCTION Discount(price IN NUMBER) RETURN NUMBER IS + BEGIN + return price * 0.9; + END; + / + +A new edition named 'DEMO' is created and the user given permission to use +editions. The use of ``FORCE`` is required if the user already contains one or +more objects whose type is editionable and that also have non-editioned +dependent objects. + +.. code-block:: sql + + connect system/ + + CREATE EDITION demo; + ALTER USER ENABLE EDITIONS FORCE; + GRANT USE ON EDITION demo to ; + +The ``Discount`` function for the demo edition is as follows: + +.. code-block:: sql + + connect / + + alter session set edition = demo; + + -- Function for the demo edition + CREATE OR REPLACE FUNCTION Discount(price IN NUMBER) RETURN NUMBER IS + BEGIN + return price * 0.5; + END; + / + +The Python application can then call the required version of the PL/SQL +function as shown: + +.. code-block:: python + + connection = cx_Oracle.connect(, , "dbhost.example.com/orclpdb1", + encoding="UTF-8") + print("Edition is:", repr(connection.edition)) + + cursor = connection.cursor() + discountedPrice = cursor.callfunc("Discount", int, [100]) + print("Price after discount is:", discountedPrice) + + # Use the edition parameter for the connection + connection = cx_Oracle.connect(, , "dbhost.example.com/orclpdb1", + edition = "demo", encoding="UTF-8") + print("Edition is:", repr(connection.edition)) + + cursor = connection.cursor() + discountedPrice = cursor.callfunc("Discount", int, [100]) + print("Price after discount is:", discountedPrice) + +The output of the function call for the default and demo edition is as shown:: + + Edition is: None + Price after discount is: 90 + Edition is: 'DEMO' + Price after discount is: 50 diff --git a/doc/src/user_guide/soda.rst b/doc/src/user_guide/soda.rst new file mode 100644 index 00000000..600e9d61 --- /dev/null +++ b/doc/src/user_guide/soda.rst @@ -0,0 +1,108 @@ +.. _sodausermanual: + +************************************ +Simple Oracle Document Access (SODA) +************************************ + +Overview +======== + +Oracle Database Simple Oracle Document Access (SODA) allows documents to be +inserted, queried, and retrieved from Oracle Database using a set of +NoSQL-style cx_Oracle methods. Documents are generally JSON data but they can +be any data at all (including video, images, sounds, or other binary content). +Documents can be fetched from the database by key lookup or by using +query-by-example (QBE) pattern-matching. + +SODA uses a SQL schema to store documents but you do not need to know SQL or +how the documents are stored. However, access via SQL does allow use of +advanced Oracle Database functionality such as analytics for reporting. + +For general information on SODA, see the `SODA home page +`__ +and `Oracle Database Introduction to SODA +`__. + +cx_Oracle uses the following objects for SODA: + +* :ref:`SODA Database Object `: The top level object for cx_Oracle SODA + operations. This is acquired from an Oracle Database connection. A 'SODA + database' is an abstraction, allowing access to SODA collections in that + 'SODA database', which then allow access to documents in those collections. + A SODA database is analogous to an Oracle Database user or schema, a + collection is analogous to a table, and a document is analogous to a table + row with one column for a unique document key, a column for the document + content, and other columns for various document attributes. + +* :ref:`SODA Collection Object `: Represents a collection of SODA + documents. By default, collections allow JSON documents to be stored. This + is recommended for most SODA users. However optional metadata can set + various details about a collection, such as its database storage, whether it + should track version and time stamp document components, how such components + are generated, and what document types are supported. By default, the name of + the Oracle Database table storing a collection is the same as the collection + name. Note: do not use SQL to drop the database table, since SODA metadata + will not be correctly removed. Use the :meth:`SodaCollection.drop()` method + instead. + +* :ref:`SODA Document Object `: Represents a document. Typically the + document content will be JSON. The document has properties including the + content, a key, timestamps, and the media type. By default, document keys + are automatically generated. See :ref:`SODA Document objects ` for + the forms of SodaDoc. + +* :ref:`SODA Document Cursor `: A cursor object representing the + result of the :meth:`SodaOperation.getCursor()` method from a + :meth:`SodaCollection.find()` operation. It can be iterated over to access + each SodaDoc. + +* :ref:`SODA Operation Object `: An internal object used with + :meth:`SodaCollection.find()` to perform read and write operations on + documents. Chained methods set properties on a SodaOperation object which is + then used by a terminal method to find, count, replace, or remove documents. + This is an internal object that should not be directly accessed. + +SODA Example +============ + +Creating and adding documents to a collection can be done as follows: + +.. code-block:: python + + soda = connection.getSodaDatabase() + + # create a new SODA collection; this will open an existing collection, if + # the name is already in use + collection = soda.createCollection("mycollection") + + # insert a document into the collection; for the common case of a JSON + # document, the content can be a simple Python dictionary which will + # internally be converted to a JSON document + content = {'name': 'Matilda', 'address': {'city': 'Melbourne'}} + returnedDoc = collection.insertOneAndGet(content) + key = returnedDoc.key + print('The key of the new SODA document is: ', key) + +By default, a system generated key is created when documents are inserted. +With a known key, you can retrieve a document: + +.. code-block:: python + + # this will return a dictionary (as was inserted in the previous code) + content = collection.find().key(key).getOne().getContent() + print(content) + +You can also search for documents using query-by-example syntax: + +.. code-block:: python + + # Find all documents with names like 'Ma%' + print("Names matching 'Ma%'") + qbe = {'name': {'$like': 'Ma%'}} + for doc in collection.find().filter(qbe).getDocuments(): + content = doc.getContent() + print(content["name"]) + +See the `samples directory +`__ +for runnable SODA examples. diff --git a/doc/src/user_guide/sql_execution.rst b/doc/src/user_guide/sql_execution.rst new file mode 100644 index 00000000..e72e231e --- /dev/null +++ b/doc/src/user_guide/sql_execution.rst @@ -0,0 +1,636 @@ +.. _sqlexecution: + +************* +SQL Execution +************* + +Executing SQL statements is the primary way in which a Python application +communicates with Oracle Database. Statements are executed using the methods +:meth:`Cursor.execute()` or :meth:`Cursor.executemany()`. Statements include +queries, Data Manipulation Language (DML), and Data Definition Language (DDL). +A few other `specialty statements +`__ can also be executed. + +PL/SQL statements are discussed in :ref:`plsqlexecution`. Other chapters +contain information on specific data types and features. See :ref:`batchstmnt`, +:ref:`lobdata`, :ref:`jsondatatype`, and :ref:`xmldatatype`. + +cx_Oracle can be used to execute individual statements, one at a time. It does +not read SQL*Plus ".sql" files. To read SQL files, use a technique like the one +in ``RunSqlScript()`` in `samples/SampleEnv.py +`__ + +SQL statements should not contain a trailing semicolon (";") or forward slash +("/"). This will fail: + +.. code-block:: sql + + cur.execute("select * from MyTable;") + +This is correct: + +.. code-block:: sql + + cur.execute("select * from MyTable") + + +SQL Queries +=========== + +Queries (statements beginning with SELECT or WITH) can only be executed using +the method :meth:`Cursor.execute()`. Rows can then be iterated over, or can be +fetched using one of the methods :meth:`Cursor.fetchone()`, +:meth:`Cursor.fetchmany()` or :meth:`Cursor.fetchall()`. There is a +:ref:`default type mapping ` to Python types that can be +optionally :ref:`overridden `. + +.. IMPORTANT:: + + Interpolating or concatenating user data with SQL statements, for example + ``cur.execute("SELECT * FROM mytab WHERE mycol = '" + myvar + "'")``, is a security risk + and impacts performance. Use :ref:`bind variables ` instead. For + example, ``cur.execute("SELECT * FROM mytab WHERE mycol = :mybv", mybv=myvar)``. + +.. _fetching: + +Fetch Methods +------------- + +After :meth:`Cursor.execute()`, the cursor is returned as a convenience. This +allows code to iterate over rows like: + +.. code-block:: python + + cur = connection.cursor() + for row in cur.execute("select * from MyTable"): + print(row) + +Rows can also be fetched one at a time using the method +:meth:`Cursor.fetchone()`: + +.. code-block:: python + + cur = connection.cursor() + cur.execute("select * from MyTable") + while True: + row = cur.fetchone() + if row is None: + break + print(row) + +If rows need to be processed in batches, the method :meth:`Cursor.fetchmany()` +can be used. The size of the batch is controlled by the ``numRows`` parameter, +which defaults to the value of :attr:`Cursor.arraysize`. + +.. code-block:: python + + cur = connection.cursor() + cur.execute("select * from MyTable") + numRows = 10 + while True: + rows = cur.fetchmany(numRows) + if not rows: + break + for row in rows: + print(row) + +If all of the rows need to be fetched, and can be contained in memory, the +method :meth:`Cursor.fetchall()` can be used. + +.. code-block:: python + + cur = connection.cursor() + cur.execute("select * from MyTable") + rows = cur.fetchall() + for row in rows: + print(row) + +Closing Cursors +--------------- + +A cursor may be used to execute multiple statements. Once it is no longer +needed, it should be closed by calling :meth:`~Cursor.close()` in order to +reclaim resources in the database. It will be closed automatically when the +variable referencing it goes out of scope (and no further references are +retained). One other way to control the lifetime of a cursor is to use a "with" +block, which ensures that a cursor is closed once the block is completed. For +example: + +.. code-block:: python + + with connection.cursor() as cursor: + for row in cursor.execute("select * from MyTable"): + print(row) + +This code ensures that, once the block is completed, the cursor is closed and +resources have been reclaimed by the database. In addition, any attempt to use +the variable ``cursor`` outside of the block will simply fail. + + +Tuning Fetch Performance +------------------------ + +For best performance, the cx_Oracle :attr:`Cursor.arraysize` value should be set +before calling :meth:`Cursor.execute()`. The default value is 100. For queries +that return a large number of rows, increasing ``arraysize`` can improve +performance because it reduces the number of round-trips to the database. +However increasing this value increases the amount of memory required. The best +value for your system depends on factors like your network speed, the query row +size, and available memory. An appropriate value can be found by experimenting +with your application. + +Regardless of which fetch method is used to get rows, internally all rows are +fetched in batches corresponding to the value of ``arraysize``. The size does +not affect how, or when, rows are returned to your application (other than being +used as the default size for :meth:`Cursor.fetchmany()`). It does not limit the +minimum or maximum number of rows returned by a query. + +Along with tuning ``arraysize``, make sure your `SQL statements are optimal +`_ and avoid +selecting columns that are not required by the application. For queries that do +not need to fetch all data, use a :ref:`row limiting clause ` to +reduce the number of rows processed by the database. + +An example of setting ``arraysize`` is: + +.. code-block:: python + + cur = connection.cursor() + cur.arraysize = 500 + for row in cur.execute("select * from MyTable"): + print(row) + +One place where increasing ``arraysize`` is particularly useful is in copying +data from one database to another: + +.. code-block:: python + + # setup cursors + sourceCursor = sourceConnection.cursor() + sourceCursor.arraysize = 1000 + targetCursor = targetConnection.cursor() + targetCursor.arraysize = 1000 + + # perform fetch and bulk insertion + sourceCursor.execute("select * from MyTable") + while True: + rows = sourceCursor.fetchmany() + if not rows: + break + targetCursor.executemany("insert into MyTable values (:1, :2)", rows) + targetConnection.commit() + +If you know that a query returns a small number of rows then you should reduce +the value of ``arraysize``. For example if you are fetching only one row, then +set ``arraysize`` to 1: + +.. code-block:: python + + cur = connection.cursor() + cur.arraysize = 1 + cur.execute("select * from MyTable where id = 1"): + row = cur.fetchone() + print(row) + +In cx_Oracle, the ``arraysize`` value is only examined when a statement is +executed the first time. To change the ``arraysize`` for a repeated statement, +create a new cursor: + +.. code-block:: python + + array_sizes = (10, 100, 1000) + for size in array_sizes: + cursor = connection.cursor() + cursor.arraysize = size + start = time.time() + cursor.execute(sql).fetchall() + elapsed = time.time() - start + print("Time for", size, elapsed, "seconds") + +.. _querymetadata: + +Query Column Metadata +--------------------- + +After executing a query, the column metadata such as column names and data types +can be obtained using :attr:`Cursor.description`: + +.. code-block:: python + + cur = connection.cursor() + cur.execute("select * from MyTable") + for column in cur.description: + print(column) + +This could result in metadata like:: + + ('ID', , 39, None, 38, 0, 0) + ('NAME', , 20, 20, None, None, 1) + + +.. _defaultfetchtypes: + +Fetch Data Types +---------------- + +The following table provides a list of all of the data types that cx_Oracle +knows how to fetch. The middle column gives the type that is returned in the +:ref:`query metadata `. The last column gives the type of Python +object that is returned by default. Python types can be changed with +:ref:`Output Type Handlers `. + +.. list-table:: + :header-rows: 1 + :widths: 1 1 1 + :align: left + + * - Oracle Database Type + - cx_Oracle Type + - Default Python type + * - BFILE + - :attr:`cx_Oracle.BFILE` + - :ref:`cx_Oracle.LOB ` + * - BINARY_DOUBLE + - :attr:`cx_Oracle.NATIVE_FLOAT` + - float + * - BINARY_FLOAT + - :attr:`cx_Oracle.NATIVE_FLOAT` + - float + * - BLOB + - :attr:`cx_Oracle.BLOB` + - :ref:`cx_Oracle.LOB ` + * - CHAR + - :attr:`cx_Oracle.FIXED_CHAR` + - str + * - CLOB + - :attr:`cx_Oracle.CLOB` + - :ref:`cx_Oracle.LOB ` + * - CURSOR + - :attr:`cx_Oracle.CURSOR` + - :ref:`cx_Oracle.Cursor ` + * - DATE + - :attr:`cx_Oracle.DATETIME` + - datetime.datetime + * - INTERVAL DAY TO SECOND + - :attr:`cx_Oracle.INTERVAL` + - datetime.timedelta + * - LONG + - :attr:`cx_Oracle.LONG_STRING` + - str + * - LONG RAW + - :attr:`cx_Oracle.LONG_BINARY` + - bytes [4]_ + * - NCHAR + - :attr:`cx_Oracle.FIXED_NCHAR` + - str [1]_ + * - NCLOB + - :attr:`cx_Oracle.NCLOB` + - :ref:`cx_Oracle.LOB ` + * - NUMBER + - :attr:`cx_Oracle.NUMBER` + - float or int [2]_ + * - NVARCHAR2 + - :attr:`cx_Oracle.NCHAR` + - str [1]_ + * - OBJECT [5]_ + - :attr:`cx_Oracle.OBJECT` + - :ref:`cx_Oracle.Object ` + * - RAW + - :attr:`cx_Oracle.BINARY` + - bytes [4]_ + * - ROWID + - :attr:`cx_Oracle.ROWID` + - str + * - TIMESTAMP + - :attr:`cx_Oracle.TIMESTAMP` + - datetime.datetime + * - TIMESTAMP WITH LOCAL TIME ZONE + - :attr:`cx_Oracle.TIMESTAMP` + - datetime.datetime [3]_ + * - TIMESTAMP WITH TIME ZONE + - :attr:`cx_Oracle.TIMESTAMP` + - datetime.datetime [3]_ + * - UROWID + - :attr:`cx_Oracle.ROWID` + - str + * - VARCHAR2 + - :attr:`cx_Oracle.STRING` + - str + +.. [1] In Python 2 these are fetched as unicode objects. +.. [2] If the precision and scale obtained from query column metadata indicate + that the value can be expressed as an integer, the value will be + returned as an int. If the column is unconstrained (no precision and + scale specified), the value will be returned as a float or an int + depending on whether the value itself is an integer. In all other cases + the value is returned as a float. Note that in Python 2, values returned + as integers will be int or long depending on the size of the integer. +.. [3] The timestamps returned are naive timestamps without any time zone + information present. +.. [4] In Python 2 these are identical to str objects since Python 2 doesn't + have a native bytes object. +.. [5] These include all user-defined types such as VARRAY, NESTED TABLE, etc. + + +.. _outputtypehandlers: + +Changing Fetched Data Types with Output Type Handlers +----------------------------------------------------- + +Sometimes the default conversion from an Oracle Database type to a Python type +must be changed in order to prevent data loss or to fit the purposes of the +Python application. In such cases, an output type handler can be specified for +queries. Output type handlers do not affect values returned from +:meth:`Cursor.callfunc()` or :meth:`Cursor.callproc()`. + +Output type handlers can be specified on the :attr:`connection +` or on the :attr:`cursor +`. If specified on the cursor, fetch type handling is +only changed on that particular cursor. If specified on the connection, all +cursors created by that connection will have their fetch type handling changed. + +The output type handler is expected to be a function with the following +signature:: + + handler(cursor, name, defaultType, size, precision, scale) + +The parameters are the same information as the query column metadata found in +:attr:`Cursor.description`. The function is called once for each column that is +going to be fetched. The function is expected to return a +:ref:`variable object ` (generally by a call to :func:`Cursor.var()`) +or the value ``None``. The value ``None`` indicates that the default type +should be used. + +Examples of output handlers are shown in :ref:`numberprecision` and +:ref:`directlobs`. + +.. _numberprecision: + +Fetched Number Precision +------------------------ + +One reason for using an output type handler is to ensure that numeric precision +is not lost when fetching certain numbers. Oracle Database uses decimal numbers +and these cannot be converted seamlessly to binary number representations like +Python floats. In addition, the range of Oracle numbers exceeds that of +floating point numbers. Python has decimal objects which do not have these +limitations and cx_Oracle knows how to perform the conversion between Oracle +numbers and Python decimal values if directed to do so. + +The following code sample demonstrates the issue: + +.. code-block:: python + + cur = connection.cursor() + cur.execute("create table test_float (X number(5, 3))") + cur.execute("insert into test_float values (7.1)") + connection.commit() + cur.execute("select * from test_float") + val, = cur.fetchone() + print(val, "* 3 =", val * 3) + +This displays ``7.1 * 3 = 21.299999999999997`` + +Using Python decimal objects, however, there is no loss of precision: + +.. code-block:: python + + import decimal + + def NumberToDecimal(cursor, name, defaultType, size, precision, scale): + if defaultType == cx_Oracle.NUMBER: + return cursor.var(decimal.Decimal, arraysize=cursor.arraysize) + + cur = connection.cursor() + cur.outputtypehandler = NumberToDecimal + cur.execute("select * from test_float") + val, = cur.fetchone() + print(val, "* 3 =", val * 3) + +This displays ``7.1 * 3 = 21.3`` + +The Python ``decimal.Decimal`` converter gets called with the string +representation of the Oracle number. The output from ``decimal.Decimal`` is +returned in the output tuple. + +.. _outconverters: + +Changing Query Results with Outconverters +----------------------------------------- + +cx_Oracle "outconverters" can be used with :ref:`output type handlers +` to change returned data. + +For example, to make queries return empty strings instead of NULLs: + +.. code-block:: python + + def OutConverter(value): + if value is None: + return '' + return value + + def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): + if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR): + return cursor.var(str, size, cur.arraysize, outconverter=OutConverter) + + connection.outputtypehandler = OutputTypeHandler + +.. _scrollablecursors: + +Scrollable Cursors +------------------ + +Scrollable cursors enable applications to move backwards, forwards, to skip +rows, and to move to a particular row in a query result set. The result set is +cached on the database server until the cursor is closed. In contrast, regular +cursors are restricted to moving forward. + +A scrollable cursor is created by setting the parameter ``scrollable=True`` +when creating the cursor. The method :meth:`Cursor.scroll()` is used to move to +different locations in the result set. + +Examples are: + +.. code-block:: python + + cursor = connection.cursor(scrollable=True) + cursor.execute("select * from ChildTable order by ChildId") + + cursor.scroll(mode="last") + print("LAST ROW:", cursor.fetchone()) + + cursor.scroll(mode="first") + print("FIRST ROW:", cursor.fetchone()) + + cursor.scroll(8, mode="absolute") + print("ROW 8:", cursor.fetchone()) + + cursor.scroll(6) + print("SKIP 6 ROWS:", cursor.fetchone()) + + cursor.scroll(-4) + print("SKIP BACK 4 ROWS:", cursor.fetchone()) + +.. _rowlimit: + +Limiting Rows +------------- + +Query data is commonly broken into one or more sets: + +- To give an upper bound on the number of rows that a query has to process, + which can help improve database scalability. + +- To perform 'Web pagination' that allows moving from one set of rows to a + next, or previous, set on demand. + +- For fetching of all data in consecutive small sets for batch processing. + This happens because the number of records is too large for Python to handle + at one time. + +The latter can be handled by calling :meth:`Cursor.fetchmany()` with one +execution of the SQL query. + +'Web pagination' and limiting the maximum number of rows are discussed in this +section. For each 'page' of results, a SQL query is executed to get the +appropriate set of rows from a table. Since the query may be executed more +than once, make sure to use :ref:`bind variables ` for row numbers and +row limits. + +Oracle Database 12c SQL introduced an ``OFFSET`` / ``FETCH`` clause which is +similar to the ``LIMIT`` keyword of MySQL. In Python you can fetch a set of +rows using: + +.. code-block:: python + + myoffset = 0 // do not skip any rows (start at row 1) + mymaxnumrows = 20 // get 20 rows + + sql = + """SELECT last_name + FROM employees + ORDER BY last_name + OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY""" + + cur = connection.cursor() + for row in cur.execute(sql, offset=myoffset, maxnumrows=mymaxnumrows): + print(row) + +In applications where the SQL query is not known in advance, this method +sometimes involves appending the ``OFFSET`` clause to the 'real' user query. Be +very careful to avoid SQL injection security issues. + +For Oracle Database 11g and earlier there are several alternative ways +to limit the number of rows returned. The old, canonical paging query +is:: + + SELECT * + FROM (SELECT a.*, ROWNUM AS rnum + FROM (YOUR_QUERY_GOES_HERE -- including the order by) a + WHERE ROWNUM <= MAX_ROW) + WHERE rnum >= MIN_ROW + +Here, ``MIN_ROW`` is the row number of first row and ``MAX_ROW`` is the row +number of the last row to return. For example:: + + SELECT * + FROM (SELECT a.*, ROWNUM AS rnum + FROM (SELECT last_name FROM employees ORDER BY last_name) a + WHERE ROWNUM <= 20) + WHERE rnum >= 1 + +This always has an 'extra' column, here called RNUM. + +An alternative and preferred query syntax for Oracle Database 11g uses the +analytic ``ROW_NUMBER()`` function. For example to get the 1st to 20th names the +query is:: + + SELECT last_name FROM + (SELECT last_name, + ROW_NUMBER() OVER (ORDER BY last_name) AS myr + FROM employees) + WHERE myr BETWEEN 1 and 20 + +Make sure to use :ref:`bind variables ` for the upper and lower limit +values. + +.. _codecerror: + +Querying Corrupt Data +--------------------- + +If queries fail with the error "codec can't decode byte" when you select data, +then: + +* Check your :ref:`character set ` is correct. Review the + :ref:`client and database character sets `. Consider using + UTF-8, if this is appropriate: + + .. code-block:: python + + connection = cx_Oracle.connect("hr", userpwd, "dbhost.example.com/orclpdb1", + encoding="UTF-8", nencoding="UTF-8") + +* Check for corrupt data in the database. + +If data really is corrupt, you can pass options to the internal `decode() +`__ used by +cx_Oracle to allow it to be selected and prevent the whole query failing. Do +this by creating an :ref:`outputtypehandler ` and setting +``encodingErrors``. For example to replace corrupt characters in character +columns: + +.. code-block:: python + + def OutputTypeHandler(cursor, name, defaultType, size, precision, scale): + if defaultType == cx_Oracle.STRING: + return cursor.var(defaultType, size, arraysize=cursor.arraysize, + encodingErrors="replace") + + cursor.outputtypehandler = OutputTypeHandler + + cursor.execute("select column1, column2 from SomeTableWithBadData") + +Other codec behaviors can be chosen for ``encodingErrors``, see `Error Handlers +`__. + +.. _dml: + + +INSERT and UPDATE Statements +============================ + +SQL Data Manipulation Language statements (DML) such as INSERT and UPDATE can +easily be executed with cx_Oracle. For example: + +.. code-block:: python + + cur = connection.cursor() + cur.execute("insert into MyTable values (:idbv, :nmbv)", [1, "Fredico"]) + +Do not concatenate or interpolate user data into SQL statements. See +:ref:`bind` instead. + +See :ref:`txnmgmnt` for best practices on committing and rolling back data +changes. + +When handling multiple data values, use :meth:`~Cursor.executemany()` for +performance. See :ref:`batchstmnt` + + +Inserting NULLs +--------------- + +Oracle requires a type, even for null values. When you pass the value None, then +cx_Oracle assumes the type is STRING. If this is not the desired type, you can +explicitly set it. For example, to insert a null :ref:`Oracle Spatial +SDO_GEOMETRY ` object: + +.. code-block:: python + + typeObj = connection.gettype("SDO_GEOMETRY") + cur = connection.cursor() + cur.setinputsizes(typeObj) + cur.execute("insert into sometable values (:1)", [None]) diff --git a/doc/src/user_guide/tracing_sql.rst b/doc/src/user_guide/tracing_sql.rst new file mode 100644 index 00000000..6f404b58 --- /dev/null +++ b/doc/src/user_guide/tracing_sql.rst @@ -0,0 +1,166 @@ +.. _tracingsql: + +********************************* +Tracing SQL and PL/SQL Statements +********************************* + +Subclass Connections +==================== + +Subclassing enables applications to add "hooks" for connection and statement +execution. This can be used to alter, or log, connection and execution +parameters, and to extend cx_Oracle functionality. + +The example below demonstrates subclassing a connection to log SQL execution +to a file. This example also shows how connection credentials can be embedded +in the custom subclass, so application code does not need to supply them. + +.. code-block:: python + + class Connection(cx_Oracle.Connection): + logFileName = "log.txt" + + def __init__(self): + connectString = "hr/hr_password@dbhost.example.com/orclpdb1" + self._log("Connect to the database") + return super(Connection, self).__init__(connectString) + + def _log(self, message): + with open(self.logFileName, "a") as f: + print(message, file=f) + + def execute(self, sql, parameters): + self._log(sql) + cursor = self.cursor() + try: + return cursor.execute(sql, parameters) + except cx_Oracle.Error as e: + errorObj, = e.args + self._log(errorObj.message) + + connection = Connection() + connection.execute(""" + select department_name + from departments + where department_id = :id""", dict(id=270)) + +The messages logged in ``log.txt`` are:: + + Connect to the database + + select department_name + from departments + where department_id = :id + +If an error occurs, perhaps due to a missing table, the log file would contain +instead:: + + Connect to the database + + select department_name + from departments + where department_id = :id + ORA-00942: table or view does not exist + +In production applications be careful not to log sensitive information. + +See `Subclassing.py +`__ for an example. + + +.. _endtoendtracing: + +Oracle Database End-to-End Tracing +================================== + +Oracle Database End-to-end application tracing simplifies diagnosing application +code flow and performance problems in multi-tier or multi-user environments. + +The connection attributes, :attr:`~Connection.client_identifier`, +:attr:`~Connection.clientinfo`, :attr:`~Connection.dbop`, +:attr:`~Connection.module` and :attr:`~Connection.action`, set the metadata for +end-to-end tracing. You can use data dictionary and ``V$`` views to monitor +tracing or use other application tracing utilities. + +The attributes are sent to the database when the next round-trip to the +database occurs, for example when the next SQL statement is executed. + +The attribute values will remain set in connections released back to connection +pools. When the application re-acquires a connection from the pool it should +initialize the values to a desired state before using that connection. + +The example below shows setting the action, module and client identifier +attributes on the connection object: + +.. code-block:: python + + # Set the tracing metadata + connection.client_identifier = "pythonuser" + connection.action = "Query Session tracing parameters" + connection.module = "End-to-end Demo" + + for row in cursor.execute(""" + SELECT username, client_identifier, module, action + FROM V$SESSION + WHERE username = 'SYSTEM'"""): + print(row) + +The output will be:: + + ('SYSTEM', 'pythonuser', 'End-to-end Demo', 'Query Session tracing parameters') + +The values can also be manually set as shown by calling +`DBMS_APPLICATION_INFO procedures +`__ +or `DBMS_SESSION.SET_IDENTIFIER +`__. These incur round-trips to +the database, however, reducing scalability. + +.. code-block:: sql + + BEGIN + DBMS_SESSION.SET_IDENTIFIER('pythonuser'); + DBMS_APPLICATION_INFO.set_module('End-to-End Demo'); + DBMS_APPLICATION_INFO.set_action(action_name => 'Query Session tracing parameters'); + END; + + +Low Level SQL Tracing in cx_Oracle +================================== + +cx_Oracle is implemented using the `ODPI-C `__ +wrapper on top of the Oracle Client libraries. The ODPI-C tracing capability +can be used to log executed cx_Oracle statements to the standard error stream. +Before executing Python, set the environment variable ``DPI_DEBUG_LEVEL`` to +16. + +At a Windows command prompt, this could be done with:: + + set DPI_DEBUG_LEVEL=16 + +On Linux, you might use:: + + export DPI_DEBUG_LEVEL=16 + +After setting the variable, run the Python Script, for example on Linux:: + + python end-to-endtracing.py 2> log.txt + +For an application that does a single query, the log file might contain a +tracing line consisting of the prefix 'ODPI', a thread identifier, a timestamp, +and the SQL statement executed:: + + ODPI [26188] 2019-03-26 09:09:03.909: ODPI-C 3.1.1 + ODPI [26188] 2019-03-26 09:09:03.909: debugging messages initialized at level 16 + ODPI [26188] 2019-03-26 09:09:09.917: SQL SELECT * FROM jobss + Traceback (most recent call last): + File "end-to-endtracing.py", line 14, in + cursor.execute("select * from jobss") + cx_Oracle.DatabaseError: ORA-00942: table or view does not exist + +See `ODPI-C Debugging +`__ for +documentation on ``DPI_DEBUG_LEVEL``. diff --git a/doc/src/user_guide/txn_management.rst b/doc/src/user_guide/txn_management.rst new file mode 100644 index 00000000..d243c18b --- /dev/null +++ b/doc/src/user_guide/txn_management.rst @@ -0,0 +1,77 @@ +.. _txnmgmnt: + +********************** +Transaction Management +********************** + +A database transaction is a grouping of SQL statements that make a logical data +change to the database. + +When :meth:`Cursor.execute()` executes a SQL statement, a transaction is +started or continued. By default, cx_Oracle does not commit this transaction +to the database. The methods :meth:`Connection.commit()` and +:meth:`Connection.rollback()` methods can be used to explicitly commit +or rollback a transaction: + +.. code-block:: python + + cursor.execute("INSERT INTO mytab (name) VALUES ('John')") + connection.commit() + +When a database connection is closed, such as with :meth:`Connection.close()`, +or when variables referencing the connection go out of scope, any uncommitted +transaction will be rolled back. + + +Autocommitting +============== + +An alternative way to commit is to set the attribute +:attr:`~Connection.autocommit` of the connection to ``True``. This ensures all +:ref:`DML ` statements (INSERT, UPDATE etc) are committed as they are +executed. Unlike :meth:`Connection.commit()`, this does not require an +additional round-trip to the database so it is more efficient when used +appropriately. + +Note that irrespective of the autocommit value, Oracle Database will always +commit an open transaction when a DDL statement is executed. + +When executing multiple DML statements that constitute a single transaction, it +is recommended to use autocommit mode only for the last DML statement in the +sequence of operations. Unnecessarily committing causes extra database load, +and can destroy transactional consistency. + +The example below shows a new customer being added to the table ``CUST_TABLE``. +The corresponding ``SALES`` table is updated with a purchase of 3000 pens from +the customer. The final insert uses autocommit mode to commit both new +records: + +.. code-block:: python + + # Add a new customer + idVar = cursor.var(int) + connection.autocommit = False # make sure any previous value is off + cursor.execute(""" + INSERT INTO cust_table (name) VALUES ('John') + RETURNING id INTO :bvid""", bvid=idVar) + + # Add sales data for the new customer and commit all new values + idVal = idVar.getvalue()[0] + connection.autocommit = True + cursor.execute("INSERT INTO sales_table VALUES (:bvid, 'pens', 3000)", + bvid=idVal) + + +Explicit Transactions +===================== + +The method :meth:`Connection.begin()` can be used to explicitly start a local +or global transaction. + +Without parameters, this explicitly begins a local transaction; otherwise, this +explicitly begins a distributed (global) transaction with the given parameters. +See the Oracle documentation for more details. + +Note that in order to make use of global (distributed) transactions, the +attributes :attr:`Connection.internal_name` and +:attr:`Connection.external_name` attributes must be set. diff --git a/doc/src/user_guide/xml_data_type.rst b/doc/src/user_guide/xml_data_type.rst new file mode 100644 index 00000000..77b7522e --- /dev/null +++ b/doc/src/user_guide/xml_data_type.rst @@ -0,0 +1,68 @@ +.. _xmldatatype: + +******************** +Working with XMLTYPE +******************** + +Oracle XMLType columns are fetched as strings by default. This is currently +limited to the maximum length of a ``VARCHAR2`` column. To return longer XML +values, they must be queried as LOB values instead. + +The examples below demonstrate using XMLType data with cx_Oracle. The +following table will be used in these examples: + +.. code-block:: sql + + CREATE TABLE xml_table ( + id NUMBER, + xml_data SYS.XMLTYPE + ); + +Inserting into the table can be done by simply binding a string as shown: + +.. code-block:: python + + xmlData = """ + + John Smith + 43 + Professor + Mathematics + """ + cursor.execute("insert into xml_table values (:id, :xml)", + id=1, xml=xmlData) + +This approach works with XML strings up to 1 GB in size. For longer strings, a +temporary CLOB must be created using :meth:`Connection.createlob()` and bound +as shown: + +.. code-block:: python + + clob = connection.createlob(cx_Oracle.CLOB) + clob.write(xmlData) + cursor.execute("insert into xml_table values (:id, sys.xmltype(:xml))", + id=2, xml=clob) + +Fetching XML data can be done simply for values that are shorter than the +length of a VARCHAR2 column, as shown: + +.. code-block:: python + + cursor.execute("select xml_data from xml_table where id = :id", id=1) + xmlData, = cursor.fetchone() + print(xmlData) # will print the string that was originally stored + +For values that exceed the length of a VARCHAR2 column, a CLOB must be returned +instead by using the function ``XMLTYPE.GETCLOBVAL()`` as shown: + +.. code-block:: python + + cursor.execute(""" + select xmltype.getclobval(xml_data) + from xml_table + where id = :id""", id=1) + clob, = cursor.fetchone() + print(clob.read()) + +The LOB that is returned can be streamed or a string can be returned instead of +a CLOB. See :ref:`lobdata` for more information about processing LOBs. diff --git a/doc/src/whatsnew.rst b/doc/src/whatsnew.rst deleted file mode 100644 index 6ca3b774..00000000 --- a/doc/src/whatsnew.rst +++ /dev/null @@ -1,171 +0,0 @@ -.. _whatsnew: - -********** -What's New -********** - -.. _whatsnew60: - -cx_Oracle 6.0 -============= - -This document contains a summary of the changes in cx_Oracle 6 compared to -cx_Oracle 5.3. cx_Oracle 6.0 was released on August 14, 2017. See the -:ref:`release notes ` for complete details. - -Highlights ----------- - - - Has been re-implemented to use the new - `ODPI-C `__ abstraction layer for Oracle - Database. The cx_Oracle API is unchanged. The cx_Oracle design, build and - linking process has improved because of ODPI-C. - - - Now has Python Wheels available for install. This is made possible by the - ODPI-C architecture. Windows installers and Linux RPMs are no longer - produced since PyPI no longer supports them. - - - Has less code in Python's Global Interpreter Lock, giving better - scalability. - - - Added support for universal rowids. - - - Added support for DML returning of multiple rows. - - - Now associates LOB locators to LOB objects so they are not overwritten on - database round trips. - - -Installation Changes --------------------- - - - On Linux, cx_Oracle 6 no longer uses instant client RPMs automatically. - You must set LD_LIBRARY_PATH or use ldconfig to locate the Oracle Client - library. - - - On platforms other than Windows, if ORACLE_HOME is set (in a database or - full client installation), remove requirement to set LD_LIBRARY_PATH in - order to locate the Oracle Client library - (`issue 20 `__). - - -Connection Management Enhancements ----------------------------------- - - - Prevent closing the connection when there are any open statements or LOBs - and add new error "DPI-1054: connection cannot be closed when open - statements or LOBs exist" when this situation is detected; this is needed - to prevent crashes under certain conditions when statements or LOBs are - being acted upon while at the same time (in another thread) a connection - is being closed; it also prevents leaks of statements and LOBs when a - connection is returned to a session pool. - - - Added attribute :attr:`SessionPool.stmtcachesize` to support getting and - setting the default statement cache size for connections in the pool. - - - Added attribute :attr:`Connection.dbop` to support setting the database - operation that is to be monitored. - - - Added attribute :attr:`Connection.handle` to facilitate testing the - creation of a connection using a OCI service context handle. - - - Added parameters tag and matchanytag to the :meth:`cx_Oracle.connect` and - :meth:`SessionPool.acquire` methods and added parameters tag and retag to - the :meth:`SessionPool.release` method in order to support session - tagging. - - - Added parameter edition to the :meth:`cx_Oracle.SessionPool` method. - - - Added parameters region, sharding_key and super_sharding_key to the - :meth:`cx_Oracle.makedsn()` method to support connecting to a sharded - database (new in Oracle Database 12.2). - - - Removed requirement that encoding and nencoding both be specified when - creating a connection or session pool. The missing value is set to its - default value if one of the values is set and the other is not - (`issue 36 `__). - - - Permit use of both string and unicode for Python 2.7 for creating session - pools and for changing passwords - (`issue 23 `__). - - -Data Type and Data Handling Enhancements ----------------------------------------- - - - Added attributes :attr:`Variable.actualElements` and - :attr:`Variable.values` to variables. - - - Added support for smallint and float data types in Oracle objects, as - requested - (`issue 4 `__). - - - Added support for getting/setting attributes of objects or element values - in collections that contain LOBs, BINARY_FLOAT values, BINARY_DOUBLE - values and NCHAR and NVARCHAR2 values. The error message for any types - that are not supported has been improved as well. - - - An exception is no longer raised when a collection is empty for methods - :meth:`Object.first()` and :meth:`Object.last()`. Instead, the value None - is returned to be consistent with the methods :meth:`Object.next()` and - :meth:`Object.prev()`. - - - Removed requirement for specifying a maximum size when fetching LONG or - LONG raw columns. This also allows CLOB, NCLOB, BLOB and BFILE columns to - be fetched as strings or bytes without needing to specify a maximum size. - The method :meth:`Cursor.setoutputsize` no longer does anything, since - ODPI-C automatically manages buffer sizes of LONG and LONG RAW columns. - - - Enable temporary LOB caching in order to avoid disk I/O as suggested - (`issue 10 `__). - - -Error Handling Enhancements ---------------------------- - - - Provide improved error message when OCI environment cannot be created, - such as when the oraaccess.xml file cannot be processed properly. - - - Define exception classes on the connection object in addition to at - module scope in order to simplify error handling in multi-connection - environments, as specified in the Python DB API. - - -Test Enhancements ------------------ - - - Reworked test suite and samples so that they are independent of each - other and so that the SQL scripts used to create/drop schemas are easily - adjusted to use different schema names, if desired. - - - Updated DB API test suite stub to support Python 3. - - -Removals --------- - - - Dropped deprecated parameter twophase from the :meth:`cx_Oracle.connect` - method. Applications should set the :attr:`Connection.internal_name` and - :attr:`Connection.external_name` attributes instead to a value - appropriate to the application. - - - Dropped deprecated parameters action, module and clientinfo from the - :meth:`cx_Oracle.connect` method. The appcontext parameter should be used - instead as shown in this `sample `__. - - - Dropped deprecated attribute numbersAsString from - :ref:`cursor objects `. Use an output type handler instead as - shown in this `sample `__. - - - Dropped deprecated attributes cqqos and rowids from - :ref:`subscription objects `. Use the qos attribute instead as - shown in this `sample `__. - - - Dropped deprecated parameters cqqos and rowids from the - :meth:`Connection.subscribe()` method. Use the qos parameter instead as - shown in this `sample `__. - diff --git a/odpi b/odpi index 34da7033..f355af15 160000 --- a/odpi +++ b/odpi @@ -1 +1 @@ -Subproject commit 34da7033238754efc64ffbc7f006b5ba47fc6e9b +Subproject commit f355af1591ee41799af5ed24f5cc26e4528463f8 diff --git a/samples/SampleEnv.py b/samples/SampleEnv.py index cec56c08..d0fdc1bf 100644 --- a/samples/SampleEnv.py +++ b/samples/SampleEnv.py @@ -29,7 +29,7 @@ # [//]host_name[:port][/service_name][:server_type][/instance_name] # # Commonly just the host_name and service_name are needed -# e.g. "localhost/orclpdb" or "localhost/XE" +# e.g. "localhost/orclpdb1" or "localhost/XEPDB1" # # If using a tnsnames.ora file, the file can be in a default # location such as $ORACLE_HOME/network/admin/tnsnames.ora or @@ -53,7 +53,7 @@ DEFAULT_MAIN_USER = "pythondemo" DEFAULT_EDITION_USER = "pythoneditions" DEFAULT_EDITION_NAME = "python_e1" -DEFAULT_CONNECT_STRING = "localhost/orclpdb" +DEFAULT_CONNECT_STRING = "localhost/orclpdb1" # dictionary containing all parameters; these are acquired as needed by the # methods below (which should be used instead of consulting this dictionary diff --git a/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html b/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html index 31ea9b15..557ae6fc 100644 --- a/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html +++ b/samples/tutorial/Python-and-Oracle-Database-Scripting-for-the-Future.html @@ -92,6 +92,12 @@

Contents

  • 10.1 Message passing with Oracle Advanced Queuing
    • +
  • 11. Simple Oracle Document Access (SODA) +
      +
    • 11.1 Inserting JSON Documents
      • +
    • 11.2 Searching SODA Documents
      • +
    +
  • Summary
  • Appendix: Python Primer
    • @@ -100,24 +106,27 @@

    Contents

    Preface

    -

    If you are running this tutorial in your own environment, install the following required software:

    +

    If you are running this tutorial in your own environment, install the required software:

      -
    1. Python (3.6 preferred but 2.7 should work)
      2. -
    2. cx_Oracle (version 7.2 preferred but 6.3 or later should work, except for the section on Advanced Queuing which requires version 7.2 or later) and Oracle Instant Client Package - Basic (version 19.3 preferred but 18.3 or 12.2 should also work) +

    3. Python. Version 3.6 is preferred.

      4. +

    4. cx_Oracle version 7.2 and the Oracle Client libraries.

      5. -
    5. Oracle Instant Client Package - SQL*Plus.
      6. + +

    6. SQL*Plus such as from the Oracle Instant Client SQL*Plus Package.

    +

    The Advanced Queuing section requires Oracle client 12.2 or later. The SODA section requires Oracle client 18.5, or later, and Oracle Database 18 or later.

    +

    To create the schema run:

    -sqlplus sys/yoursyspassword@localhost/orclpdb as sysdba @sql/SetupSamples
+sqlplus sys/yoursyspassword@localhost/orclpdb1 as sysdba @sql/SetupSamples

    Connection Information

    @@ -130,11 +139,11 @@

    Connection Information

    The username is "pythonhol" with - the password "welcome". The connect string is "localhost/orclpdb". + the password "welcome". The connect string is "localhost/orclpdb1". See sql/SampleEnv.sql.

    It is easist to have a local pluggable database with the service - 'orclpdb' configured. If your database is not local, or has a + 'orclpdb1' configured. If your database is not local, or has a different service, you will need to modify the connection information in db_config.py and db_config.sql.

    The following sections may need adjusting, depending on how you @@ -174,16 +183,16 @@

    1.1 Review the connection credentials

     user = "pythonhol"
 pw = "welcome"
-dsn = "localhost/orclpdb"
+dsn = "localhost/orclpdb1"
    db_config.sql 
     def user = "pythonhol"
 def pw = "welcome"
-def connect_string = "localhost/orclpdb"
+def connect_string = "localhost/orclpdb1"
    -

    By default they connect to the 'orclpdb' database service on the same machine as Python. You can modify the values in both files to match the connection information for your environment.

    +

    By default they connect to the 'orclpdb1' database service on the same machine as Python. You can modify the values in both files to match the connection information for your environment.

    @@ -207,7 +216,7 @@

    1.2 Creating a basic connection

    the db_config.py module. In this case, Oracle's Easy Connect connection string syntax is used. It consists of the hostname of your machine, localhost, and the database service name - orclpdb.

    + orclpdb1.

    Open a command terminal and change to the tutorial directory:

    @@ -1128,7 +1137,7 @@

    2.5 More DRCP investigation

    In a terminal window, start SQL*Plus using the lab credentials and connection string, such as:

    -sqlplus pythonhol/welcome@localhost/orclpdb
+sqlplus pythonhol/welcome@localhost/orclpdb1

    Use the SQL*Plus DESCRIBE command to look at the SDO definition:

    @@ -1661,6 +1670,9 @@

    6.1 Basic output type handler

  • 7. LOBs

    +

    Oracle Database "LOB" long objects can be streamed using a LOB + locator, or worked with directly as strings or bytes.

    +

    • 7.1 Fetching a CLOB using a locator

      @@ -1694,8 +1706,8 @@

      7.1 Fetching a CLOB using a locator

      This inserts some test string data and then fetches one - record into clob, which is a cx_Oracle LOB Object. - Methods on LOB include size() and + record into clob, which is a cx_Oracle character + LOB Object. Methods on LOB include size() and read().

      To see the output, run the file:

      @@ -1947,7 +1959,7 @@

      7.2 Fetching a CLOB as a string

    • 10. Advanced Queuing

        -

      • 10.1 Message passing with Oracle Advanced Queuing

        • +

      • 10.1 Message passing with Oracle Advanced Queuing

        Review aq.py:

        @@ -2074,6 +2086,114 @@

        7.2 Fetching a CLOB as a string

      • + + +

    • 11. Simple Oracle Document Access (SODA)

      + +

      Simple Oracle Document Access is a set of NoSQL-style APIs. + Documents can be inserted, queried, and retrieved from Oracle + Database. By default, documents are JSON strings. SODA APIs + exist in many languages.

      + +
        + +

      • 11.1 Inserting JSON Documents

        + +

        Review soda.py:

        + +
        +import cx_Oracle
+import db_config
+
+con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)
+
+soda = con.getSodaDatabase()
+
+collection = soda.createCollection("friends")
+
+content = {'name': 'Jared', 'age': 35, 'address': {'city': 'Melbourne'}}
+
+doc = collection.insertOneAndGet(content)
+key = doc.key
+
+doc = collection.find().key(key).getOne()
+content = doc.getContent()
+print('Retrieved SODA document dictionary is:')
+print(content)
+
        + +

        soda.createCollection() will create a new + collection, or open an existing collection, if the name is + already in use.

        + +

        insertOneAndGet() inserts the content of a + document into the database and returns a SODA Document Object. + This allows access to meta data such as the document key. By + default, document keys are automatically generated.

        + +

        The find() method is used to begin an operation + that will act upon documents in the collection.

        + +

        content is a dictionary. You can also get a JSON string + by calling doc.getContentAsString().

        + +

        Run the file:

        + +
        python soda.py
        + +

        The output shows the content of the new document.

        + +
        • + +

      • 11.2 Searching SODA Documents

        + +

        Extend soda.py to insert some more documents and + perform a find filter operation:

        + +
        +myDocs = [
+    {'name': 'Gerald', 'age': 21, 'address': {'city': 'London'}},
+    {'name': 'David', 'age': 28, 'address': {'city': 'Melbourne'}},
+    {'name': 'Shawn', 'age': 20, 'address': {'city': 'San Francisco'}}
+]
+collection.insertMany(myDocs)
+
+filterSpec = { "address.city": "Melbourne" }
+myDocuments = collection.find().filter(filterSpec).getDocuments()
+
+print('Melbourne people:')
+for doc in myDocuments:
+    print(doc.getContent()["name"])
+
        + +

        Run the script again:

        + + 
        python soda.py
        + +

        The find operation filters the collection and returns + documents where the city is Melbourne. Note the + insertMany() method is currently in preview.

        + +

        SODA supports query by example (QBE) with an extensive set of + operators. Extend soda.py with a QBE to find + documents where the age is less than 25:

        + +
        +filterSpec = {'age': {'$lt': 25}}
+myDocuments = collection.find().filter(filterSpec).getDocuments()
+
+print('Young people:')
+for doc in myDocuments:
+    print(doc.getContent()["name"])
+
        + +

        Running the script displays the names.

        + +
        • +
      + +
      • +

      Summary

      @@ -2307,7 +2427,7 @@

      Resources

      - + diff --git a/samples/tutorial/db_config.py b/samples/tutorial/db_config.py index 8e785678..0ca76f94 100644 --- a/samples/tutorial/db_config.py +++ b/samples/tutorial/db_config.py @@ -1,3 +1,3 @@ user = "pythonhol" pw = "welcome" -dsn = "localhost/orclpdb" +dsn = "localhost/orclpdb1" diff --git a/samples/tutorial/db_config.sql b/samples/tutorial/db_config.sql index 0f02b1b4..d1d49ff5 100644 --- a/samples/tutorial/db_config.sql +++ b/samples/tutorial/db_config.sql @@ -1,3 +1,3 @@ def user = "pythonhol" def pw = "welcome" -def connect_string = "localhost/orclpdb" +def connect_string = "localhost/orclpdb1" diff --git a/samples/tutorial/soda.py b/samples/tutorial/soda.py new file mode 100644 index 00000000..25c2ca51 --- /dev/null +++ b/samples/tutorial/soda.py @@ -0,0 +1,28 @@ +#------------------------------------------------------------------------------ +# soda.py (Section 11.1) +#------------------------------------------------------------------------------ + +#------------------------------------------------------------------------------ +# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved. +#------------------------------------------------------------------------------ + +from __future__ import print_function + +import cx_Oracle +import db_config + +con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn) + +soda = con.getSodaDatabase() + +collection = soda.createCollection("friends") + +content = {'name': 'Jared', 'age': 35, 'address': {'city': 'Melbourne'}} + +doc = collection.insertOneAndGet(content) +key = doc.key + +doc = collection.find().key(key).getOne() +content = doc.getContent() +print('Retrieved SODA document dictionary is:') +print(content) diff --git a/samples/tutorial/solutions/soda.py b/samples/tutorial/solutions/soda.py new file mode 100644 index 00000000..511403cf --- /dev/null +++ b/samples/tutorial/solutions/soda.py @@ -0,0 +1,49 @@ +#------------------------------------------------------------------------------ +# soda.py (Section 11.2) +#------------------------------------------------------------------------------ + +#------------------------------------------------------------------------------ +# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved. +#------------------------------------------------------------------------------ + +from __future__ import print_function + +import cx_Oracle +import db_config + +con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn) + +soda = con.getSodaDatabase() + +collection = soda.createCollection("friends") + +content = {'name': 'Jared', 'age': 35, 'address': {'city': 'Melbourne'}} + +doc = collection.insertOneAndGet(content) +key = doc.key + +doc = collection.find().key(key).getOne() +content = doc.getContent() +print('Retrieved SODA document dictionary is:') +print(content) + +myDocs = [ + {'name': 'Gerald', 'age': 21, 'address': {'city': 'London'}}, + {'name': 'David', 'age': 28, 'address': {'city': 'Melbourne'}}, + {'name': 'Shawn', 'age': 20, 'address': {'city': 'San Francisco'}} +] +collection.insertMany(myDocs) + +filterSpec = { "address.city": "Melbourne" } +myDocuments = collection.find().filter(filterSpec).getDocuments() + +print('Melbourne people:') +for doc in myDocuments: + print(doc.getContent()["name"]) + +filterSpec = {'age': {'$lt': 25}} +myDocuments = collection.find().filter(filterSpec).getDocuments() + +print('Young people:') +for doc in myDocuments: + print(doc.getContent()["name"]) diff --git a/samples/tutorial/sql/SetupSamples.sql b/samples/tutorial/sql/SetupSamples.sql index 47945671..33a8eba6 100644 --- a/samples/tutorial/sql/SetupSamples.sql +++ b/samples/tutorial/sql/SetupSamples.sql @@ -38,6 +38,19 @@ to &main_user; grant execute on dbms_aqadm to &main_user; grant execute on dbms_lock to &main_user; +begin + + for r in + ( select role + from dba_roles + where role in ('SODA_APP') + ) loop + execute immediate 'grant ' || r.role || ' to &main_user'; + end loop; + +end; +/ + create table &main_user..testclobs ( id number not null, myclob clob not null diff --git a/setup.py b/setup.py index 3d817376..f2ec1a80 100644 --- a/setup.py +++ b/setup.py @@ -20,7 +20,7 @@ from distutils.extension import Extension # define build constants -BUILD_VERSION = "7.2.0" +BUILD_VERSION = "7.2.3" # setup extra link and compile args extraLinkArgs = [] diff --git a/src/cxoCursor.c b/src/cxoCursor.c index ac0302e8..d6cc3c7b 100644 --- a/src/cxoCursor.c +++ b/src/cxoCursor.c @@ -414,7 +414,7 @@ static int cxoCursor_performDefine(cxoCursor *cursor, uint32_t numQueryColumns) // if using an output type handler, None implies default behavior if (outputTypeHandler) { result = PyObject_CallFunction(outputTypeHandler, "Os#Oiii", - cursor, queryInfo.name, queryInfo.nameLength, + cursor, queryInfo.name, (Py_ssize_t) queryInfo.nameLength, varType->pythonType, size, queryInfo.typeInfo.precision, queryInfo.typeInfo.scale); if (!result) { diff --git a/src/cxoTransform.c b/src/cxoTransform.c index 0dc2c81a..f02e7af1 100644 --- a/src/cxoTransform.c +++ b/src/cxoTransform.c @@ -274,22 +274,28 @@ int cxoTransform_fromPython(cxoTransformNum transformNum, case CXO_TRANSFORM_INT: case CXO_TRANSFORM_DECIMAL: case CXO_TRANSFORM_FLOAT: - if (!PyFloat_Check(pyValue) && + if (PyBool_Check(pyValue)) { + buffer->ptr = (pyValue == Py_True) ? "1" : "0"; + buffer->size = 1; + buffer->numCharacters = 1; + } else { + if (!PyFloat_Check(pyValue) && #if PY_MAJOR_VERSION < 3 - !PyInt_Check(pyValue) && + !PyInt_Check(pyValue) && #endif - !PyLong_Check(pyValue) && - !PyObject_TypeCheck(pyValue, cxoPyTypeDecimal)) { - PyErr_SetString(PyExc_TypeError, "expecting number"); - return -1; + !PyLong_Check(pyValue) && + !PyObject_TypeCheck(pyValue, cxoPyTypeDecimal)) { + PyErr_SetString(PyExc_TypeError, "expecting number"); + return -1; + } + textValue = PyObject_Str(pyValue); + if (!textValue) + return -1; + status = cxoBuffer_fromObject(buffer, textValue, encoding); + Py_DECREF(textValue); + if (status < 0) + return -1; } - textValue = PyObject_Str(pyValue); - if (!textValue) - return -1; - status = cxoBuffer_fromObject(buffer, textValue, encoding); - Py_DECREF(textValue); - if (status < 0) - return -1; dbValue->asBytes.ptr = (char*) buffer->ptr; dbValue->asBytes.length = buffer->size; return 0; diff --git a/test/BulkAQ.py b/test/BulkAQ.py new file mode 100644 index 00000000..5abbc31c --- /dev/null +++ b/test/BulkAQ.py @@ -0,0 +1,133 @@ +#------------------------------------------------------------------------------ +# Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved. +#------------------------------------------------------------------------------ + +"""Module for testing AQ Bulk enqueue/dequeue.""" + +import TestEnv + +import cx_Oracle +import decimal +import threading + +RAW_QUEUE_NAME = "TEST_RAW_QUEUE" +RAW_PAYLOAD_DATA = [ + "The first message", + "The second message", + "The third message", + "The fourth message", + "The fifth message", + "The sixth message", + "The seventh message", + "The eighth message", + "The ninth message", + "The tenth message", + "The eleventh message", + "The twelfth and final message" +] + +class TestCase(TestEnv.BaseTestCase): + + def __deqInThread(self, results): + connection = TestEnv.GetConnection(threaded=True) + queue = connection.queue(RAW_QUEUE_NAME) + queue.deqOptions.wait = 10 + queue.deqOptions.navigation = cx_Oracle.DEQ_FIRST_MSG + while len(results) < len(RAW_PAYLOAD_DATA): + messages = queue.deqMany(5) + if not messages: + break + for m in messages: + results.append(m.payload.decode(connection.encoding)) + connection.commit() + + def __getAndClearRawQueue(self): + queue = self.connection.queue(RAW_QUEUE_NAME) + queue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT + queue.deqOptions.navigation = cx_Oracle.DEQ_FIRST_MSG + while queue.deqOne(): + pass + self.connection.commit() + return queue + + def testEnqAndDeq(self): + "test bulk enqueue and dequeue" + queue = self.__getAndClearRawQueue() + messages = [self.connection.msgproperties(payload=d) \ + for d in RAW_PAYLOAD_DATA] + queue.enqMany(messages) + messages = queue.deqMany(len(RAW_PAYLOAD_DATA)) + data = [m.payload.decode(self.connection.encoding) for m in messages] + self.connection.commit() + self.assertEqual(data, RAW_PAYLOAD_DATA) + + def testDequeueEmpty(self): + "test empty bulk dequeue" + queue = self.__getAndClearRawQueue() + messages = queue.deqMany(5) + self.connection.commit() + self.assertEqual(messages, []) + + def testDeqWithWait(self): + "test bulk dequeue with wait" + queue = self.__getAndClearRawQueue() + results = [] + thread = threading.Thread(target=self.__deqInThread, args=(results,)) + thread.start() + messages = [self.connection.msgproperties(payload=d) \ + for d in RAW_PAYLOAD_DATA] + queue.enqOptions.visibility = cx_Oracle.ENQ_IMMEDIATE + queue.enqMany(messages) + thread.join() + self.assertEqual(results, RAW_PAYLOAD_DATA) + + def testEnqAndDeqMultipleTimes(self): + "test enqueue and dequeue multiple times" + queue = self.__getAndClearRawQueue() + dataToEnqueue = RAW_PAYLOAD_DATA + for num in (2, 6, 4): + messages = [self.connection.msgproperties(payload=d) \ + for d in dataToEnqueue[:num]] + dataToEnqueue = dataToEnqueue[num:] + queue.enqMany(messages) + self.connection.commit() + allData = [] + for num in (3, 5, 10): + messages = queue.deqMany(num) + allData.extend(m.payload.decode(self.connection.encoding) \ + for m in messages) + self.connection.commit() + self.assertEqual(allData, RAW_PAYLOAD_DATA) + + def testEnqAndDeqVisibility(self): + "test visibility option for enqueue and dequeue" + queue = self.__getAndClearRawQueue() + + # first test with ENQ_ON_COMMIT (commit required) + queue.enqOptions.visibility = cx_Oracle.ENQ_ON_COMMIT + props1 = self.connection.msgproperties(payload="A first message") + props2 = self.connection.msgproperties(payload="A second message") + queue.enqMany([props1, props2]) + otherConnection = TestEnv.GetConnection() + otherQueue = otherConnection.queue(RAW_QUEUE_NAME) + otherQueue.deqOptions.wait = cx_Oracle.DEQ_NO_WAIT + otherQueue.deqOptions.visibility = cx_Oracle.DEQ_ON_COMMIT + messages = otherQueue.deqMany(5) + self.assertEqual(len(messages), 0) + self.connection.commit() + messages = otherQueue.deqMany(5) + self.assertEqual(len(messages), 2) + otherConnection.rollback() + + # second test with ENQ_IMMEDIATE (no commit required) + queue.enqOptions.visibility = cx_Oracle.ENQ_IMMEDIATE + otherQueue.deqOptions.visibility = cx_Oracle.DEQ_IMMEDIATE + queue.enqMany([props1, props2]) + messages = otherQueue.deqMany(5) + self.assertEqual(len(messages), 4) + otherConnection.rollback() + messages = otherQueue.deqMany(5) + self.assertEqual(len(messages), 0) + +if __name__ == "__main__": + TestEnv.RunTestCases() diff --git a/test/Cursor.py b/test/Cursor.py index 6bb943ab..c57bb2d1 100644 --- a/test/Cursor.py +++ b/test/Cursor.py @@ -267,6 +267,7 @@ def testExecuteManyNoParameters(self): insert into TestTempTable (IntCol, StringCol) values (t_Id, 'Test String ' || t_Id); end;""", numRows) + self.assertEqual(self.cursor.rowcount, numRows) self.cursor.execute("select count(*) from TestTempTable") count, = self.cursor.fetchone() self.assertEqual(count, numRows) @@ -290,6 +291,7 @@ def testExecuteManyBoundEarlier(self): select sum(IntCol) into :1 from TestTempTable; end;""", numRows) + self.assertEqual(self.cursor.rowcount, numRows) expectedData = [1, 3, 6, 10, 15, 21, 28, 36, 45] self.assertEqual(var.values, expectedData) @@ -697,4 +699,3 @@ def testFetchXMLType(self): if __name__ == "__main__": TestEnv.RunTestCases() - diff --git a/test/Features12_1.py b/test/Features12_1.py index 9d9ed287..b4cd7f04 100644 --- a/test/Features12_1.py +++ b/test/Features12_1.py @@ -287,6 +287,7 @@ def testExecutingDelete(self): statement = "delete from TestArrayDML where IntCol2 = :1" self.cursor.executemany(statement, rows, arraydmlrowcounts = True) self.assertEqual(self.cursor.getarraydmlrowcounts(), [1, 3, 2]) + self.assertEqual(self.cursor.rowcount, 6) def testExecutingUpdate(self): "test executing update statement with arraydmlrowcount mode" @@ -309,6 +310,7 @@ def testExecutingUpdate(self): sql = "update TestArrayDML set StringCol = :1 where IntCol2 = :2" self.cursor.executemany(sql, rows, arraydmlrowcounts = True) self.assertEqual(self.cursor.getarraydmlrowcounts(), [1, 1, 3, 2]) + self.assertEqual(self.cursor.rowcount, 7) def testImplicitResults(self): "test getimplicitresults() returns the correct data" diff --git a/test/NumberVar.py b/test/NumberVar.py index 31de4cab..4be4928b 100644 --- a/test/NumberVar.py +++ b/test/NumberVar.py @@ -50,6 +50,18 @@ def testBindBoolean(self): (True,)) self.assertEqual(result, "TRUE") + def testBindBooleanAsNumber(self): + "test binding in a boolean as a number" + var = self.cursor.var(cx_Oracle.NUMBER) + var.setvalue(0, True) + self.cursor.execute("select :1 from dual", [var]) + result, = self.cursor.fetchone() + self.assertEqual(result, 1) + var.setvalue(0, False) + self.cursor.execute("select :1 from dual", [var]) + result, = self.cursor.fetchone() + self.assertEqual(result, 0) + def testBindDecimal(self): "test binding in a decimal.Decimal" self.cursor.execute(""" diff --git a/test/TestEnv.py b/test/TestEnv.py index 38fc7e54..3a5d238e 100644 --- a/test/TestEnv.py +++ b/test/TestEnv.py @@ -33,7 +33,7 @@ # [//]host_name[:port][/service_name][:server_type][/instance_name] # # Commonly just the host_name and service_name are needed -# e.g. "localhost/orclpdb" or "localhost/XE" +# e.g. "localhost/orclpdb1" or "localhost/XEPDB1" # # If using a tnsnames.ora file, the file can be in a default # location such as $ORACLE_HOME/network/admin/tnsnames.ora or @@ -58,7 +58,7 @@ # default values DEFAULT_MAIN_USER = "pythontest" DEFAULT_PROXY_USER = "pythontestproxy" -DEFAULT_CONNECT_STRING = "localhost/orclpdb" +DEFAULT_CONNECT_STRING = "localhost/orclpdb1" # dictionary containing all parameters; these are acquired as needed by the # methods below (which should be used instead of consulting this dictionary diff --git a/test/sql/SetupTestExec.sql b/test/sql/SetupTestExec.sql index a21e56b6..99678a47 100644 --- a/test/sql/SetupTestExec.sql +++ b/test/sql/SetupTestExec.sql @@ -260,6 +260,11 @@ begin dbms_aqadm.create_queue('&main_user..TEST_BOOK_QUEUE', '&main_user..BOOK_QUEUE_TAB'); dbms_aqadm.start_queue('&main_user..TEST_BOOK_QUEUE'); + + dbms_aqadm.create_queue_table('&main_user..RAW_QUEUE_TAB', 'RAW'); + dbms_aqadm.create_queue('&main_user..TEST_RAW_QUEUE', + '&main_user..RAW_QUEUE_TAB'); + dbms_aqadm.start_queue('&main_user..TEST_RAW_QUEUE'); end; / diff --git a/test/test.py b/test/test.py index 8e4b0e9b..33f617b2 100644 --- a/test/test.py +++ b/test/test.py @@ -48,6 +48,7 @@ "StringVar", "TimestampVar", "AQ", + "BulkAQ", "Rowid", "Subscription" ]
      Copyright © 2017, Oracle and/or its affiliates. All rights reservedCopyright © 2017, 2019, Oracle and/or its affiliates. All rights reserved