Connection Object

Note

Any outstanding changes will be rolled back when the connection object is destroyed or closed.

Connection.__enter__()

The entry point for the connection as a context manager. It returns itself.

Note

This method is an extension to the DB API definition.

Connection.__exit__()

The exit point for the connection as a context manager. This will close the connection and roll back any uncomitted transaction.

Note

This method is an extension to the DB API definition.

Connection.action

This write-only attribute sets the action column in the v$session table. It is a string attribute and cannot be set to None – use the empty string instead.

Note

This attribute is an extension to the DB API definition.

Connection.autocommit

This read-write attribute determines whether autocommit mode is on or off. When autocommit mode is on, all statements are committed as soon as they have completed executing.

Note

This attribute is an extension to the DB API definition.

Connection.begin([formatId, transactionId, branchId])

Explicitly begin a new 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 internal_name and external_name attributes must be set.

Note

This method is an extension to the DB API definition.

Connection.callTimeout

This read-write attribute specifies the amount of time (in milliseconds) that a single round-trip to the database may take before a timeout will occur. A value of 0 means that no timeout will take place.

Note

This attribute is an extension to the DB API definition and is only available in Oracle Client 18c and higher.

Connection.cancel()

Cancel a long-running transaction.

Note

This method is an extension to the DB API definition.

Connection.changepassword(oldpassword, newpassword)

Change the password of the logon.

Note

This method is an extension to the DB API definition.

Connection.client_identifier

This write-only attribute sets the client_identifier column in the v$session table.

Note

This attribute is an extension to the DB API definition.

Connection.clientinfo

This write-only attribute sets the client_info column in the v$session table.

Note

This attribute is an extension to the DB API definition.

Connection.close()

Close the connection now, rather than whenever __del__ is called. The connection will be unusable from this point forward; an Error exception will be raised if any operation is attempted with the connection.

All open cursors and LOBs created by the connection will be closed and will also no longer be usable.

Internally, references to the connection are held by cursor objects, LOB objects, subscription objects, etc. Once all of these references are released, the connection itself will be closed automatically. Either control references to these related objects carefully or explicitly close connections in order to ensure sufficient resources are available.

Connection.commit()

Commit any pending transactions to the database.

Connection.createlob(lobType)

Create and return a new temporary LOB object of the specified type. The lobType parameter should be one of cx_Oracle.CLOB, cx_Oracle.BLOB or cx_Oracle.NCLOB.

New in version 6.2.

Note

This method is an extension to the DB API definition.

Connection.current_schema

This read-write attribute sets the current schema attribute for the session. Setting this value is the same as executing the SQL statement “ALTER SESSION SET CURRENT_SCHEMA”. The attribute is set (and verified) on the next call that does a round trip to the server. The value is placed before unqualified database objects in SQL statements you then execute.

Note

This attribute is an extension to the DB API definition.

Connection.cursor()

Return a new cursor object using the connection.

Connection.dbop

This write-only attribute sets the database operation that is to be monitored. This can be viewed in the DBOP_NAME column of the V$SQL_MONITOR table.

Note

This attribute is an extension to the DB API definition.

Connection.deq(name, options, msgproperties, payload)

Returns a message id after successfully dequeuing a message. The options object can be created using deqoptions() and the msgproperties object can be created using msgproperties(). The payload must be an object created using ObjectType.newobject().

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.deqoptions()

Returns an object specifying the options to use when dequeuing messages. See Dequeue Options for more information.

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.dsn

This read-only attribute returns the TNS entry of the database to which a connection has been established.

Note

This attribute is an extension to the DB API definition.

Connection.edition

This read-only attribute gets the session edition and is only available in Oracle Database 11.2 (both client and server must be at this level or higher for this to work).

New in version 5.3.

Note

This attribute is an extension to the DB API definition.

Connection.encoding

This read-only attribute returns the IANA character set name of the character set in use by the Oracle client for regular strings.

Note

This attribute is an extension to the DB API definition.

Connection.enq(name, options, msgproperties, payload)

Returns a message id after successfully enqueuing a message. The options object can be created using enqoptions() and the msgproperties object can be created using msgproperties(). The payload must be an object created using ObjectType.newobject().

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.enqoptions()

Returns an object specifying the options to use when enqueuing messages. See Enqueue Options for more information.

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.external_name

This read-write attribute specifies the external name that is used by the connection when logging distributed transactions.

New in version 5.3.

Note

This attribute is an extension to the DB API definition.

Connection.getSodaDatabase()

Return a SodaDatabase object for Simple Oracle Document Access (SODA). All SODA operations are performed either on the returned SodaDatabase object or from objects created by the returned SodaDatabase object. See here for additional information on SODA.

SODA support in cx_Oracle is in Preview status and should not be used in production. It will be supported with a future version of Oracle Client libraries.

New in version 7.0.

Note

This method is an extension to the DB API definition.

Connection.gettype(name)

Return a type object given its name. This can then be used to create objects which can be bound to cursors created by this connection.

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.handle

This read-only attribute returns the OCI service context handle for the connection. It is primarily provided to facilitate testing the creation of a connection using the OCI service context handle.

Note

This attribute is an extension to the DB API definition.

Connection.inputtypehandler

This read-write attribute specifies a method called for each value that is bound to a statement executed on any cursor associated with this connection. The method signature is handler(cursor, value, arraysize) and the return value is expected to be a variable object or None in which case a default variable object will be created. If this attribute is None, the default behavior will take place for all values bound to statements.

Note

This attribute is an extension to the DB API definition.

Connection.internal_name

This read-write attribute specifies the internal name that is used by the connection when logging distributed transactions.

New in version 5.3.

Note

This attribute is an extension to the DB API definition.

Connection.ltxid

This read-only attribute returns the logical transaction id for the connection. It is used within Oracle Transaction Guard as a means of ensuring that transactions are not duplicated. See the Oracle documentation and the provided sample for more information.

New in version 5.3.

Connection.maxBytesPerCharacter

This read-only attribute returns the maximum number of bytes each character can use for the client character set.

Note

This attribute is an extension to the DB API definition.

Connection.module

This write-only attribute sets the module column in the v$session table. The maximum length for this string is 48 and if you exceed this length you will get ORA-24960.

Connection.msgproperties()

Returns an object specifying the properties of messages used in advanced queuing. See Message Properties for more information.

New in version 5.3.

Note

This method is an extension to the DB API definition.

Connection.nencoding

This read-only attribute returns the IANA character set name of the national character set in use by the Oracle client.

Note

This attribute is an extension to the DB API definition.

Connection.outputtypehandler

This read-write attribute specifies a method called for each column that is going to be fetched from any cursor associated with this connection. The method signature is handler(cursor, name, defaultType, length, precision, scale) and the return value is expected to be a variable object or None in which case a default variable object will be created. If this attribute is None, the default behavior will take place for all columns fetched from cursors.

Note

This attribute is an extension to the DB API definition.

Connection.ping()

Ping the server which can be used to test if the connection is still active.

Note

This method is an extension to the DB API definition.

Connection.prepare()

Prepare the distributed (global) transaction for commit. Return a boolean indicating if a transaction was actually prepared in order to avoid the error ORA-24756 (transaction does not exist).

Note

This method is an extension to the DB API definition.

Connection.rollback()

Rollback any pending transactions.

Connection.shutdown([mode])

Shutdown the database. In order to do this the connection must be connected as SYSDBA or SYSOPER. Two calls must be made unless the mode specified is DBSHUTDOWN_ABORT. An example is shown below:

import cx_Oracle

connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
connection.shutdown(mode = cx_Oracle.DBSHUTDOWN_IMMEDIATE)
cursor = connection.cursor()
cursor.execute("alter database close normal")
cursor.execute("alter database dismount")
connection.shutdown(mode = cx_Oracle.DBSHUTDOWN_FINAL)

Note

This method is an extension to the DB API definition.

Connection.startup(force=False, restrict=False)

Startup the database. This is equivalent to the SQL*Plus command “startup nomount”. The connection must be connected as SYSDBA or SYSOPER with the PRELIM_AUTH option specified for this to work. An example is shown below:

import cx_Oracle

connection = cx_Oracle.connect(
        mode=cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH)
connection.startup()
connection = cx_Oracle.connect(mode=cx_Oracle.SYSDBA)
cursor = connection.cursor()
cursor.execute("alter database mount")
cursor.execute("alter database open")

Note

This method is an extension to the DB API definition.

Connection.stmtcachesize

This read-write attribute specifies the size of the statement cache. This value can make a significant difference in performance (up to 100x) if you have a small number of statements that you execute repeatedly.

Note

This attribute is an extension to the DB API definition.

Connection.subscribe(namespace=cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE, protocol=cx_Oracle.SUBSCR_PROTO_OCI, callback=None, timeout=0, operations=OPCODE_ALLOPS, port=0, qos=0, ipAddress=None, groupingClass=0, groupingValue=0, groupingType=cx_Oracle.SUBSCR_GROUPING_TYPE_SUMMARY, name=None)

Return a new subscription object that receives notifications for events that take place in the database that match the given parameters.

The namespace parameter specifies the namespace the subscription uses. It can be one of cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE or cx_Oracle.SUBSCR_NAMESPACE_AQ.

The protocol parameter specifies the protocol to use when notifications are sent. Currently the only valid value is cx_Oracle.SUBSCR_PROTO_OCI.

The callback is expected to be a callable that accepts a single parameter. A message object is passed to this callback whenever a notification is received.

The timeout value specifies that the subscription expires after the given time in seconds. The default value of 0 indicates that the subscription never expires.

The operations parameter enables filtering of the messages that are sent (insert, update, delete). The default value will send notifications for all operations. This parameter is only used when the namespace is set to cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE.

The port parameter specifies the listening port for callback notifications from the database server. If not specified, an unused port will be selected by the Oracle Client libraries.

The qos parameter specifies quality of service options. It should be one or more of the following flags, OR’ed together: cx_Oracle.SUBSCR_QOS_RELIABLE, cx_Oracle.SUBSCR_QOS_DEREG_NFY, cx_Oracle.SUBSCR_QOS_ROWIDS, cx_Oracle.SUBSCR_QOS_QUERY, cx_Oracle.SUBSCR_QOS_BEST_EFFORT.

The ipAddress parameter specifies the IP address (IPv4 or IPv6) in standard string notation to bind for callback notifications from the database server. If not specified, the client IP address will be determined by the Oracle Client libraries.

The groupingClass parameter specifies what type of grouping of notifications should take place. Currently, if set, this value can only be set to the value cx_Oracle.SUBSCR_GROUPING_CLASS_TIME, which will group notifications by the number of seconds specified in the groupingValue parameter. The groupingType parameter should be one of the values cx_Oracle.SUBSCR_GROUPING_TYPE_SUMMARY (the default) or cx_Oracle.SUBSCR_GROUPING_TYPE_LAST.

The name parameter is used to identify the subscription and is specific to the selected namespace. If the namespace parameter is cx_Oracle.SUBSCR_NAMESPACE_DBCHANGE then the name is optional and can be any value. If the namespace parameter is cx_Oracle.SUBSCR_NAMESPACE_AQ, however, the name must be in the format ‘<QUEUE_NAME>’ for single consumer queues and ‘<QUEUE_NAME>:<CONSUMER_NAME>’ for multiple consumer queues, and identifies the queue that will be monitored for messages. The queue name may include the schema, if needed.

New in version 6.4: The parameters ipAddress, groupingClass, groupingValue, groupingType and name were added.

Note

This method is an extension to the DB API definition.

Note

The subscription can be deregistered in the database by calling the function unsubscribe(). If this method is not called and the connection that was used to create the subscription is explictly closed using the function close(), the subscription will not be deregistered in the database.

Connection.tag

This read-write attribute initially contains the actual tag of the session that was acquired from a pool by SessionPool.acquire(). If the connection was not acquired from a pool or no tagging parameters were specified (tag and matchanytag) when the connection was acquired from the pool, this value will be None. If the value is changed, it must be a string containing name=value pairs like “k1=v1;k2=v2”.

If this value is not None when the connection is released back to the pool it will be used to retag the session. This value can be overridden in the call to SessionPool.release().

Note

This attribute is an extension to the DB API definition.

New in version 7.1.

Connection.tnsentry

This read-only attribute returns the TNS entry of the database to which a connection has been established.

Note

This attribute is an extension to the DB API definition.

Connection.unsubscribe(subscr)

Unsubscribe from events in the database that were originally subscribed to using subscribe(). The connection used to unsubscribe should be the same one used to create the subscription, or should access the same database and be connected as the same user name.

New in version 6.4.

Connection.username

This read-only attribute returns the name of the user which established the connection to the database.

Note

This attribute is an extension to the DB API definition.

Connection.version

This read-only attribute returns the version of the database to which a connection has been established.

Note

This attribute is an extension to the DB API definition.

Note

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).