25. 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 |
Lightweight Directory Access Protocol (LDAP) connections |
No |
Yes |
Yes |
Proxy connections (see Connecting Using Proxy Authentication) |
Yes |
Yes |
Yes |
Socket Secure (SOCKS) Proxy connections |
No |
No |
No |
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 (NNE) (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 |
Oracle Database 23c Implicit Connection Pooling with DRCP and PRCP (see Implicit Connection Pooling with DRCP and PRCP) |
Yes |
Yes |
No |
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 - unless the row contains LOBs or similar types |
Yes - unless the row contains LOBs or similar types |
Yes - unless the row contains LOBs or similar types |
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 |
Concurrent programming with asyncio (see Concurrent Programming with asyncio) |
Yes |
No |
No |
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 length prefetching |
Yes |
Yes |
Yes |
LOB locator operations such as trim |
Yes |
Yes |
Yes |
25.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 |
Notes |
Supported Python Types |
---|---|---|---|
VARCHAR2 |
bytes, str |
||
NVARCHAR2 |
bytes, str |
||
NUMBER, FLOAT |
bool, int, float, decimal.Decimal |
||
DATE |
datetime.date, datetime.datetime |
||
BOOLEAN (PL/SQL and Oracle Database 23c SQL) |
Any type convertible to bool |
||
BINARY_DOUBLE |
bool, int, float, decimal.Decimal |
||
BINARY_FLOAT |
bool, int, float, decimal.Decimal |
||
TIMESTAMP |
datetime.date, datetime.datetime |
||
TIMESTAMP WITH TIME ZONE |
datetime.date, datetime.datetime |
||
TIMESTAMP WITH LOCAL TIME ZONE |
datetime.date, datetime.datetime |
||
INTERVAL YEAR TO MONTH |
Not supported in python-oracledb. |
Cannot be set |
|
INTERVAL DAY TO SECOND |
datetime.timedelta |
||
RAW |
bytes, str |
||
LONG |
bytes, str |
||
LONG RAW |
bytes, str |
||
ROWID |
bytes, str |
||
UROWID |
|
May show |
bytes, str |
CHAR |
bytes, str |
||
BLOB |
oracledb.LOB, bytes, str |
||
CLOB |
oracledb.LOB, bytes, str |
||
NCHAR |
bytes, str |
||
NCLOB |
|
oracledb.LOB, bytes, str |
|
BFILE |
Not supported in python-oracledb Thin mode. |
Cannot be set |
|
JSON |
Any type convertible to Oracle JSON |
||
REF CURSOR (PL/SQL OR nested cursor) |
|||
PLS_INTEGER |
bool, int, float, decimal.Decimal |
||
BINARY_INTEGER |
bool, int, float, decimal.Decimal |
||
REF |
n/a |
Not supported in python-oracledb Thin mode |
n/a |
XMLType |
May need to use |
bytes, str |
|
User-defined types (object type, VARRAY, records, collections, SDO_*types) |
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.