23. Appendix A: Oracle Database Features Supported by python-oracledb
By default, python-oracledb runs in a ‘Thin’ mode which connects directly to Oracle Database. This mode does not need Oracle Client libraries. However, some additional functionality is available when python-oracledb uses them. Python-oracledb is said to be in ‘Thick’ mode when Oracle Client libraries are used. Both modes have comprehensive functionality supporting the Python Database API v2.0 Specification. See Initializing python-oracledb for how to enable Thick mode.
The following table summarizes the Oracle Database features supported by python-oracledb Thin and Thick modes, and by cx_Oracle 8.3. For more details see Appendix B: Differences between python-oracledb Thin and Thick Modes and Differences between the python-oracledb and cx_Oracle Drivers.
Oracle Feature |
python-oracledb Thin Mode |
python-oracledb Thick Mode |
cx_Oracle 8.3 |
|---|---|---|---|
Python Database API Support |
Yes - a couple of features are not feasible. Many extensions. |
Yes - a couple of features are not feasible. Many extensions. |
Yes - a couple of features are not feasible. Many extensions. |
Oracle Client version |
Not applicable |
Release 11.2 and later |
Release 11.2 and later |
Oracle Database version |
Release 12.1 and later |
Release 9.2 and later depending on Oracle Client library version |
Release 9.2 and later depending on Oracle Client library version |
Standalone connections (see Standalone Connections) |
Yes - must use keyword arguments |
Yes - must use keyword arguments |
Yes |
Connection Pooling - Heterogeneous and Homogeneous (see Connection pooling) |
Homogeneous only - must use keyword arguments |
Yes - must use keyword arguments |
Yes |
Connection Pool Connection Load Balancing (CLB) |
Yes |
Yes |
Yes |
Connection Pool Runtime Load Balancing (RLB) |
No |
Yes |
Yes |
Connection Pool draining |
Yes |
Yes |
Yes |
Connection Pool session state callback (see Session CallBacks for Setting Pooled Connection State) |
Yes - Python functions but not PL/SQL functions |
Yes |
Yes |
Connection pool session tagging (see Connection Tagging) |
No |
Yes |
Yes |
Password authentication |
Yes |
Yes |
Yes |
External authentication (see Connecting Using External Authentication) |
No |
Yes |
Yes |
Oracle Cloud Infrastructure (OCI) Identity and Access Management (IAM) Tokens (see Connecting Using OCI IAM Token-Based Authentication) |
Yes |
Yes |
Yes - in connection string with appropriate Oracle Client |
Open Authorization (OAuth 2.0) (see Connecting Using OAuth 2.0 Token-Based Authentication) |
Yes |
Yes |
Yes - in connection string with appropriate Oracle Client |
Kerberos and Radius authentication |
No |
Yes |
Yes |
Proxy connections (see Connecting Using Proxy Authentication) |
Yes |
Yes |
Yes |
Connection mode privileges (see Connection Authorization Modes) |
Yes |
Yes - only |
Yes - only |
Preliminary connections |
No |
Yes |
Yes |
Set the current schema using an attribute |
Yes |
Yes |
Yes |
Oracle Cloud Database connectivity (see Connecting to Oracle Cloud Autonomous Databases) |
Yes |
Yes |
Yes |
Real Application Clusters (RAC) |
Yes |
Yes |
Yes |
Oracle Sharded Databases (see Connecting to Sharded Databases) |
No |
Yes - No TIMESTAMP support |
Yes - No TIMESTAMP support |
Oracle Database Native Network Encryption (see Native Network Encryption) |
No |
Yes |
Yes |
Connection pinging API |
Yes |
Yes |
Yes |
Oracle Net Services |
Yes |
Yes |
Yes |
Oracle Net Services |
No - many values can be set at connection time |
Yes |
Yes |
Oracle Client library configuration file |
Not applicable |
Yes |
Yes |
Easy Connect Plus connection strings |
Yes - mostly supported. Unknown settings are ignored and not passed to Oracle Database. |
Yes |
Yes |
One-way TLS connections (see One-way TLS Connection to Oracle Autonomous Database) |
Yes |
Yes |
Yes |
Mutual TLS (mTLS) connections (see Mutual TLS (mTLS) Connection to Oracle Autonomous Database) |
Yes - needs a PEM format wallet (see Creating a PEM File for python-oracledb Thin Mode) |
Yes |
Yes |
Oracle Database Dedicated Servers, Shared Servers and Database Resident Connection Pooling (DRCP) |
Yes |
Yes |
Yes |
Multitenant Databases |
Yes |
Yes |
Yes |
CMAN and CMAN-TDM connectivity |
Yes |
Yes |
Yes |
Password changing (see |
Yes |
Yes |
Yes |
Statement break/reset (see |
Yes |
Yes |
Yes |
Edition Based Redefinition (EBR) (see Edition-Based Redefinition (EBR)) |
No - not at connect time. ALTER SESSION can be used. |
Yes |
Yes |
SQL execution (see Executing SQL) |
Yes - bind and fetch all types except BFILE and JSON |
Yes |
Yes |
PL/SQL execution (see Executing PL/SQL) |
Yes for scalar types. Yes for collection types using array interface. |
Yes |
Yes |
Simple Oracle Document Access (SODA) API (see SODA) |
No |
Yes |
Yes |
Bind variables for data binding (see Using Bind Variables) |
Yes |
Yes |
Yes |
Array DML binding for bulk DML and PL/SQL (see Executing Batch Statements and Bulk Loading) |
Yes |
Yes |
Yes |
SQL and PL/SQL type and collections (see Fetching Oracle Database Objects and Collections) |
Yes |
Yes |
Yes |
Query column metadata |
Yes |
Yes |
Yes |
Client character set support (see Character Sets and Globalization) |
UTF-8 |
UTF-8 |
Yes - can use Python encodings. Default in 8.0 is UTF-8 |
Oracle Globalization support |
No - All NLS environment variables are ignored. Use Python globalization support instead |
Yes - NLS environment variables are respected except character set in NLS_LANG |
Yes - NLS environment variables are respected except character set in NLS_LANG |
Row prefetching on first query execute.(see |
Yes |
Yes |
Yes |
Array fetching for queries (see |
Yes |
Yes |
Yes |
Statement caching (see Statement Caching) |
Yes - new driver also supports dropping from the cache |
Yes - new driver also supports dropping from the cache |
Yes |
Client Result Caching (CRC) (see Client Result Caching (CRC)) |
No |
Yes |
Yes |
Continuous Query Notification (CQN) (see Working with Continuous Query Notification (CQN)) |
No |
Yes |
Yes |
Advanced Queuing (AQ) (see Using Oracle Advanced Queuing (AQ)) |
No |
Yes - must use new API introduced in cx_Oracle 7.2 |
Yes |
Call timeouts (see |
Yes |
Yes |
Yes |
Scrollable cursors (see Scrollable Cursors) |
No |
Yes |
Yes |
Oracle Database startup and shutdown (see Starting and Stopping Oracle Database) |
No |
Yes |
Yes |
Transaction management (see Managing Transactions) |
Yes |
Yes |
Yes |
Events mode for notifications |
No |
Yes |
Yes |
Fast Application Notification (FAN) (see Fast Application Notification (FAN)) |
No |
Yes |
Yes |
In-band notifications |
Yes |
Yes |
Yes |
Transparent Application Failover (TAF) |
No |
Yes - no callback |
Yes - no callback |
Transaction Guard (TG) (see Transaction Guard) |
No |
Yes |
Yes |
Data Guard (DG) and Active Data Guard (ADG) |
Yes |
Yes |
Yes |
Application Continuity (AC) and Transparent Application Continuity (TAC) (see Application Continuity (AC)) |
No |
Yes |
Yes |
End-to-end monitoring and tracing attributes (see Tracing python-oracledb) |
Yes |
Yes |
Yes |
Automatic Diagnostic Repository (ADR) |
No |
Yes |
Yes |
Java Debug Wire Protocol for debugging PL/SQL (see Debugging PL/SQL with the Java Debug Wire Protocol) |
Yes |
Yes |
Yes |
Two-phase Commit (TPC) |
No |
Yes - improved support (see Using Two-Phase Commits (TPC)) |
Yes - limited support |
REF CURSORs and Nested Cursors |
Yes |
Yes |
Yes |
Pipelined tables |
Yes |
Yes |
Yes |
Implicit Result Sets |
Yes |
Yes |
Yes |
Application Contexts |
No |
Yes |
Yes |
Persistent and Temporary LOBs |
Yes |
Yes |
Yes |
LOB prefetching |
No |
No - does have LOB length prefetch |
No - does have LOB length prefetch |
LOB locator operations such as trim |
Yes |
Yes |
Yes |
CHAR, VARCHAR2, NUMBER, FLOAT, DATE, and LONG data types |
Yes |
Yes |
Yes |
BLOB and CLOB data types |
Yes |
Yes |
Yes |
BINARY_DOUBLE and BINARY_FLOAT data types |
Yes |
Yes |
Yes |
RAW and LONG RAW data types |
Yes |
Yes |
Yes |
INTERVAL DAY TO SECOND data type (see |
Yes |
Yes |
Yes |
INTERVAL YEAR TO MONTH data type (see |
No |
No |
No |
Oracle 12c JSON |
Yes |
Yes |
Yes |
Oracle 21c JSON data type (see |
No - can fetch with an output type handler, see Fetching JSON Differences |
Yes |
Yes |
ROWID, UROWID data types |
Yes |
Yes |
Yes |
TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE data types |
Yes |
Yes |
Yes |
NCHAR, NVARCHAR2, NCLOB data types |
Yes |
Yes |
Yes |
PL/SQL data types BOOLEAN, PLS_INTEGER and BINARY_INTEGER |
Yes |
Yes |
Yes |
XMLType data type (see Using XMLTYPE Data) |
Yes |
Yes - may need to fetch as CLOB |
Yes - may need to fetch as CLOB |
BFILE data type (see |
No |
Yes |
Yes |
23.1. Supported Oracle Database Data Types
The following table lists the Oracle Database types that are supported in the python-oracledb driver. See Oracle Database Types and PL/SQL Types. The python-oracledb constant shown is the common one. In some python-oracledb APIs you may use other types, for example when binding numeric values.
Oracle Database Type |
python-oracledb Constant Name |
Supported in python-oracledb |
Supported Python Types |
|---|---|---|---|
VARCHAR2 |
DB_TYPE_VARCHAR |
Yes |
bytes, str |
NVARCHAR2 |
DB_TYPE_NVARCHAR |
Yes |
bytes, str |
NUMBER, FLOAT |
DB_TYPE_NUMBER |
Yes |
bool, int, float, decimal.Decimal |
DATE |
DB_TYPE_DATE |
Yes |
datetime.date, datetime.datetime |
BOOLEAN (PL/SQL) |
DB_TYPE_BOOLEAN |
Yes |
ANY (converted to bool) |
BINARY_DOUBLE |
DB_TYPE_BINARY_DOUBLE |
Yes |
bool, int, float, decimal.Decimal |
BINARY_FLOAT |
DB_TYPE_BINARY_FLOAT |
Yes |
bool, int, float, decimal.Decimal |
TIMESTAMP |
DB_TYPE_TIMESTAMP |
Yes |
datetime.date, datetime.datetime |
TIMESTAMP WITH TIME ZONE |
DB_TYPE_TIMESTAMP_TZ |
Yes |
datetime.date, datetime.datetime |
TIMESTAMP WITH LOCAL TIME ZONE |
DB_TYPE_TIMESTAMP_LTZ |
Yes |
datetime.date, datetime.datetime |
INTERVAL YEAR TO MONTH |
DB_TYPE_INTERVAL_YM |
Not supported in python-oracledb |
cannot be set |
INTERVAL DAY TO SECOND |
DB_TYPE_INTERVAL_DS |
Yes |
datetime.timedelta |
RAW |
DB_TYPE_RAW |
Yes |
bytes, str |
LONG |
DB_TYPE_LONG |
Yes |
bytes, str |
LONG RAW |
DB_TYPE_LONG_RAW |
Yes |
bytes, str |
ROWID |
DB_TYPE_ROWID |
Yes |
bytes, str |
UROWID |
DB_TYPE_ROWID, DB_TYPE_UROWID (only supported in python-oracledb Thin mode) |
Yes. May show DB_TYPE_UROWID in metadata. See Query Metadata Differences. |
bytes, str |
CHAR |
DB_TYPE_CHAR |
Yes |
bytes, str |
BLOB |
DB_TYPE_BLOB |
Yes |
BLOB, bytes, str |
CLOB |
DB_TYPE_CLOB |
Yes |
CLOB, bytes, str |
NCHAR |
DB_TYPE_NCHAR |
Yes |
bytes, str |
NCLOB |
DB_TYPE_NCLOB |
Yes |
NCLOB, bytes, str |
BFILE |
DB_TYPE_BFILE |
Not supported in python-oracledb Thin mode |
cannot be set |
JSON |
DB_TYPE_JSON |
Yes. In python-oracledb Thin mode use an output type handler to fetch this Oracle Database 21c data type. See Using JSON Data. |
ANY (converted) |
REF CURSOR (PL/SQL OR nested cursor) |
DB_TYPE_CURSOR |
Yes |
CURSOR |
PLS_INTEGER |
DB_TYPE_BINARY_INTEGER |
Yes |
bool, int, float, decimal.Decimal |
BINARY_INTEGER |
DB_TYPE_BINARY_INTEGER |
Yes |
bool, int, float, decimal.Decimal |
REF |
n/a |
Not supported in python-oracledb Thin mode |
n/a |
XMLType |
n/a |
Not supported in python-oracledb. Use |
n/a |
User-defined types (object type, VARRAY, records, collections, SDO_*types) |
DB_TYPE_OBJECT |
Yes |
OBJECT of specific type |
Binding of contiguous PL/SQL Index-by BINARY_INTEGER arrays of string, number, and date are
supported in python-oracledb Thin and Thick modes. Use Cursor.arrayvar() to build
these arrays.