1. API: python-oracledb Module

1.1. Oracledb Methods

oracledb.Binary(value: Any) → bytes: Constructs an object holding a binary (long) string value.

oracledb.clientversion() → tuple

This function can only be called when python-oracledb is in Thick mode. Using it in Thin mode will throw an exception.

See Enabling python-oracledb Thick mode.

This method is an extension to the DB API definition.

Factory function which creates a connection to the database and returns it.

The dsn parameter (data source name) can be a string in the format user/password@connect_string or can simply be the connect string (in which case authentication credentials such as the username and password need to be specified separately). See the documentation on connection strings for more information.

The pool parameter is expected to be a pool object. This parameter was deprecated in python-oracledb 3.0.0. Use ConnectionPool.acquire() instead since the use of this parameter is the equivalent of calling this method.

The conn_class parameter is expected to be Connection or a subclass of Connection.

The params parameter is expected to be of type ConnectParams and contains connection parameters that will be used when establishing the connection. See the documentation on ConnectParams for more information. If this parameter is not specified, the additional keyword parameters will be used to create an instance of ConnectParams. If both the params parameter and additional keyword parameters are specified, the values in the keyword parameters have precedence. Note that if a dsn is also supplied, then in python-oracledb Thin mode, the values of the parameters specified (if any) within the dsn will override the values passed as additional keyword parameters, which themselves override the values set in the params parameter object.

The following parameters are all optional. A brief description of each parameter follows:

user: the name of the database user to connect to (default: None)
proxy_user: the name of the proxy user to connect to. If this value is not specified, it will be parsed out of user if user is in the form “user[proxy_user]” (default: None)
password: the password for the database user (default: None)
newpassword: a new password for the database user. The new password will take effect immediately upon a successful connection to the database (default: None)
wallet_password: the password to use to decrypt the wallet, if it is encrypted. This is not the database password. For Oracle Autonomous Database this is the password created when downloading the wallet. This value is only used in python-oracledb Thin mode. (default: None)
access_token: a string, or a 2-tuple, or a callable. If it is a string, it specifies an Entra ID OAuth2 token used for Open Authorization (OAuth 2.0) token based authentication. If it is a 2-tuple, it specifies the token and private key strings used for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) token based authentication. If it is a callable, it returns either a string or a 2-tuple used for OAuth 2.0 or OCI IAM token based authentication and is useful when the pool needs to expand and create new connections but the current authentication token has expired (default: None)
host: the hostname or IP address of the machine hosting the database or the database listener (default: None)
port: the port number on which the database listener is listening (default: 1521)
protocol: one of the strings “tcp” or “tcps” indicating whether to use unencrypted network traffic or encrypted network traffic (TLS) (default: “tcp”)
https_proxy: the hostname or IP address of a proxy host to use for tunneling secure connections (default: None)
https_proxy_port: the port on which to communicate with the proxy host (default: 0)
service_name: the service name of the database (default: None)
instance_name: the instance name of the database (default: None)
sid: the system identifier (SID) of the database. Note using a service_name instead is recommended (default: None)
server_type: the type of server connection that should be established. If specified, it should be one of strings “dedicated”, “shared” or “pooled” (default: None)
cclass: the connection class to use for Database Resident Connection Pooling (DRCP) (default: None)
purity: the connection purity to use for Database Resident Connection Pooling (DRCP) (default: oracledb.PURITY_DEFAULT)
expire_time: the number of minutes between the sending of keepalive probes. If this parameter is set to a value greater than zero it enables keepalive (default: 0)
retry_count: the number of times that initial connection establishment should be retried before the connection attempt is terminated (default: 0)
retry_delay: the number of seconds to wait before retrying to establish a connection (default: 1)
tcp_connect_timeout: a float indicating the maximum number of seconds to wait when establishing a connection to the database host (default: 20.0)
ssl_server_dn_match: a boolean indicating whether the server certificate distinguished name (DN) should be matched in addition to the regular certificate verification that is performed. Note that if the ssl_server_cert_dn parameter is not privided, host name matching is performed instead (default: True)
ssl_server_cert_dn: the distinguished name (DN) which should be matched with the server. This value is ignored if the ssl_server_dn_match parameter is not set to the value True. If specified this value is used for any verfication. Otherwise the hostname will be used (default: None)
wallet_location: the directory where the wallet can be found. In python-oracledb Thin mode this must be the directory containing the PEM- encoded wallet file ewallet.pem. In python-oracledb Thick mode this must be the directory containing the file cwallet.sso (default: None)
events: a boolean specifying whether events mode should be enabled. This value is only used in python-oracledb Thick mode and is needed for continuous query notification and high availability event notifications (default: False)
externalauth: a boolean indicating whether to use external authentication (default: False)
mode: the authorization mode to use. One of the constants oracledb.AUTH_MODE_DEFAULT, oracledb.AUTH_MODE_PRELIM, oracledb.AUTH_MODE_SYSASM, oracledb.AUTH_MODE_SYSBKP, oracledb.AUTH_MODE_SYSDBA, oracledb.AUTH_MODE_SYSDGD, oracledb.AUTH_MODE_SYSKMT, oracledb.AUTH_MODE_SYSOPER, or oracledb.AUTH_MODE_SYSRAC (default: oracledb.AUTH_MODE_DEFAULT)
disable_oob: a boolean indicating whether out-of-band breaks should be disabled. This value is only used in python-oracledb Thin mode. It has no effect on Windows which does not support this functionality (default: False)
stmtcachesize: the size of the statement cache (default: oracledb.defaults.stmtcachesize)
edition: edition to use for the connection. This parameter cannot be used simultaneously with the cclass parameter (default: None)
tag: identifies the type of connection that should be returned from a pool. This value is only used in python-oracledb Thick mode (default: None)
matchanytag: a boolean specifying whether any tag can be used when acquiring a connection from the pool. This value is only used in python- oracledb Thick mode (default: False)
config_dir: a directory in which the optional tnsnames.ora configuration file is located. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode, it is used if oracledb.defaults.thick_mode_dsn_passthrough is False. Otherwise in Thick mode use the config_dir parameter of oracledb.init_oracle_client() (default: oracledb.defaults.config_dir)
appcontext: application context used by the connection. It should be a list of 3-tuples (namespace, name, value) and each entry in the tuple should be a string (default: None)
shardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python- oracledb Thick mode (default: None)
supershardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python-oracledb Thick mode (default: None)
debug_jdwp: a string with the format “host=<host>;port=<port>” that specifies the host and port of the PL/SQL debugger. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode set the ORA_DEBUG_JDWP environment variable (default: None)
connection_id_prefix: an application specific prefix that is added to the connection identifier used for tracing (default: None)
ssl_context: an SSLContext object used for connecting to the database using TLS. This SSL context will be modified to include the private key or any certificates found in a separately supplied wallet. This parameter should only be specified if the default SSLContext object cannot be used (default: None)
sdu: the requested size of the Session Data Unit (SDU), in bytes. The value tunes internal buffers used for communication to the database. Bigger values can increase throughput for large queries or bulk data loads, but at the cost of higher memory use. The SDU size that will actually be used is negotiated down to the lower of this value and the database network SDU configuration value (default: 8192)
pool_boundary: one of the values “statement” or “transaction” indicating when pooled DRCP connections can be returned to the pool. This requires the use of DRCP with Oracle Database 23.4 or higher (default: None)
use_tcp_fast_open: a boolean indicating whether to use TCP fast open. This is an Oracle Autonomous Database Serverless (ADB-S) specific property for clients connecting from within OCI Cloud network. Please refer to the ADB-S documentation for more information (default: False)
ssl_version: one of the values ssl.TLSVersion.TLSv1_2 or ssl.TLSVersion.TLSv1_3 indicating which TLS version to use (default: None)
program: a string recorded by Oracle Database as the program from which the connection originates (default: oracledb.defaults.program)
machine: a string recorded by Oracle Database as the name of the machine from which the connection originates (default: oracledb.defaults.machine)
terminal: a string recorded by Oracle Database as the terminal identifier from which the connection originates (default: oracledb.defaults.terminal)
osuser: a string recorded by Oracle Database as the operating system user who originated the connection (default: oracledb.defaults.osuser)
driver_name: a string recorded by Oracle Database as the name of the driver which originated the connection (default: oracledb.defaults.driver_name)
use_sni: a boolean indicating whether to use the TLS SNI extension to bypass the second TLS neogiation that would otherwise be required (default: False)
thick_mode_dsn_passthrough: a boolean indicating whether to pass the connect string to the Oracle Client libraries unchanged without parsing by the driver. Setting this to False makes python-oracledb Thick and Thin mode applications behave similarly regarding connection string parameter handling and locating any optional tnsnames.ora configuration file (default: oracledb.defaults.thick_mode_dsn_passthrough)
extra_auth_params: a dictionary containing configuration parameters necessary for Oracle Database authentication using plugins, such as the Azure and OCI cloud-native authentication plugins (default: None)
pool_name: the name of the DRCP pool when using multi-pool DRCP with Oracle Database 23.4, or higher (default: None)
handle: an integer representing a pointer to a valid service context handle. This value is only used in python-oracledb Thick mode. It should be used with extreme caution (default: 0)

Changed in version 3.2.0: The pool_name parameter was added.

Changed in version 3.0.0: The pool_alias, instance_name, use_sni, thick_mode_dsn_passthrough, and extra_auth_params parameters were added. The pool parameter was deprecated: use ConnectionPool.acquire() instead.

Changed in version 2.5.0: The program, machine, terminal, osuser, and driver_name parameters were added. Support for edition and appcontext was added to python-oracledb Thin mode.

Changed in version 2.3.0: The default value of the retry_delay parameter was changed from 0 seconds to 1 second. The default value of the tcp_connect_timeout parameter was changed from 60.0 seconds to 20.0 seconds. The ssl_version parameter was added.

Changed in version 2.1.0: The pool_boundary and use_tcp_fast_open parameters were added.

Changed in version 2.0.0: The ssl_context and sdu parameters were added.

Changed in version 1.4.0: The connection_id_prefix parameter was added.

Factory function which creates a connection to the database and returns it.

The dsn parameter (data source name) can be a string in the format user/password@connect_string or can simply be the connect string (in which case authentication credentials such as the username and password need to be specified separately). See the documentation on connection strings for more information.

The pool parameter is expected to be a pool object. This parameter was deprecated in python-oracledb 3.0.0. Use ConnectionPool.acquire() instead since the use of this parameter is the equivalent of calling this method.

The conn_class parameter is expected to be AsyncConnection or a subclass of AsyncConnection.

The params parameter is expected to be of type ConnectParams and contains connection parameters that will be used when establishing the connection. See the documentation on ConnectParams for more information. If this parameter is not specified, the additional keyword parameters will be used to create an instance of ConnectParams. If both the params parameter and additional keyword parameters are specified, the values in the keyword parameters have precedence. Note that if a dsn is also supplied, then in python-oracledb Thin mode, the values of the parameters specified (if any) within the dsn will override the values passed as additional keyword parameters, which themselves override the values set in the params parameter object.

The following parameters are all optional. A brief description of each parameter follows:

user: the name of the database user to connect to (default: None)
proxy_user: the name of the proxy user to connect to. If this value is not specified, it will be parsed out of user if user is in the form “user[proxy_user]” (default: None)
password: the password for the database user (default: None)
newpassword: a new password for the database user. The new password will take effect immediately upon a successful connection to the database (default: None)
wallet_password: the password to use to decrypt the wallet, if it is encrypted. This is not the database password. For Oracle Autonomous Database this is the password created when downloading the wallet. This value is only used in python-oracledb Thin mode. (default: None)
access_token: a string, or a 2-tuple, or a callable. If it is a string, it specifies an Entra ID OAuth2 token used for Open Authorization (OAuth 2.0) token based authentication. If it is a 2-tuple, it specifies the token and private key strings used for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) token based authentication. If it is a callable, it returns either a string or a 2-tuple used for OAuth 2.0 or OCI IAM token based authentication and is useful when the pool needs to expand and create new connections but the current authentication token has expired (default: None)
host: the hostname or IP address of the machine hosting the database or the database listener (default: None)
port: the port number on which the database listener is listening (default: 1521)
protocol: one of the strings “tcp” or “tcps” indicating whether to use unencrypted network traffic or encrypted network traffic (TLS) (default: “tcp”)
https_proxy: the hostname or IP address of a proxy host to use for tunneling secure connections (default: None)
https_proxy_port: the port on which to communicate with the proxy host (default: 0)
service_name: the service name of the database (default: None)
instance_name: the instance name of the database (default: None)
sid: the system identifier (SID) of the database. Note using a service_name instead is recommended (default: None)
server_type: the type of server connection that should be established. If specified, it should be one of strings “dedicated”, “shared” or “pooled” (default: None)
cclass: the connection class to use for Database Resident Connection Pooling (DRCP) (default: None)
purity: the connection purity to use for Database Resident Connection Pooling (DRCP) (default: oracledb.PURITY_DEFAULT)
expire_time: the number of minutes between the sending of keepalive probes. If this parameter is set to a value greater than zero it enables keepalive (default: 0)
retry_count: the number of times that initial connection establishment should be retried before the connection attempt is terminated (default: 0)
retry_delay: the number of seconds to wait before retrying to establish a connection (default: 1)
tcp_connect_timeout: a float indicating the maximum number of seconds to wait when establishing a connection to the database host (default: 20.0)
ssl_server_dn_match: a boolean indicating whether the server certificate distinguished name (DN) should be matched in addition to the regular certificate verification that is performed. Note that if the ssl_server_cert_dn parameter is not privided, host name matching is performed instead (default: True)
ssl_server_cert_dn: the distinguished name (DN) which should be matched with the server. This value is ignored if the ssl_server_dn_match parameter is not set to the value True. If specified this value is used for any verfication. Otherwise the hostname will be used (default: None)
wallet_location: the directory where the wallet can be found. In python-oracledb Thin mode this must be the directory containing the PEM- encoded wallet file ewallet.pem. In python-oracledb Thick mode this must be the directory containing the file cwallet.sso (default: None)
events: a boolean specifying whether events mode should be enabled. This value is only used in python-oracledb Thick mode and is needed for continuous query notification and high availability event notifications (default: False)
externalauth: a boolean indicating whether to use external authentication (default: False)
mode: the authorization mode to use. One of the constants oracledb.AUTH_MODE_DEFAULT, oracledb.AUTH_MODE_PRELIM, oracledb.AUTH_MODE_SYSASM, oracledb.AUTH_MODE_SYSBKP, oracledb.AUTH_MODE_SYSDBA, oracledb.AUTH_MODE_SYSDGD, oracledb.AUTH_MODE_SYSKMT, oracledb.AUTH_MODE_SYSOPER, or oracledb.AUTH_MODE_SYSRAC (default: oracledb.AUTH_MODE_DEFAULT)
disable_oob: a boolean indicating whether out-of-band breaks should be disabled. This value is only used in python-oracledb Thin mode. It has no effect on Windows which does not support this functionality (default: False)
stmtcachesize: the size of the statement cache (default: oracledb.defaults.stmtcachesize)
edition: edition to use for the connection. This parameter cannot be used simultaneously with the cclass parameter (default: None)
tag: identifies the type of connection that should be returned from a pool. This value is only used in python-oracledb Thick mode (default: None)
matchanytag: a boolean specifying whether any tag can be used when acquiring a connection from the pool. This value is only used in python- oracledb Thick mode (default: False)
config_dir: a directory in which the optional tnsnames.ora configuration file is located. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode, it is used if oracledb.defaults.thick_mode_dsn_passthrough is False. Otherwise in Thick mode use the config_dir parameter of oracledb.init_oracle_client() (default: oracledb.defaults.config_dir)
appcontext: application context used by the connection. It should be a list of 3-tuples (namespace, name, value) and each entry in the tuple should be a string (default: None)
shardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python- oracledb Thick mode (default: None)
supershardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python-oracledb Thick mode (default: None)
debug_jdwp: a string with the format “host=<host>;port=<port>” that specifies the host and port of the PL/SQL debugger. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode set the ORA_DEBUG_JDWP environment variable (default: None)
connection_id_prefix: an application specific prefix that is added to the connection identifier used for tracing (default: None)
ssl_context: an SSLContext object used for connecting to the database using TLS. This SSL context will be modified to include the private key or any certificates found in a separately supplied wallet. This parameter should only be specified if the default SSLContext object cannot be used (default: None)
sdu: the requested size of the Session Data Unit (SDU), in bytes. The value tunes internal buffers used for communication to the database. Bigger values can increase throughput for large queries or bulk data loads, but at the cost of higher memory use. The SDU size that will actually be used is negotiated down to the lower of this value and the database network SDU configuration value (default: 8192)
pool_boundary: one of the values “statement” or “transaction” indicating when pooled DRCP connections can be returned to the pool. This requires the use of DRCP with Oracle Database 23.4 or higher (default: None)
use_tcp_fast_open: a boolean indicating whether to use TCP fast open. This is an Oracle Autonomous Database Serverless (ADB-S) specific property for clients connecting from within OCI Cloud network. Please refer to the ADB-S documentation for more information (default: False)
ssl_version: one of the values ssl.TLSVersion.TLSv1_2 or ssl.TLSVersion.TLSv1_3 indicating which TLS version to use (default: None)
program: a string recorded by Oracle Database as the program from which the connection originates (default: oracledb.defaults.program)
machine: a string recorded by Oracle Database as the name of the machine from which the connection originates (default: oracledb.defaults.machine)
terminal: a string recorded by Oracle Database as the terminal identifier from which the connection originates (default: oracledb.defaults.terminal)
osuser: a string recorded by Oracle Database as the operating system user who originated the connection (default: oracledb.defaults.osuser)
driver_name: a string recorded by Oracle Database as the name of the driver which originated the connection (default: oracledb.defaults.driver_name)
use_sni: a boolean indicating whether to use the TLS SNI extension to bypass the second TLS neogiation that would otherwise be required (default: False)
thick_mode_dsn_passthrough: a boolean indicating whether to pass the connect string to the Oracle Client libraries unchanged without parsing by the driver. Setting this to False makes python-oracledb Thick and Thin mode applications behave similarly regarding connection string parameter handling and locating any optional tnsnames.ora configuration file (default: oracledb.defaults.thick_mode_dsn_passthrough)
extra_auth_params: a dictionary containing configuration parameters necessary for Oracle Database authentication using plugins, such as the Azure and OCI cloud-native authentication plugins (default: None)
pool_name: the name of the DRCP pool when using multi-pool DRCP with Oracle Database 23.4, or higher (default: None)
handle: an integer representing a pointer to a valid service context handle. This value is only used in python-oracledb Thick mode. It should be used with extreme caution (default: 0)

Changed in version 3.2.0: The pool_name parameter was added.

Changed in version 3.0.0: The pool_alias, instance_name, use_sni, thick_mode_dsn_passthrough, and extra_auth_params parameters were added. The pool parameter was deprecated: use AsyncConnectionPool.acquire() instead.

Changed in version 2.5.0: The program, machine, terminal, osuser, and driver_name parameters were added. Support for edition and appcontext was added.

Changed in version 2.3.0: The default value of the retry_delay parameter was changed from 0 seconds to 1 second. The default value of the tcp_connect_timeout parameter was changed from 60.0 seconds to 20.0 seconds. The ssl_version parameter was added.

Changed in version 2.1.0: The pool_boundary and use_tcp_fast_open parameters were added.

Changed in version 2.0.0: The ssl_context and sdu parameters were added.

Changed in version 1.4.0: The connection_id_prefix parameter was added.

oracledb.create_pipeline() → Pipeline: Creates a pipeline object which can be used to process a set of operations against a database.

Added in version 2.4.0.

Creates a connection pool with the supplied parameters and returns it.

The dsn parameter (data source name) can be a string in the format user/password@connect_string or can simply be the connect string (in which case authentication credentials such as the username and password need to be specified separately). See the documentation on connection strings for more information.

The pool_class parameter is expected to be ConnectionPool or a subclass of ConnectionPool.

The pool_alias parameter is expected to be a string representing the name used to store and reference the pool in the python-oracledb connection pool cache. If this parameter is not specified, then the pool will not be added to the cache. The value of this parameter can be used with the oracledb.get_pool() and oracledb.connect() methods to access the pool.

The params parameter is expected to be of type PoolParams and contains parameters that are used to create the pool. See the documentation on PoolParams for more information. If this parameter is not specified, the additional keyword parameters will be used to create an instance of PoolParams. If both the params parameter and additional keyword parameters are specified, the values in the keyword parameters have precedence. Note that if a dsn is also supplied, then in python-oracledb Thin mode, the values of the parameters specified (if any) within the dsn will override the values passed as additional keyword parameters, which themselves override the values set in the params parameter object.

The following parameters are all optional. A brief description of each parameter follows:

min: the minimum number of connections the pool should contain (default: 1)
max: the maximum number of connections the pool should contain (default: 2)
increment: the number of connections that should be added to the pool whenever the pool needs to grow (default: 1)
connectiontype: the class of the connection that should be returned during calls to pool.acquire(). It must be oracledb.Connection or a subclass of oracledb.Connection (default: None)
getmode: how pool.acquire() will behave. One of the constants oracledb.POOL_GETMODE_WAIT, oracledb.POOL_GETMODE_NOWAIT, oracledb.POOL_GETMODE_FORCEGET, or oracledb.POOL_GETMODE_TIMEDWAIT (default: oracledb.POOL_GETMODE_WAIT)
homogeneous: a boolean indicating whether the connections in the pool are homogeneous (same user) or heterogeneous (multiple users) (default: True)
timeout: the length of time (in seconds) that a connection may remain idle in the pool before it is terminated. If it is 0 then connections are never terminated (default: 0)
wait_timeout: the length of time (in milliseconds) that a caller should wait when acquiring a connection from the pool with getmode set to oracledb.POOL_GETMODE_TIMEDWAIT (default: 0)
max_lifetime_session: the length of time (in seconds) that connections can remain in the pool. If it is 0 then connections may remain in the pool indefinitely (default: 0)
session_callback: a callable that is invoked when a connection is returned from the pool for the first time, or when the connection tag differs from the one requested (default: None)
max_sessions_per_shard: the maximum number of connections that may be associated with a particular shard (default: 0)
soda_metadata_cache: a boolean indicating whether or not the SODA metadata cache should be enabled (default: False)
ping_interval: the length of time (in seconds) after which an unused connection in the pool will be a candidate for pinging when pool.acquire() is called. If the ping to the database indicates the connection is not alive a replacement connection will be returned by pool.acquire(). If ping_interval is a negative value the ping functionality will be disabled (default: 60)
ping_timeout: the maximum length of time (in milliseconds) to wait for a connection in the pool to respond to an internal ping to the database before being discarded and replaced during a call to acquire() (default: 5000)
user: the name of the database user to connect to (default: None)
proxy_user: the name of the proxy user to connect to. If this value is not specified, it will be parsed out of user if user is in the form “user[proxy_user]” (default: None)
password: the password for the database user (default: None)
newpassword: a new password for the database user. The new password will take effect immediately upon a successful connection to the database (default: None)
wallet_password: the password to use to decrypt the wallet, if it is encrypted. This is not the database password. For Oracle Autonomous Database this is the password created when downloading the wallet. This value is only used in python-oracledb Thin mode. (default: None)
access_token: a string, or a 2-tuple, or a callable. If it is a string, it specifies an Entra ID OAuth2 token used for Open Authorization (OAuth 2.0) token based authentication. If it is a 2-tuple, it specifies the token and private key strings used for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) token based authentication. If it is a callable, it returns either a string or a 2-tuple used for OAuth 2.0 or OCI IAM token based authentication and is useful when the pool needs to expand and create new connections but the current authentication token has expired (default: None)
host: the hostname or IP address of the machine hosting the database or the database listener (default: None)
port: the port number on which the database listener is listening (default: 1521)
protocol: one of the strings “tcp” or “tcps” indicating whether to use unencrypted network traffic or encrypted network traffic (TLS) (default: “tcp”)
https_proxy: the hostname or IP address of a proxy host to use for tunneling secure connections (default: None)
https_proxy_port: the port on which to communicate with the proxy host (default: 0)
service_name: the service name of the database (default: None)
instance_name: the instance name of the database (default: None)
sid: the system identifier (SID) of the database. Note using a service_name instead is recommended (default: None)
server_type: the type of server connection that should be established. If specified, it should be one of strings “dedicated”, “shared” or “pooled” (default: None)
cclass: the connection class to use for Database Resident Connection Pooling (DRCP) (default: None)
purity: the connection purity to use for Database Resident Connection Pooling (DRCP) (default: oracledb.PURITY_DEFAULT)
expire_time: the number of minutes between the sending of keepalive probes. If this parameter is set to a value greater than zero it enables keepalive (default: 0)
retry_count: the number of times that initial connection establishment should be retried before the connection attempt is terminated (default: 0)
retry_delay: the number of seconds to wait before retrying to establish a connection (default: 1)
tcp_connect_timeout: a float indicating the maximum number of seconds to wait when establishing a connection to the database host (default: 20.0)
ssl_server_dn_match: a boolean indicating whether the server certificate distinguished name (DN) should be matched in addition to the regular certificate verification that is performed. Note that if the ssl_server_cert_dn parameter is not privided, host name matching is performed instead (default: True)
ssl_server_cert_dn: the distinguished name (DN) which should be matched with the server. This value is ignored if the ssl_server_dn_match parameter is not set to the value True. If specified this value is used for any verfication. Otherwise the hostname will be used (default: None)
wallet_location: the directory where the wallet can be found. In python-oracledb Thin mode this must be the directory containing the PEM- encoded wallet file ewallet.pem. In python-oracledb Thick mode this must be the directory containing the file cwallet.sso (default: None)
events: a boolean specifying whether events mode should be enabled. This value is only used in python-oracledb Thick mode and is needed for continuous query notification and high availability event notifications (default: False)
externalauth: a boolean indicating whether to use external authentication (default: False)
mode: the authorization mode to use. One of the constants oracledb.AUTH_MODE_DEFAULT, oracledb.AUTH_MODE_PRELIM, oracledb.AUTH_MODE_SYSASM, oracledb.AUTH_MODE_SYSBKP, oracledb.AUTH_MODE_SYSDBA, oracledb.AUTH_MODE_SYSDGD, oracledb.AUTH_MODE_SYSKMT, oracledb.AUTH_MODE_SYSOPER, or oracledb.AUTH_MODE_SYSRAC (default: oracledb.AUTH_MODE_DEFAULT)
disable_oob: a boolean indicating whether out-of-band breaks should be disabled. This value is only used in python-oracledb Thin mode. It has no effect on Windows which does not support this functionality (default: False)
stmtcachesize: the size of the statement cache (default: oracledb.defaults.stmtcachesize)
edition: edition to use for the connection. This parameter cannot be used simultaneously with the cclass parameter (default: None)
tag: identifies the type of connection that should be returned from a pool. This value is only used in python-oracledb Thick mode (default: None)
matchanytag: a boolean specifying whether any tag can be used when acquiring a connection from the pool. This value is only used in python- oracledb Thick mode (default: False)
config_dir: a directory in which the optional tnsnames.ora configuration file is located. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode, it is used if oracledb.defaults.thick_mode_dsn_passthrough is False. Otherwise in Thick mode use the config_dir parameter of oracledb.init_oracle_client() (default: oracledb.defaults.config_dir)
appcontext: application context used by the connection. It should be a list of 3-tuples (namespace, name, value) and each entry in the tuple should be a string (default: None)
shardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python- oracledb Thick mode (default: None)
supershardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python-oracledb Thick mode (default: None)
debug_jdwp: a string with the format “host=<host>;port=<port>” that specifies the host and port of the PL/SQL debugger. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode set the ORA_DEBUG_JDWP environment variable (default: None)
connection_id_prefix: an application specific prefix that is added to the connection identifier used for tracing (default: None)
ssl_context: an SSLContext object used for connecting to the database using TLS. This SSL context will be modified to include the private key or any certificates found in a separately supplied wallet. This parameter should only be specified if the default SSLContext object cannot be used (default: None)
sdu: the requested size of the Session Data Unit (SDU), in bytes. The value tunes internal buffers used for communication to the database. Bigger values can increase throughput for large queries or bulk data loads, but at the cost of higher memory use. The SDU size that will actually be used is negotiated down to the lower of this value and the database network SDU configuration value (default: 8192)
pool_boundary: one of the values “statement” or “transaction” indicating when pooled DRCP connections can be returned to the pool. This requires the use of DRCP with Oracle Database 23.4 or higher (default: None)
use_tcp_fast_open: a boolean indicating whether to use TCP fast open. This is an Oracle Autonomous Database Serverless (ADB-S) specific property for clients connecting from within OCI Cloud network. Please refer to the ADB-S documentation for more information (default: False)
ssl_version: one of the values ssl.TLSVersion.TLSv1_2 or ssl.TLSVersion.TLSv1_3 indicating which TLS version to use (default: None)
program: a string recorded by Oracle Database as the program from which the connection originates (default: oracledb.defaults.program)
machine: a string recorded by Oracle Database as the name of the machine from which the connection originates (default: oracledb.defaults.machine)
terminal: a string recorded by Oracle Database as the terminal identifier from which the connection originates (default: oracledb.defaults.terminal)
osuser: a string recorded by Oracle Database as the operating system user who originated the connection (default: oracledb.defaults.osuser)
driver_name: a string recorded by Oracle Database as the name of the driver which originated the connection (default: oracledb.defaults.driver_name)
use_sni: a boolean indicating whether to use the TLS SNI extension to bypass the second TLS neogiation that would otherwise be required (default: False)
thick_mode_dsn_passthrough: a boolean indicating whether to pass the connect string to the Oracle Client libraries unchanged without parsing by the driver. Setting this to False makes python-oracledb Thick and Thin mode applications behave similarly regarding connection string parameter handling and locating any optional tnsnames.ora configuration file (default: oracledb.defaults.thick_mode_dsn_passthrough)
extra_auth_params: a dictionary containing configuration parameters necessary for Oracle Database authentication using plugins, such as the Azure and OCI cloud-native authentication plugins (default: None)
pool_name: the name of the DRCP pool when using multi-pool DRCP with Oracle Database 23.4, or higher (default: None)
handle: an integer representing a pointer to a valid service context handle. This value is only used in python-oracledb Thick mode. It should be used with extreme caution (default: 0)

Changed in version 3.2.0: The pool_name parameter was added.

Changed in version 3.0.0: The pool_alias, instance_name, use_sni, thick_mode_dsn_passthrough, and extra_auth_params parameters were added.

Changed in version 2.5.0: The program, machine, terminal, osuser, and driver_name parameters were added. Support for edition and appcontext was added to python-oracledb Thin mode.

Changed in version 2.3.0: The default value of the retry_delay parameter was changed from 0 seconds to 1 second. The default value of the tcp_connect_timeout parameter was changed from 60.0 seconds to 20.0 seconds. The ping_timeout and ssl_version parameters were added.

Changed in version 2.1.0: The pool_boundary and use_tcp_fast_open parameters were added.

Changed in version 2.0.0: The ssl_context and sdu parameters were added.

Changed in version 1.4.0: The connection_id_prefix parameter was added.

Creates a connection pool with the supplied parameters and returns it.

The dsn parameter (data source name) can be a string in the format user/password@connect_string or can simply be the connect string (in which case authentication credentials such as the username and password need to be specified separately). See the documentation on connection strings for more information.

The pool_class parameter is expected to be AsyncConnectionPool or a subclass of AsyncConnectionPool.

The pool_alias parameter is expected to be a string representing the name used to store and reference the pool in the python-oracledb connection pool cache. If this parameter is not specified, then the pool will not be added to the cache. The value of this parameter can be used with the oracledb.get_pool() and :meth:o`racledb.connect_async()` methods to access the pool.

The params parameter is expected to be of type PoolParams and contains parameters that are used to create the pool. See the documentation on PoolParams for more information. If this parameter is not specified, the additional keyword parameters will be used to create an instance of PoolParams. If both the params parameter and additional keyword parameters are specified, the values in the keyword parameters have precedence. Note that if a dsn is also supplied, then in python-oracledb Thin mode, the values of the parameters specified (if any) within the dsn will override the values passed as additional keyword parameters, which themselves override the values set in the params parameter object.

The following parameters are all optional. A brief description of each parameter follows:

min: the minimum number of connections the pool should contain (default: 1)
max: the maximum number of connections the pool should contain (default: 2)
increment: the number of connections that should be added to the pool whenever the pool needs to grow (default: 1)
connectiontype: the class of the connection that should be returned during calls to pool.acquire(). It must be oracledb.AsyncConnection or a subclass of oracledb.AsyncConnection (default: None)
getmode: how pool.acquire() will behave. One of the constants oracledb.POOL_GETMODE_WAIT, oracledb.POOL_GETMODE_NOWAIT, oracledb.POOL_GETMODE_FORCEGET, or oracledb.POOL_GETMODE_TIMEDWAIT (default: oracledb.POOL_GETMODE_WAIT)
homogeneous: a boolean indicating whether the connections in the pool are homogeneous (same user) or heterogeneous (multiple users) (default: True)
timeout: the length of time (in seconds) that a connection may remain idle in the pool before it is terminated. If it is 0 then connections are never terminated (default: 0)
wait_timeout: the length of time (in milliseconds) that a caller should wait when acquiring a connection from the pool with getmode set to oracledb.POOL_GETMODE_TIMEDWAIT (default: 0)
max_lifetime_session: the length of time (in seconds) that connections can remain in the pool. If it is 0 then connections may remain in the pool indefinitely (default: 0)
session_callback: a callable that is invoked when a connection is returned from the pool for the first time, or when the connection tag differs from the one requested (default: None)
max_sessions_per_shard: the maximum number of connections that may be associated with a particular shard (default: 0)
soda_metadata_cache: a boolean indicating whether or not the SODA metadata cache should be enabled (default: False)
ping_interval: the length of time (in seconds) after which an unused connection in the pool will be a candidate for pinging when pool.acquire() is called. If the ping to the database indicates the connection is not alive a replacement connection will be returned by pool.acquire(). If ping_interval is a negative value the ping functionality will be disabled (default: 60)
ping_timeout: the maximum length of time (in milliseconds) to wait for a connection in the pool to respond to an internal ping to the database before being discarded and replaced during a call to acquire() (default: 5000)
user: the name of the database user to connect to (default: None)
proxy_user: the name of the proxy user to connect to. If this value is not specified, it will be parsed out of user if user is in the form “user[proxy_user]” (default: None)
password: the password for the database user (default: None)
newpassword: a new password for the database user. The new password will take effect immediately upon a successful connection to the database (default: None)
wallet_password: the password to use to decrypt the wallet, if it is encrypted. This is not the database password. For Oracle Autonomous Database this is the password created when downloading the wallet. This value is only used in python-oracledb Thin mode. (default: None)
access_token: a string, or a 2-tuple, or a callable. If it is a string, it specifies an Entra ID OAuth2 token used for Open Authorization (OAuth 2.0) token based authentication. If it is a 2-tuple, it specifies the token and private key strings used for Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) token based authentication. If it is a callable, it returns either a string or a 2-tuple used for OAuth 2.0 or OCI IAM token based authentication and is useful when the pool needs to expand and create new connections but the current authentication token has expired (default: None)
host: the hostname or IP address of the machine hosting the database or the database listener (default: None)
port: the port number on which the database listener is listening (default: 1521)
protocol: one of the strings “tcp” or “tcps” indicating whether to use unencrypted network traffic or encrypted network traffic (TLS) (default: “tcp”)
https_proxy: the hostname or IP address of a proxy host to use for tunneling secure connections (default: None)
https_proxy_port: the port on which to communicate with the proxy host (default: 0)
service_name: the service name of the database (default: None)
instance_name: the instance name of the database (default: None)
sid: the system identifier (SID) of the database. Note using a service_name instead is recommended (default: None)
server_type: the type of server connection that should be established. If specified, it should be one of strings “dedicated”, “shared” or “pooled” (default: None)
cclass: the connection class to use for Database Resident Connection Pooling (DRCP) (default: None)
purity: the connection purity to use for Database Resident Connection Pooling (DRCP) (default: oracledb.PURITY_DEFAULT)
expire_time: the number of minutes between the sending of keepalive probes. If this parameter is set to a value greater than zero it enables keepalive (default: 0)
retry_count: the number of times that initial connection establishment should be retried before the connection attempt is terminated (default: 0)
retry_delay: the number of seconds to wait before retrying to establish a connection (default: 1)
tcp_connect_timeout: a float indicating the maximum number of seconds to wait when establishing a connection to the database host (default: 20.0)
ssl_server_dn_match: a boolean indicating whether the server certificate distinguished name (DN) should be matched in addition to the regular certificate verification that is performed. Note that if the ssl_server_cert_dn parameter is not privided, host name matching is performed instead (default: True)
ssl_server_cert_dn: the distinguished name (DN) which should be matched with the server. This value is ignored if the ssl_server_dn_match parameter is not set to the value True. If specified this value is used for any verfication. Otherwise the hostname will be used (default: None)
wallet_location: the directory where the wallet can be found. In python-oracledb Thin mode this must be the directory containing the PEM- encoded wallet file ewallet.pem. In python-oracledb Thick mode this must be the directory containing the file cwallet.sso (default: None)
events: a boolean specifying whether events mode should be enabled. This value is only used in python-oracledb Thick mode and is needed for continuous query notification and high availability event notifications (default: False)
externalauth: a boolean indicating whether to use external authentication (default: False)
mode: the authorization mode to use. One of the constants oracledb.AUTH_MODE_DEFAULT, oracledb.AUTH_MODE_PRELIM, oracledb.AUTH_MODE_SYSASM, oracledb.AUTH_MODE_SYSBKP, oracledb.AUTH_MODE_SYSDBA, oracledb.AUTH_MODE_SYSDGD, oracledb.AUTH_MODE_SYSKMT, oracledb.AUTH_MODE_SYSOPER, or oracledb.AUTH_MODE_SYSRAC (default: oracledb.AUTH_MODE_DEFAULT)
disable_oob: a boolean indicating whether out-of-band breaks should be disabled. This value is only used in python-oracledb Thin mode. It has no effect on Windows which does not support this functionality (default: False)
stmtcachesize: the size of the statement cache (default: oracledb.defaults.stmtcachesize)
edition: edition to use for the connection. This parameter cannot be used simultaneously with the cclass parameter (default: None)
tag: identifies the type of connection that should be returned from a pool. This value is only used in python-oracledb Thick mode (default: None)
matchanytag: a boolean specifying whether any tag can be used when acquiring a connection from the pool. This value is only used in python- oracledb Thick mode (default: False)
config_dir: a directory in which the optional tnsnames.ora configuration file is located. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode, it is used if oracledb.defaults.thick_mode_dsn_passthrough is False. Otherwise in Thick mode use the config_dir parameter of oracledb.init_oracle_client() (default: oracledb.defaults.config_dir)
appcontext: application context used by the connection. It should be a list of 3-tuples (namespace, name, value) and each entry in the tuple should be a string (default: None)
shardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python- oracledb Thick mode (default: None)
supershardingkey: a list of strings, numbers, bytes or dates that identify the database shard to connect to. This value is only used in python-oracledb Thick mode (default: None)
debug_jdwp: a string with the format “host=<host>;port=<port>” that specifies the host and port of the PL/SQL debugger. This value is only used in python-oracledb Thin mode. For python-oracledb Thick mode set the ORA_DEBUG_JDWP environment variable (default: None)
connection_id_prefix: an application specific prefix that is added to the connection identifier used for tracing (default: None)
ssl_context: an SSLContext object used for connecting to the database using TLS. This SSL context will be modified to include the private key or any certificates found in a separately supplied wallet. This parameter should only be specified if the default SSLContext object cannot be used (default: None)
sdu: the requested size of the Session Data Unit (SDU), in bytes. The value tunes internal buffers used for communication to the database. Bigger values can increase throughput for large queries or bulk data loads, but at the cost of higher memory use. The SDU size that will actually be used is negotiated down to the lower of this value and the database network SDU configuration value (default: 8192)
pool_boundary: one of the values “statement” or “transaction” indicating when pooled DRCP connections can be returned to the pool. This requires the use of DRCP with Oracle Database 23.4 or higher (default: None)
use_tcp_fast_open: a boolean indicating whether to use TCP fast open. This is an Oracle Autonomous Database Serverless (ADB-S) specific property for clients connecting from within OCI Cloud network. Please refer to the ADB-S documentation for more information (default: False)
ssl_version: one of the values ssl.TLSVersion.TLSv1_2 or ssl.TLSVersion.TLSv1_3 indicating which TLS version to use (default: None)
program: a string recorded by Oracle Database as the program from which the connection originates (default: oracledb.defaults.program)
machine: a string recorded by Oracle Database as the name of the machine from which the connection originates (default: oracledb.defaults.machine)
terminal: a string recorded by Oracle Database as the terminal identifier from which the connection originates (default: oracledb.defaults.terminal)
osuser: a string recorded by Oracle Database as the operating system user who originated the connection (default: oracledb.defaults.osuser)
driver_name: a string recorded by Oracle Database as the name of the driver which originated the connection (default: oracledb.defaults.driver_name)
use_sni: a boolean indicating whether to use the TLS SNI extension to bypass the second TLS neogiation that would otherwise be required (default: False)
thick_mode_dsn_passthrough: a boolean indicating whether to pass the connect string to the Oracle Client libraries unchanged without parsing by the driver. Setting this to False makes python-oracledb Thick and Thin mode applications behave similarly regarding connection string parameter handling and locating any optional tnsnames.ora configuration file (default: oracledb.defaults.thick_mode_dsn_passthrough)
extra_auth_params: a dictionary containing configuration parameters necessary for Oracle Database authentication using plugins, such as the Azure and OCI cloud-native authentication plugins (default: None)
pool_name: the name of the DRCP pool when using multi-pool DRCP with Oracle Database 23.4, or higher (default: None)
handle: an integer representing a pointer to a valid service context handle. This value is only used in python-oracledb Thick mode. It should be used with extreme caution (default: 0)

Changed in version 3.2.0: The pool_name parameter was added.

Changed in version 3.0.0: The pool_alias, instance_name, use_sni, thick_mode_dsn_passthrough, and extra_auth_params parameters were added.

Changed in version 2.5.0: The program, machine, terminal, osuser, and driver_name parameters were added. Support for edition and appcontext was added.

Changed in version 2.3.0: The default value of the retry_delay parameter was changed from 0 seconds to 1 second. The default value of the tcp_connect_timeout parameter was changed from 60.0 seconds to 20.0 seconds. The ping_timeout and ssl_version parameters were added.

Changed in version 2.1.0: The pool_boundary and use_tcp_fast_open parameters were added.

Changed in version 2.0.0: The ssl_context and sdu parameters were added.

Changed in version 1.4.0: The connection_id_prefix parameter was added.

oracledb.Date(year: int, month: int, day: int) → date: Constructs an object holding a date value.

oracledb.DateFromTicks(ticks: float) → date: Constructor mandated by the database API for creating a date value given the number of seconds since the epoch (January 1, 1970). This is equivalent to using datetime.date.fromtimestamp() and that should be used instead.

oracledb.enable_thin_mode()

Makes python-oracledb be in Thin mode. After this method is called, Thick mode cannot be enabled. If python-oracledb is already in Thick mode, then calling enable_thin_mode() will fail. If Thin mode connections have already been opened, or a connection pool created in Thin mode, then calling enable_thin_mode() is a no-op.

Since python-oracledb defaults to Thin mode, almost all applications do not need to call this method. However, because it bypasses python-oracledb’s internal mode-determination heuristic, it may be useful for applications with multiple threads that concurrently create standalone connections when the application starts.

See Explicitly Enabling python-oracledb Thin Mode for more information.

Added in version 2.5.0.

oracledb.from_arrow(obj: Any) → DataFrame | ArrowArray

This method converts a data frame to a DataFrame or ArrowArray instance.

If obj supports the Arrow PyCapsule interface __arrow_c_stream__ method, then from_arrow() returns the instance as a DataFrame. If obj does not support that method, but does support __arrow_c_array__, then an ArrowArray is returned.

Added in version 3.3.0.

oracledb.get_pool(pool_alias: str) → ConnectionPool | AsyncConnectionPool | None

Returns a ConnectionPool object from the python-oracledb pool cache. The pool must have been previously created by passing the same pool_alias value to oracledb.create_pool() or oracledb.create_pool_async().

If a pool with the given name does not exist, None is returned.

See Using the Connection Pool Cache for more information.

Added in version 3.0.0.

Enables python-oracledb Thick mode by initializing the Oracle Client library, see Enabling python-oracledb Thick mode. If a standalone connection or pool has already been created in Thin mode, init_oracle_client() will raise an exception and python-oracledb will remain in Thin mode.

If a standalone connection or pool has not already been created in Thin mode, but init_oracle_client() raises an exception, python-oracledb will remain in Thin mode but further calls to init_oracle_client() can be made, if desired.

The init_oracle_client() method can be called multiple times in each Python process as long as the arguments are the same each time.

The lib_dir parameter is a string or a bytes object that specifies the directory containing Oracle Client libraries. If the lib_dir parameter is set, then the specified directory is the only one searched for the Oracle Client libraries; otherwise, the operating system library search path is used to locate the Oracle Client library. If you are using Python 3.11 and later, then the value specified in this parameter is encoded using locale.getencoding(). For all other Python versions, the encoding “utf-8” is used. If a bytes object is specified in this parameter, then this value will be used as is without any encoding.

The config_dir parameter is a string or a bytes object that specifies the directory in which the Optional Oracle Net Configuration and Optional Oracle Client Configuration files reside. If the config_dir parameter is set, then the specified directory is used to find Oracle Client library configuration files. This is equivalent to setting the environment variable TNS_ADMIN and overrides any value already set in TNS_ADMIN. If this parameter is not set, the Oracle standard way of locating Oracle Client library configuration files is used. If you are using Python 3.11 and later, then the value specified in this parameter is encoded using locale.getencoding(). For all other Python versions, the encoding “utf-8” is used. If a bytes object is specified in this parameter, then this value will be used as is without any encoding.

The error_url parameter is a string that specifies the URL which is included in the python-oracledb exception message if the Oracle Client libraries cannot be loaded. If the error_url parameter is set, then the specified value is included in the message of the exception raised when the Oracle Client library cannot be loaded; otherwise, the Installing python-oracledb URL is included. This parameter lets your application display custom installation instructions.

The driver_name parameter is a string that specifies the driver name value. If the driver_name parameter is set, then the specified value can be found in database views that give information about connections. For example, it is in the CLIENT_DRIVER column of the V$SESSION_CONNECT_INFO view. From Oracle Database 12.2, the name displayed can be 30 characters. The standard is to set this value to "<name> : version>", where <name> is the name of the driver and <version> is its version. There should be a single space character before and after the colon. If this parameter is not set, then the value specified in oracledb.defaults.driver_name is used. If the value of this attribute is None, then the default value in python-oracledb Thick mode is like “python-oracledb thk : <version>”. See Other python-oracledb Thick Mode Initialization.

At successful completion of a call to oracledb.init_oracle_client(), the attribute oracledb.defaults.config_dir will be set as determined below (first one wins):

the value of the oracledb.init_oracle_client() parameter config_dir, if one was passed.
the value of oracledb.defaults.config_dir if it has one. i.e. oracledb.defaults.config_dir remains unchanged after oracledb.init_oracle_client() completes.
the value of the environment variable $TNS_ADMIN, if it is set.
the value of $ORACLE_HOME/network/admin if the environment variable $ORACLE_HOME is set.
the directory of the loaded Oracle Client library, appended with network/admin. Note this directory is not determinable on AIX.
otherwise the value None is used. (Leaving oracledb.defaults.config_dir unchanged).

This method is an extension to the DB API definition.

Changed in version 3.0.0: At completion of the method, the value of oracledb.defaults.config_dir may get changed by python-oracledb.

Changed in version 2.5.0: The values supplied to the lib_dir and config_dir parameters are encoded using the encoding returned by locale.getencoding() for Python 3.11 and higher; for all other versions, the encoding “utf-8” is used. These values may also be supplied as a bytes object, in which case they will be used as is.

oracledb.is_thin_mode() → bool

Returns a boolean indicating if python-oracledb is in Thin mode.

Immediately after python-oracledb is imported, this function will return True indicating that python-oracledb defaults to Thin mode. If a call to oracledb.init_oracle_client() returns successfully, then a subsequent call to is_thin_mode() will return False indicating that Thick mode is enabled. Once the first standalone connection or connection pool is created, or a successful call to oracledb.init_oracle_client() is made, or oracledb.enable_thin_mode() is called, then python-oracledb’s mode is fixed and the value returned by is_thin_mode() will never change for the lifetime of the process.

The attribute Connection.thin can be used to check a connection’s mode. The attribute ConnectionPool.thin can be used to check a pool’s mode.

See Finding the python-oracledb Mode.

This method is an extension to the DB API definition.

Added in version 1.1.0.

Returns a string suitable for use as the dsn parameter for connect(). This string is identical to the strings that are defined by the Oracle names server or defined in the tnsnames.ora file.

Deprecated since python-oracledb 1.0.

Use the oracledb.ConnectParams() method instead.

This method is an extension to the DB API definition.

oracledb.register_params_hook(hook_function: Callable) → None

Registers a user parameter hook function that will be called internally by python-oracledb prior to connection or pool creation. The hook function accepts a copy of the parameters that will be used to create the pool or standalone connection and may modify them. For example, the cloud native authentication plugins modify the “access_token” parameter with a function that will acquire the token using information found in the “extra_auth_parms” parameter.

Multiple hooks may be registered. They will be invoked in order of registration.

To unregister a user function, use oracledb.unregister_params_hook().

See Using Parameter Hook Functions.

This method is an extension to the DB API definition.

Added in version 3.0.0.

oracledb.register_password_type(password_type: str, hook_function: Callable) → None

Registers a user password hook function that will be called internally by python-oracledb when a password is supplied as a dictionary containing the given password_type as the key “type”. The hook function is called for passwords specified as the password, newpassword and wallet_parameter parameters in calls to oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), and oracledb.create_pool_async().

Your hook function is expected to accept the dictionary supplied by the application and return the valid password.

Calling register_password_type() with the hook_function parameter set to None will result in a previously registered user function being removed and the default behavior restored.

See Using Password Hook Functions.

This method is an extension to the DB API definition.

Added in version 3.0.0.

oracledb.register_protocol(protocol: str, hook_function: Callable) → None

Registers a user protocol hook function that will be called internally by python-oracledb Thin mode prior to connection or pool creation. The hook function will be invoked when oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), or oracledb.create_pool_async() are called with a dsn parameter value prefixed with the specified protocol. The user function will also be invoked when ConnectParams.parse_connect_string() is called in Thin or Thick modes with a similar connect_string parameter value.

Your hook function is expected to construct valid connection details. For example, if a hook function is registered for the “ldaps” protocol, then calling oracledb.connect() with a connection string prefixed with “ldaps://” will invoke the function. The function can then perform LDAP lookup to retrieve and set the actual database information that will be used internally by python-oracledb to complete the connection creation.

The protocol parameter is a string that will be matched against the prefix appearing before “://” in connection strings.

The hook_function parameter should be a function with the signature:

hook_function(protocol, protocol_arg, params)

The hook function will be called with the following arguments:

The protocol parameter is the value that was registered.
The protocol_arg parameter is the section after “://” in the connection string used in the connection or pool creation call, or passed to parse_connect_string().
The params parameter is an instance of ConnectParams.

When your hook function is invoked internally prior to connection or pool creation, params will be the ConnectParams instance originally passed to the oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), or oracledb.create_pool_async() call, if such an instance was passed. Otherwise it will be a new ConnectParams instance. The hook function should parse protocol and protocol_arg and take any desired action to update params attributes with appropriate connection parameters. Attributes can be set using ConnectParams.set() or ConnectParams.parse_connect_string(). The ConnectParams instance will then be used to complete the connection or pool creation.

When your hook function is invoked by ConnectParams.parse_connect_string(), then params will be the invoking ConnectParams instance that you can update using ConnectParams.set() or ConnectParams.parse_connect_string().

Internal hook functions for the “tcp” and “tcps” protocols are pre-registered but can be overridden if needed. If any other protocol has not been registered, then connecting will result in the error DPY-4021: invalid protocol.

Calling register_protocol() with the hook_function parameter set to None will result in a previously registered user function being removed and the default behavior restored.

See Using Protocol Hook Functions for more information.

This method is an extension to the DB API definition.

Added in version 2.5.0.

oracledb.Time(hour: int, minute: int, second: int) → None: Constructor mandated by the database API for creating a time value. Since Oracle doesn’t support time only values, an exception is raised when this method is called.

oracledb.TimeFromTicks(ticks: float) → None: Constructor mandated by the database API for creating a time value given the number of seconds since the epoch (January 1, 1970). Since Oracle doesn’t support time only values, an exception is raised when this method is called.

oracledb.Timestamp(year: int, month: int, day: int, hour: int = 0, minute: int = 0, second: int = 0) → datetime: Constructs an object holding a time stamp value.

oracledb.TimestampFromTicks(ticks: float) → datetime: Constructor mandated by the database API for creating a timestamp value given the number of seconds since the epoch (January 1, 1970). This is equivalent to using datetime.datetime.fromtimestamp() and that should be used instead.

oracledb.unregister_params_hook(hook_function: Callable) → None

Unregisters a user parameter function that was earlier registered with a call to oracledb.register_params_hook().

This method is an extension to the DB API definition.

Added in version 3.0.0.

1.2. Oracledb Attributes

oracledb.apilevel: str = '2.0': A string constant stating the Python DB API level supported by python-oracledb.

oracledb.defaults: Defaults

The defaults object for setting default behaviors of python-oracledb.

See Changing python-oracledb Default Settings.

This attribute is an extension to the DB API definition.

oracledb.paramstyle: str = 'named': A string constant stating the type of parameter marker formatting expected by the interface. Currently ‘named’ as in ‘where name = :name’.

oracledb.threadsafety: int = 2: An integer constant stating the level of thread safety that python-oracledb supports. Currently 2, which means that threads may share the module and connections, but not cursors. Sharing means that a thread may use a resource without wrapping it using a mutex semaphore to implement resource locking.

oracledb.__version__: str = '3.5.0b1'

A string constant stating the version of the module.

This attribute is an extension to the DB API definition.

1.3. Oracledb Constants

1.3.1. Advanced Queuing: Delivery Modes

The AQ Delivery mode constants are possible values for the deliverymode attribute of the dequeue options object passed as the options parameter to the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), and AsyncQueue.deqmany() methods as well as the deliverymode attribute of the enqueue options object passed as the options parameter to the Queue.enqone(), Queue.enqmany(), AsyncQueue.enqone(), and AsyncQueue.enqmany() methods. They are also possible values for the deliverymode attribute of the message properties object passed as the msgproperties parameter to the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), or AsyncQueue.deqmany(), and Queue.enqone(), Queue.enqmany(), AsyncQueue.enqone(), or AsyncQueue.enqmany() methods.

These constants are extensions to the DB API definition.

oracledb.MSG_BUFFERED: int = 2

This constant is used to specify that enqueue or dequeue operations should enqueue or dequeue buffered messages, respectively. For multi-consumer queues, a subscriber with buffered delivery mode needs to be created prior to enqueuing buffered messages.

This mode is not supported for bulk array operations in python-oracledb Thick mode, and for JSON payloads.

oracledb.MSG_PERSISTENT: int = 1: This constant is used to specify that enqueue/dequeue operations should enqueue or dequeue persistent messages. This is the default value.

oracledb.MSG_PERSISTENT_OR_BUFFERED: int = 3: This constant is used to specify that dequeue operations should dequeue either persistent or buffered messages.

1.3.2. Advanced Queuing: Dequeue Modes

The AQ Dequeue mode constants are possible values for the mode attribute of the dequeue options object. This object is the options parameter for the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), or AsyncQueue.deqmany() methods.

These constants are extensions to the DB API definition.

oracledb.DEQ_BROWSE: int = 1: This constant is used to specify that dequeue should read the message without acquiring any lock on the message (equivalent to a select statement).

oracledb.DEQ_LOCKED: int = 2: This constant is used to specify that dequeue should read and obtain a write lock on the message for the duration of the transaction (equivalent to a select for update statement).

oracledb.DEQ_REMOVE: int = 3: This constant is used to specify that dequeue should read the message and update or delete it. This is the default value.

oracledb.DEQ_REMOVE_NODATA: int = 4: This constant is used to specify that dequeue should confirm receipt of the message but not deliver the actual message content.

1.3.3. Advanced Queuing: Dequeue Navigation Modes

The AQ Dequeue Navigation mode constants are possible values for the navigation attribute of the dequeue options object. This object is the options parameter for the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), or AsyncQueue.deqmany() methods.

These constants are extensions to the DB API definition.

oracledb.DEQ_FIRST_MSG: int = 1: This constant is used to specify that dequeue should retrieve the first available message that matches the search criteria. This resets the position to the beginning of the queue.

oracledb.DEQ_NEXT_MSG: int = 3: This constant is used to specify that dequeue should retrieve the next available message that matches the search criteria. If the previous message belongs to a message group, AQ retrieves the next available message that matches the search criteria and belongs to the message group. This is the default.

oracledb.DEQ_NEXT_TRANSACTION: int = 2: This constant is used to specify that dequeue should skip the remainder of the transaction group and retrieve the first message of the next transaction group. This option can only be used if message grouping is enabled for the current queue.

1.3.4. Advanced Queuing: Dequeue Visibility Modes

The AQ Dequeue Visibility mode constants are possible values for the visibility attribute of the dequeue options object. This object is the options parameter for the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), or AsyncQueue.deqmany() methods.

These constants are extensions to the DB API definition.

oracledb.DEQ_IMMEDIATE: int = 1: This constant is used to specify that dequeue should perform its work as part of an independent transaction.

oracledb.DEQ_ON_COMMIT: int = 2: This constant is used to specify that dequeue should be part of the current transaction. This is the default value.

1.3.5. Advanced Queuing: Dequeue Wait Modes

The AQ Dequeue Wait mode constants are possible values for the wait attribute of the dequeue options object. This object is the options parameter for the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone(), or AsyncQueue.deqmany() methods.

These constants are extensions to the DB API definition.

oracledb.DEQ_NO_WAIT: int = 0: This constant is used to specify that dequeue not wait for messages to be available for dequeuing.

oracledb.DEQ_WAIT_FOREVER: int = 4294967295: This constant is used to specify that dequeue should wait forever for messages to be available for dequeuing. This is the default value.

1.3.6. Advanced Queuing: Enqueue Visibility Modes

The AQ Enqueue Visibility mode constants are possible values for the visibility attribute of the enqueue options object. This object is the options parameter for the Queue.enqone(), Queue.enqmany(), AsyncQueue.enqone(), or AsyncQueue.enqmany() methods.

These constants are extensions to the DB API definition.

oracledb.ENQ_IMMEDIATE: int = 1

This constant is used to specify that enqueue should perform its work as part of an independent transaction.

The use of this constant with bulk enqueuing is only supported in python-oracledb Thick mode.

1.3.7. Advanced Queuing: Message States

The AQ Message state constants are possible values for the state attribute of the message properties object. This object is the msgproperties parameter for the Queue.deqone(), Queue.deqmany(), AsyncQueue.deqone() or AsyncQueue.deqmany() and Queue.enqone(), Queue.enqmany(), AsyncQueue.enqone(), or AsyncQueue.enqmany() methods.

These constants are extensions to the DB API definition.

oracledb.MSG_EXPIRED: int = 3: This constant is used to specify that the message has been moved to the exception queue.

oracledb.MSG_PROCESSED: int = 2: This constant is used to specify that the message has been processed and has been retained.

oracledb.MSG_READY: int = 0: This constant is used to specify that the message is ready to be processed.

oracledb.MSG_WAITING: int = 1: This constant is used to specify that the message delay has not yet been reached.

1.3.8. Advanced Queuing: Other Constants

This section contains other constants that are used for Advanced Queueing.

These constants are extensions to the DB API definition.

oracledb.MSG_NO_DELAY: int = 0: This constant is a possible value for the delay attribute of the message properties object passed as the msgproperties parameter to the Queue.deqone() or Queue.deqmany() and Queue.enqone() or Queue.enqmany() methods. It specifies that no delay should be imposed and the message should be immediately available for dequeuing. This is also the default value.

oracledb.MSG_NO_EXPIRATION: int = -1: This constant is a possible value for the expiration attribute of the message properties object passed as the msgproperties parameter to the Queue.deqone() or Queue.deqmany() and Queue.enqone() or Queue.enqmany() methods. It specifies that the message never expires. This is also the default value.

1.3.9. Connection Authorization Modes

The Connection Authorization mode constants belong to the enumeration called AuthMode. They are possible values for the mode parameters of oracledb.connect(), oracledb.create_pool(), oracledb.connect_async(), and oracledb.create_pool_async(). These constants have deprecated the authorization modes used in the obsolete cx_Oracle driver.

These constants are extensions to the DB API definition.

Changed in version 2.3.0: The integer constants for the connection authorization modes were replaced with the enumeration AuthMode.

oracledb.AUTH_MODE_DEFAULT: AuthMode = <AuthMode.DEFAULT: 0>

This constant is used to specify that default authentication is to take place. This is the default value if no mode is passed at all.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the DEFAULT_AUTH constant that was used in the obsolete cx_Oracle driver, and was the default mode value.

oracledb.AUTH_MODE_PRELIM: AuthMode = <AuthMode.PRELIM: 8>

This constant is used to specify that preliminary authentication is to be used. This is needed for performing database startup and shutdown.

It can only be used in python-oracledb Thick mode for standalone connections.

This constant deprecates the PRELIM_AUTH constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSASM: AuthMode = <AuthMode.SYSASM: 32768>

This constant is used to specify that SYSASM access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSASM constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSBKP: AuthMode = <AuthMode.SYSBKP: 131072>

This constant is used to specify that SYSBACKUP access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSBKP constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSDBA: AuthMode = <AuthMode.SYSDBA: 2>

This constant is used to specify that SYSDBA access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSDBA constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSDGD: AuthMode = <AuthMode.SYSDGD: 262144>

This constant is used to specify that SYSDG access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSDGD constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSKMT: AuthMode = <AuthMode.SYSKMT: 524288>

This constant is used to specify that SYSKM access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSKMT constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSOPER: AuthMode = <AuthMode.SYSOPER: 4>

This constant is used to specify that SYSOPER access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSOPER constant that was used in the obsolete cx_Oracle driver.

oracledb.AUTH_MODE_SYSRAC: AuthMode = <AuthMode.SYSRAC: 1048576>

This constant is used to specify that SYSRAC access is to be acquired.

It can be used for standalone and pooled connections in python-oracledb Thin mode, and for standalone connections in Thick mode.

This constant deprecates the SYSRAC constant that was used in the obsolete cx_Oracle driver.

1.3.10. Pipeline Operation Types

The Pipeline Operation type constants belong to the enumeration called PipelineOpType. The pipelining constants listed below are used to identify the type of operation added. They are possible values for the PipelineOp.op_type attribute.

Added in version 2.4.0.

oracledb.PIPELINE_OP_TYPE_CALL_FUNC: PipelineOpType = <PipelineOpType.CALL_FUNC: 1>: This constant identifies the type of operation as the calling of a stored function.

oracledb.PIPELINE_OP_TYPE_CALL_PROC: PipelineOpType = <PipelineOpType.CALL_PROC: 2>: This constant identifies the type of operation as the calling of a stored procedure.

oracledb.PIPELINE_OP_TYPE_COMMIT: PipelineOpType = <PipelineOpType.COMMIT: 3>: This constant identifies the type of operation as the performing of a commit.

oracledb.PIPELINE_OP_TYPE_EXECUTE: PipelineOpType = <PipelineOpType.EXECUTE: 4>: This constant identifies the type of operation as the executing of a statement.

oracledb.PIPELINE_OP_TYPE_EXECUTE_MANY: PipelineOpType = <PipelineOpType.EXECUTE_MANY: 5>: This constant identifies the type of operations as the executing of a statement multiple times.

oracledb.PIPELINE_OP_TYPE_FETCH_ALL: PipelineOpType = <PipelineOpType.FETCH_ALL: 6>: This constant identifies the type of operation as the executing of a query and returning all of the rows from the result set.

oracledb.PIPELINE_OP_TYPE_FETCH_MANY: PipelineOpType = <PipelineOpType.FETCH_MANY: 7>: This constant identifies the type of operation as the executing of a query and returning up to the specified number of rows from the result set.

oracledb.PIPELINE_OP_TYPE_FETCH_ONE: PipelineOpType = <PipelineOpType.FETCH_ONE: 8>: This constant identifies the type of operation as the executing of a query and returning the first row of the result set.

1.3.11. Database Shutdown Modes

The Database Shutdown mode constants are possible values for the mode parameter of the Connection.shutdown() method.

These constants are extensions to the DB API definition.

oracledb.DBSHUTDOWN_ABORT: int = 4: This constant is used to specify that the caller should not wait for current processing to complete or for users to disconnect from the database. This should only be used in unusual circumstances since database recovery may be necessary upon next startup.

oracledb.DBSHUTDOWN_FINAL: int = 5: This constant is used to specify that the instance can be truly halted. This should only be done after the database has been shutdown with one of the other modes (except abort) and the database has been closed and dismounted using the appropriate SQL commands.

oracledb.DBSHUTDOWN_IMMEDIATE: int = 3: This constant is used to specify that all uncommitted transactions should be rolled back and any connected users should be disconnected.

oracledb.DBSHUTDOWN_TRANSACTIONAL: int = 1: This constant is used to specify that further connections to the database should be prohibited and no new transactions should be allowed. It then waits for all active transactions to complete.

oracledb.DBSHUTDOWN_TRANSACTIONAL_LOCAL: int = 2: This constant is used to specify that further connections to the database should be prohibited and no new transactions should be allowed. It then waits for only local active transactions to complete.

1.3.12. Event Types

The Event type constants are possible values for the Message.type attribute of the messages that are sent for subscriptions created by the Connection.subscribe() method.

These constants are extensions to the DB API definition.

oracledb.EVENT_AQ: int = 100: This constant is used to specify that one or more messages are available for dequeuing on the queue specified when the subscription was created.

oracledb.EVENT_DEREG: int = 5: This constant is used to specify that the subscription has been deregistered and no further notifications will be sent.

oracledb.EVENT_NONE: int = 0: This constant is used to specify no information is available about the event.

oracledb.EVENT_OBJCHANGE: int = 6: This constant is used to specify that a database change has taken place on a table registered with the Subscription.registerquery() method.

oracledb.EVENT_QUERYCHANGE: int = 7: This constant is used to specify that the result set of a query registered with the Subscription.registerquery() method has been changed.

oracledb.EVENT_SHUTDOWN: int = 2: This constant is used to specify that the instance is in the process of being shut down.

oracledb.EVENT_SHUTDOWN_ANY: int = 3: This constant is used to specify that any instance (when running RAC) is in the process of being shut down.

oracledb.EVENT_STARTUP: int = 1: This constant is used to specify that the instance is in the process of being started up.

1.3.13. Operation Codes

The Operation code constants are possible values for the operations parameter for the Connection.subscribe() method. One or more of these values can be OR’ed together. These values are also used by the MessageTable.operation or MessageQuery.operation attributes of the messages that are sent.

These constants are extensions to the DB API definition.

oracledb.OPCODE_ALLOPS: int = 0: This constant is used to specify that messages should be sent for all operations.

oracledb.OPCODE_ALLROWS: int = 1: This constant is used to specify that the table or query has been completely invalidated.

oracledb.OPCODE_ALTER: int = 16: This constant is used to specify that messages should be sent when a registered table has been altered in some fashion by DDL, or that the message identifies a table that has been altered.

oracledb.OPCODE_DELETE: int = 8: This constant is used to specify that messages should be sent when data is deleted, or that the message identifies a row that has been deleted.

oracledb.OPCODE_DROP: int = 32: This constant is used to specify that messages should be sent when a registered table has been dropped, or that the message identifies a table that has been dropped.

oracledb.OPCODE_INSERT: int = 2: This constant is used to specify that messages should be sent when data is inserted, or that the message identifies a row that has been inserted.

oracledb.OPCODE_UPDATE: int = 4: This constant is used to specify that messages should be sent when data is updated, or that the message identifies a row that has been updated.

1.3.14. Connection Pool Get Modes

The Connection Pool Get mode constants belong to the enumeration called PoolGetMode. They are possible values for the getmode parameters of oracledb.create_pool(), oracledb.create_pool_async(), PoolParams.set(), and for related attributes. These constants have deprecated the Session Pool mode constants that were used in the obsolete cx_Oracle driver.

These constants are extensions to the DB API definition.

Changed in version 2.3.0: The integer constants for the connection pool creation, reconfiguration, and acquisition getmode parameters were replaced with the enumeration PoolGetMode.

oracledb.POOL_GETMODE_FORCEGET: PoolGetMode = PoolGetMode.FORCEGET

This constant is used to specify that a new connection should be created and returned by ConnectionPool.acquire() if there are no free connections available in the pool and the pool is already at its maximum size.

When a connection acquired in this mode is eventually released back to the pool, it will be dropped and not added to the pool if the pool is still at its maximum size.

This constant deprecates the SPOOL_ATTRVAL_FORCEGET constant that was used in the obsolete cx_Oracle driver.

oracledb.POOL_GETMODE_NOWAIT: PoolGetMode = PoolGetMode.NOWAIT

This constant is used to specify that an exception should be raised by ConnectionPool.acquire() when all currently created connections are already in use and so acquire() cannot immediately return a connection. Note the exception may occur even if the pool is smaller than its maximum size.

This constant deprecates the SPOOL_ATTRVAL_NOWAIT constant that was used in the obsolete cx_Oracle driver, and was the default getmode value.

oracledb.POOL_GETMODE_TIMEDWAIT: PoolGetMode = PoolGetMode.TIMEDWAIT

This constant is used to specify that ConnectionPool.acquire() should wait for a period of time (defined by the wait_timeout parameter) for a connection to become available before returning with an error.

This constant deprecates the SPOOL_ATTRVAL_TIMEDWAIT constant that was used in the obsolete cx_Oracle driver.

oracledb.POOL_GETMODE_WAIT: PoolGetMode = PoolGetMode.WAIT

This constant is used to specify that ConnectionPool.acquire() should wait until a connection is available if there are currently no free connections available in the pool. This is the default value.

This constant deprecates the SPOOL_ATTRVAL_WAIT constant that was used in the obsolete cx_Oracle driver.

1.3.15. Connection Pool Purity Constants

The Connection Pool Purity constants belong to the enumeration called Purity. They are possible values for the Database Resident Connection Pooling (DRCP) purity parameter of oracledb.create_pool(), ConnectionPool.acquire(), oracledb.connect(), oracledb.create_pool_async(), and oracledb.connect_async(). These constants have deprecated the Session Pool purity constants that were used in the obsolete cx_Oracle driver.

These constants are extensions to the DB API definition.

Changed in version 2.3.0: The integer constants for the connection pool get modes were replaced with the enumeration Purity.

oracledb.PURITY_DEFAULT: Purity = Purity.DEFAULT

This constant is used to specify that the purity of the session is the default value identified by Oracle (see Oracle’s documentation for more information). This is the default value.

This constant deprecates the ATTR_PURITY_DEFAULT constant that was used in the obsolete cx_Oracle driver, and was the default purity value.

oracledb.PURITY_NEW: Purity = Purity.NEW

This constant is used to specify that the session acquired from the pool should be new and not have any prior session state.

This constant deprecates the ATTR_PURITY_NEW constant that was used in the obsolete cx_Oracle driver.

oracledb.PURITY_SELF: Purity = Purity.SELF

This constant is used to specify that the session acquired from the pool need not be new and may have prior session state.

This constant deprecates the ATTR_PURITY_SELF constant that was used in the obsolete cx_Oracle driver.

1.3.16. Subscription Grouping Classes

The Subscription Grouping Class constants are possible values for the groupingClass parameter of the Connection.subscribe() method.

These constants are extensions to the DB API definition.

oracledb.SUBSCR_GROUPING_CLASS_NONE: int = 0: This constant is used to specify that no grouping should take place.

oracledb.SUBSCR_GROUPING_CLASS_TIME: int = 1: This constant is used to specify that events are to be grouped by the period of time in which they are received.

1.3.17. Subscription Grouping Types

The Subscription Grouping Type constants are possible values for the groupingType parameter of the Connection.subscribe() method.

These constants are extensions to the DB API definition.

oracledb.SUBSCR_GROUPING_TYPE_SUMMARY: int = 1: This constant is used to specify that when events are grouped a summary of the events should be sent instead of the individual events. This is the default value.

oracledb.SUBSCR_GROUPING_TYPE_LAST: int = 2: This constant is used to specify that when events are grouped the last event that makes up the group should be sent instead of the individual events.

1.3.18. Subscription Namespaces

The Subscription Namespace constants are possible values for the namespace parameter of the Connection.subscribe() method.

These constants are extensions to the DB API definition.

oracledb.SUBSCR_NAMESPACE_AQ: int = 1: This constant is used to specify that notifications should be sent when a queue has messages available to dequeue.

oracledb.SUBSCR_NAMESPACE_DBCHANGE: int = 2: This constant is used to specify that database change notification or query change notification messages are to be sent. This is the default value.

1.3.19. Subscription Protocols

The Subscription Protocol constants are possible values for the protocol parameter of the Connection.subscribe() method.

These constants are extensions to the DB API definition.

oracledb.SUBSCR_PROTO_CALLBACK: int = 0: This constant is used to specify that notifications will be sent to the callback routine identified when the subscription was created. It is the default value and the only value currently supported.

oracledb.SUBSCR_PROTO_HTTP: int = 3: This constant is used to specify that notifications will be sent to an HTTP URL when a message is generated. This value is currently not supported.

oracledb.SUBSCR_PROTO_MAIL: int = 1: This constant is used to specify that notifications will be sent to an e-mail address when a message is generated. This value is currently not supported.

oracledb.SUBSCR_PROTO_SERVER: int = 2: This constant is used to specify that notifications will be sent to a PL/SQL procedure when a message is generated. This value is currently not supported.

1.3.20. Subscription Quality of Service

The Subscription Quality of Service constants are possible values for the qos parameter of the Connection.subscribe() method. One or more of these values can be OR’ed together.

These constants are extensions to the DB API definition.

oracledb.SUBSCR_QOS_BEST_EFFORT: int = 16: This constant is used to specify that best effort filtering for query result set changes is acceptable. False positive notifications may be received. This behaviour may be suitable for caching applications.

oracledb.SUBSCR_QOS_DEFAULT: int = 0: This constant is used to specify that the default behavior for subscriptions should be used.

oracledb.SUBSCR_QOS_DEREG_NFY: int = 2: This constant is used to specify that the subscription should be automatically unregistered after the first notification is received.

oracledb.SUBSCR_QOS_QUERY: int = 8: This constant is used to specify that notifications should be sent if the result set of the registered query changes. By default, no false positive notifications will be generated.

oracledb.SUBSCR_QOS_RELIABLE: int = 1: This constant is used to specify that notifications should not be lost in the event of database failure.

oracledb.SUBSCR_QOS_ROWIDS: int = 4: This constant is used to specify that the rowids of the inserted, updated or deleted rows should be included in the message objects that are sent.

1.3.21. DB API Types

class oracledb.ApiType: This type object is the Python type of the database API type constants.

oracledb.BINARY: ApiType: This type object is used to describe columns in a database that contain binary data. The database types DB_TYPE_RAW and DB_TYPE_LONG_RAW will compare equal to this value. If a variable is created with this type, the database type DB_TYPE_RAW will be used.

oracledb.DATETIME: ApiType: This type object is used to describe columns in a database that are dates. The database types DB_TYPE_DATE, DB_TYPE_TIMESTAMP, DB_TYPE_TIMESTAMP_LTZ and DB_TYPE_TIMESTAMP_TZ will all compare equal to this value. If a variable is created with this type, the database type DB_TYPE_DATE will be used.

oracledb.NUMBER: ApiType: This type object is used to describe columns in a database that are numbers. The database types DB_TYPE_BINARY_DOUBLE, DB_TYPE_BINARY_FLOAT, DB_TYPE_BINARY_INTEGER and DB_TYPE_NUMBER will all compare equal to this value. If a variable is created with this type, the database type DB_TYPE_NUMBER will be used.

oracledb.ROWID: ApiType: This type object is used to describe the pseudo column “rowid”. The database types DB_TYPE_ROWID and DB_TYPE_UROWID will compare equal to this value. If a variable is created with this type, the database type DB_TYPE_VARCHAR will be used.

oracledb.STRING: ApiType: This type object is used to describe columns in a database that are strings. The database types DB_TYPE_CHAR, DB_TYPE_LONG, DB_TYPE_NCHAR, DB_TYPE_NVARCHAR and DB_TYPE_VARCHAR will all compare equal to this value. If a variable is created with this type, the database type DB_TYPE_VARCHAR will be used.

1.3.22. Database Types

class oracledb.DbType

This type object is the Python type of the database type constants. All of these types are extensions to the DB API definition. They are found in query and object metadata. They can also be used to specify the database type when binding data.

Also see the table Supported Oracle Database Data Types.

oracledb.DB_TYPE_BFILE: DbType: Describes columns, attributes or array elements in a database that are of type BFILE. It will compare equal to the DB API type BINARY.

oracledb.DB_TYPE_BINARY_DOUBLE: DbType: Describes columns, attributes or array elements in a database that are of type BINARY_DOUBLE. It will compare equal to the DB API type NUMBER.

oracledb.DB_TYPE_BINARY_FLOAT: DbType: Describes columns, attributes or array elements in a database that are of type BINARY_FLOAT. It will compare equal to the DB API type NUMBER.

oracledb.DB_TYPE_BINARY_INTEGER: DbType: Describes attributes or array elements in a database that are of type BINARY_INTEGER. It will compare equal to the DB API type NUMBER.

oracledb.DB_TYPE_BLOB: DbType: Describes columns, attributes or array elements in a database that are of type BLOB. It will compare equal to the DB API type BINARY.

oracledb.DB_TYPE_BOOLEAN: DbType: Describes attributes or array elements in a database that are of type BOOLEAN. It is only available in Oracle 12.1 and higher and only within PL/SQL.

oracledb.DB_TYPE_CHAR: DbType

Describes columns, attributes or array elements in a database that are of type CHAR. It will compare equal to the DB API type STRING.

Note that these are fixed length string values and behave differently from VARCHAR2.

oracledb.DB_TYPE_CLOB: DbType: Describes columns, attributes or array elements in a database that are of type CLOB. It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_CURSOR: DbType: Describes columns in a database that are of type CURSOR. In PL/SQL, these are known as REF CURSOR.

oracledb.DB_TYPE_DATE: DbType: Describes columns, attributes or array elements in a database that are of type DATE. It will compare equal to the DB API type DATETIME.

oracledb.DB_TYPE_INTERVAL_DS: DbType: Describes columns, attributes or array elements in a database that are of type INTERVAL DAY TO SECOND.

oracledb.DB_TYPE_INTERVAL_YM: DbType: Describes columns, attributes or array elements in a database that are of type INTERVAL YEAR TO MONTH.

oracledb.DB_TYPE_JSON: DbType: Describes columns in a database that are of type JSON (with Oracle Database 21 or later).

oracledb.DB_TYPE_LONG: DbType: Describes columns, attributes or array elements in a database that are of type LONG. It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_LONG_NVARCHAR: DbType

This constant can be used in output type handlers when fetching NCLOB columns as a string. (Note a type handler is not needed if oracledb.defaults.fetch_lobs, or the equivalent execution parameter, is set to False). For IN binds, this constant can be used to create a bind variable in Cursor.var() or via Cursor.setinputsizes(). The DB_TYPE_LONG_NVARCHAR value won’t be shown in query metadata since it is not a database type.

It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_LONG_RAW: DbType: Describes columns, attributes or array elements in a database that are of type LONG RAW. It will compare equal to the DB API type BINARY.

oracledb.DB_TYPE_NCHAR: DbType

Describes columns, attributes or array elements in a database that are of type NCHAR. It will compare equal to the DB API type STRING.

Note that these are fixed length string values and behave differently from NVARCHAR2.

oracledb.DB_TYPE_NCLOB: DbType: Describes columns, attributes or array elements in a database that are of type NCLOB. It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_NUMBER: DbType: Describes columns, attributes or array elements in a database that are of type NUMBER. It will compare equal to the DB API type NUMBER.

oracledb.DB_TYPE_NVARCHAR: DbType: Describes columns, attributes or array elements in a database that are of type NVARCHAR2. It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_OBJECT: DbType: Describes columns, attributes or array elements in a database that are an instance of a named SQL or PL/SQL type.

oracledb.DB_TYPE_RAW: DbType: Describes columns, attributes or array elements in a database that are of type RAW. It will compare equal to the DB API type BINARY.

oracledb.DB_TYPE_ROWID: DbType: Describes columns, attributes or array elements in a database that are of type ROWID or UROWID. It will compare equal to the DB API type ROWID.

oracledb.DB_TYPE_TIMESTAMP: DbType: Describes columns, attributes or array elements in a database that are of type TIMESTAMP. It will compare equal to the DB API type DATETIME.

oracledb.DB_TYPE_TIMESTAMP_LTZ: DbType: Describes columns, attributes or array elements in a database that are of type TIMESTAMP WITH LOCAL TIME ZONE. It will compare equal to the DB API type DATETIME.

oracledb.DB_TYPE_TIMESTAMP_TZ: DbType: Describes columns, attributes or array elements in a database that are of type TIMESTAMP WITH TIME ZONE. It will compare equal to the DB API type DATETIME.

oracledb.DB_TYPE_UNKNOWN: DbType: Describes columns, attributes or array elements in a database that are of an unknown type.

oracledb.DB_TYPE_UROWID: DbType: Describes columns, attributes or array elements in a database that are of type UROWID. It will compare equal to the DB API type ROWID.

Note

This type is not supported in python-oracledb Thick mode. See Query Metadata in Thin and Thick Modes.

oracledb.DB_TYPE_VARCHAR: DbType: Describes columns, attributes or array elements in a database that are of type VARCHAR2. It will compare equal to the DB API type STRING.

oracledb.DB_TYPE_VECTOR: DbType: Describes columns, attributes or array elements in a database that are of type VECTOR (with Oracle Database 23 or later).

Added in version 2.2.0.

oracledb.DB_TYPE_XMLTYPE: DbType: Describes columns, attributes or array elements in a database that are of type SYS.XMLTYPE.

Added in version 2.0.0.

1.3.23. Database Type Synonyms

All of the following constants are deprecated and will be removed in a future version of python-oracledb.

oracledb.BFILE: A synonym for DB_TYPE_BFILE.

Deprecated since cx_Oracle 8.0.

oracledb.BLOB: A synonym for DB_TYPE_BLOB.

Deprecated since cx_Oracle 8.0.

oracledb.BOOLEAN: A synonym for DB_TYPE_BOOLEAN.

Deprecated since cx_Oracle 8.0.

oracledb.CLOB: A synonym for DB_TYPE_CLOB.

Deprecated since cx_Oracle 8.0.

oracledb.CURSOR: A synonym for DB_TYPE_CURSOR.

Deprecated since cx_Oracle 8.0.

oracledb.FIXED_CHAR: A synonym for DB_TYPE_CHAR.

Deprecated since cx_Oracle 8.0.

oracledb.FIXED_NCHAR: A synonym for DB_TYPE_NCHAR.

Deprecated since cx_Oracle 8.0.

oracledb.INTERVAL: A synonym for DB_TYPE_INTERVAL_DS.

Deprecated since cx_Oracle 8.0.

oracledb.LONG_BINARY: A synonym for DB_TYPE_LONG_RAW.

Deprecated since cx_Oracle 8.0.

oracledb.LONG_STRING: A synonym for DB_TYPE_LONG.

Deprecated since cx_Oracle 8.0.

oracledb.NATIVE_FLOAT: A synonym for DB_TYPE_BINARY_DOUBLE.

Deprecated since cx_Oracle 8.0.

oracledb.NATIVE_INT: A synonym for DB_TYPE_BINARY_INTEGER.

Deprecated since cx_Oracle 8.0.

oracledb.NCHAR: A synonym for DB_TYPE_NCHAR.

Deprecated since cx_Oracle 8.0.

oracledb.NCLOB: A synonym for DB_TYPE_NCLOB.

Deprecated since cx_Oracle 8.0.

oracledb.OBJECT: A synonym for DB_TYPE_OBJECT.

Deprecated since cx_Oracle 8.0.

oracledb.TIMESTAMP: A synonym for DB_TYPE_TIMESTAMP.

Deprecated since cx_Oracle 8.0.

1.3.24. Two-Phase Commit (TPC) Constants

The constants for the two-phase commit (TPC) functions tpc_begin() and tpc_end() are listed below.

oracledb.TPC_BEGIN_JOIN: int = 2: This constant is used to join an existing TPC transaction.

oracledb.TPC_BEGIN_NEW: int = 1: This constant is used to create a new TPC transaction.

oracledb.TPC_BEGIN_PROMOTE: int = 8: This constant is used to promote a local transaction to a TPC transaction.

oracledb.TPC_BEGIN_RESUME: int = 4: This constant is used to resume an existing TPC transaction.

oracledb.TPC_END_NORMAL: int = 0: This constant is used to end TPC transaction participation normally.

oracledb.TPC_END_SUSPEND: int = 1048576: This constant is used to suspend a TPC transaction.

1.3.25. Vector Format Constants

These constants belong to the enumeration called VectorFormat and are possible values for the FetchInfo.vector_format attribute.

Added in version 2.2.0.

Changed in version 2.3.0: The integer constants for the vector format constants were replaced with the enumeration VectorFormat.

oracledb.VECTOR_FORMAT_BINARY: VectorFormat = VectorFormat.BINARY: This constant is used to represent the storage format of VECTOR columns using 8-bit unsigned integers.

Added in version 2.3.0.

oracledb.VECTOR_FORMAT_FLOAT32: VectorFormat = VectorFormat.FLOAT32: This constant is used to represent the storage format of VECTOR columns using 32-bit floating point numbers.

oracledb.VECTOR_FORMAT_FLOAT64: VectorFormat = VectorFormat.FLOAT64: This constant is used to represent the storage format of VECTOR columns using 64-bit floating point numbers.

oracledb.VECTOR_FORMAT_INT8: VectorFormat = VectorFormat.INT8: This constant is used to represent the storage format of VECTOR columns using 8-bit signed integers.

1.4. Oracledb Exceptions

See Catching Exceptions for usage information.

exception oracledb.Warning

Exception raised for warnings.

Exception messages of this class will have the prefix DPY and an error number in the range 9000 - 9999.

exception oracledb.Error: Exception that is the base class of all other exceptions defined by python-oracledb.

exception oracledb.DataError

Exception raised for errors that are due to problems with the processed data. It is a subclass of DatabaseError.

Exception messages of this class are generated by the database and will have a prefix such as ORA.

exception oracledb.DatabaseError

Exception raised for errors that are related to the database. It is a subclass of Error.

Exception messages of this class will have the prefix DPY and an error number in the range 4000 - 4999.

exception oracledb.IntegrityError

Exception raised when the relational integrity of the database is affected. It is a subclass of DatabaseError.

Exception messages of this class are generated by the database and will have a prefix such as ORA.

exception oracledb.InterfaceError

Exception raised for errors that are related to the database interface rather than the database itself. It is a subclass of Error.

Exception messages of this class will have the prefix DPY and an error number in the range 1000 - 1999.

exception oracledb.InternalError

Exception raised when the database encounters an internal error. It is a subclass of DatabaseError.

Exception messages of this class will have the prefix DPY and an error number in the range 5000 - 5999.

exception oracledb.NotSupportedError

Exception raised when a method or database API was used which is not supported by the database. It is a subclass of DatabaseError.

Exception messages of this class will have the prefix DPY and an error number in the range 3000 - 3999.

exception oracledb.OperationalError

Exception raised for errors that are related to the operation of the database but are not necessarily under the control of the programmer. It is a subclass of DatabaseError.

Exception messages of this class will have the prefix DPY and an error number in the range 6000 - 6999.

exception oracledb.ProgrammingError

Exception raised for programming errors. It is a subclass of DatabaseError.

Exception messages of this class will have the prefix DPY and an error number in the range 2000 - 2999.

1.5. Oracledb _Error Objects

See Catching Exceptions for usage information.

Note

PEP 249 (Python Database API Specification v2.0) says the following about exception values:

[…] The values of these exceptions are not defined. They should give the user a fairly good idea of what went wrong, though. […]

With python-oracledb every exception object has exactly one argument in the args tuple. This argument is an oracledb._Error object which has the following six read-only attributes.

_Error.code: Integer attribute representing the Oracle error number (ORA-XXXXX).

_Error.offset: Integer attribute representing the error offset when applicable.

_Error.full_code: String attribute representing the top-level error prefix and the code that is shown in the _Error.message.

_Error.message: String attribute representing the Oracle message of the error. This message may be localized by the environment of the Oracle connection.

_Error.context: String attribute representing the context in which the exception was raised.

_Error.isrecoverable

Boolean attribute representing whether the error is recoverable or not. This requires Oracle Database 12.1 (or later). If python-oracledb Thick mode is used, then Oracle Client 12.1 (or later) is also required.

See Transaction Guard for more information.

1.6. Oracledb future Object

A special object that contains attributes which control the behavior of python-oracledb, allowing for opting in for new features.

1.7. Oracledb Plugins

The namespace package oracledb.plugins can contain plugins to extend the capability of python-oracledb. See Building Custom Plugins. Note that the namespace oracledb.plugins.ldap_support is reserved for future use by the python-oracledb project.

To use the python-oracledb plugins in your application, import using import oracledb.plugins.<name of plugin>, for example:

import oracledb.plugins.oci_config_provider

Added in version 3.0.0.

1.7.1. Oracle Cloud Infrastructure (OCI) Object Storage Configuration Provider Plugin

oci_config_provider is a plugin that can be imported to provide access to database connection credentials and application configuration information stored in the OCI Object Storage configuration provider.

This plugin is implemented as a connection protocol hook function to handle connection strings which have the prefix config-ociobject, see OCI Object Storage connection strings. The plugin parses these connection strings and gets the stored configuration information. Python-oracledb then uses this information to connect to Oracle Database.

To use this plugin in python-oracledb Thick mode, you must set oracledb.defaults.thick_mode_dsn_passthrough to False. Alternatively use ConnectParams.parse_connect_string(), see Using the ConnectParams Builder Class.

See Using an OCI Object Storage Centralized Configuration Provider for more information.

Added in version 3.0.0.

1.7.2. Azure App Configuration Provider Plugin

azure_config_provider is a plugin that can be imported to provide access to database connection credentials and application configuration information stored in the Azure App Configuration provider.

This plugin is implemented as a connection protocol hook function to handle connection strings which have the prefix config-azure, see Azure App Configuration connection strings. The plugin parses these connection strings and gets the stored configuration information. Python-oracledb then uses this information to connect to Oracle Database.

To use this plugin in python-oracledb Thick mode, you must set oracledb.defaults.thick_mode_dsn_passthrough to False. Alternatively use ConnectParams.parse_connect_string(), see Using the ConnectParams Builder Class.

See Using an Azure App Centralized Configuration Provider for more information.

Added in version 3.0.0.

1.7.3. Oracle Cloud Infrastructure (OCI) Cloud Native Authentication Plugin

oci_tokens is a plugin that can be imported to use the Oracle Cloud Infrastructure (OCI) Software Development Kit (SDK) for generating access tokens when authenticating with OCI Identity and Access Management (IAM) token-based authentication.

This plugin is implemented as a parameter hook function which uses the extra_auth_params parameter values of your connection and pool creation calls to generate OCI IAM access tokens. Python-oracledb then uses these tokens to connect to Oracle Database.

See OCI Cloud Native Authentication with the oci_tokens Plugin for more information.

Added in version 3.0.0.

1.7.4. Azure Cloud Native Authentication Plugin

azure_tokens is a plugin that can be imported to use the Microsoft Authentication Library (MSAL) for generating access tokens when authenticating with OAuth 2.0 token-based authentication.

This plugin is implemented as a parameter hook function which uses the extra_auth_params parameter values of your connection and pool creation calls to generate OAuth2 access tokens. Python-oracledb then uses these tokens to connect to Oracle Database.

See Azure Cloud Native Authentication with the azure_tokens Plugin for more information.

Added in version 3.0.0.

1.8. Oracledb IntervalYM Class

Objects of this class are returned for columns of type INTERVAL YEAR TO MONTH and can be passed to variables of type oracledb.DB_TYPE_INTERVAL_YM The class is a collections.namedtuple() class with two integer attributes, years and months.

Added in version 2.2.0.

1.9. Oracledb JsonId Class

Objects of this class are returned by SODA in the _id attribute of documents stored in native collections when using Oracle Database 23.4 (and later). It is a subclass of the bytes class.

Added in version 2.1.0.

This method is an extension to the DB API definition.

1. API: python-oracledb Module

1.1. Oracledb Methods

1.2. Oracledb Attributes

1.3. Oracledb Constants

1.3.1. Advanced Queuing: Delivery Modes

1.3.2. Advanced Queuing: Dequeue Modes

1.3.3. Advanced Queuing: Dequeue Navigation Modes

1.3.4. Advanced Queuing: Dequeue Visibility Modes

1.3.5. Advanced Queuing: Dequeue Wait Modes

1.3.6. Advanced Queuing: Enqueue Visibility Modes

1.3.7. Advanced Queuing: Message States

1.3.8. Advanced Queuing: Other Constants

1.3.9. Connection Authorization Modes

1.3.10. Pipeline Operation Types

1.3.11. Database Shutdown Modes

1.3.12. Event Types

1.3.13. Operation Codes

1.3.14. Connection Pool Get Modes

1.3.15. Connection Pool Purity Constants

1.3.16. Subscription Grouping Classes

1.3.17. Subscription Grouping Types

1.3.18. Subscription Namespaces

1.3.19. Subscription Protocols

1.3.20. Subscription Quality of Service

1.3.21. DB API Types

1.3.22. Database Types

1.3.23. Database Type Synonyms

1.3.24. Two-Phase Commit (TPC) Constants

1.3.25. Vector Format Constants

1.4. Oracledb Exceptions

1.5. Oracledb _Error Objects

1.6. Oracledb __future__ Object

1.7. Oracledb Plugins

1.7.1. Oracle Cloud Infrastructure (OCI) Object Storage Configuration Provider Plugin

1.7.2. Azure App Configuration Provider Plugin

1.7.3. Oracle Cloud Infrastructure (OCI) Cloud Native Authentication Plugin

1.7.4. Azure Cloud Native Authentication Plugin

1.8. Oracledb IntervalYM Class

1.9. Oracledb JsonId Class

1.6. Oracledb future Object