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

Table 26.1 Features Supported by python-oracledb and cx_Oracle 8.3

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 AUTH_MODE_SYSDBA is supported

Yes - only AUTH_MODE_SYSDBA is supported

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 tnsnames.ora file (see Optional Oracle Net Configuration Files)

Yes

Yes

Yes

Oracle Net Services sqlnet.ora file (see Optional Oracle Net Configuration Files)

No - many values can be set at connection time

Yes

Yes

Oracle Client library configuration file oraaccess.xml (see Optional Oracle Client 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

Secure External Password Store (SEPS) wallet (e.g. wallets created by mkstore)

No

Yes

Yes

Oracle Database Dedicated Servers, Shared Servers and Database Resident Connection Pooling (DRCP)

Yes

Yes

Yes

Oracle Database 23ai Implicit Connection Pooling with DRCP and PRCP (see Implicit Connection Pooling)

Yes

Yes

No

Multitenant Databases

Yes

Yes

Yes

CMAN and CMAN-TDM connectivity

Yes

Yes

Yes

Password changing (see Connection.changepassword())

Yes

Yes

Yes

Statement break/reset (see Connection.cancel())

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

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

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

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) (see Using Two-Phase Commits (TPC))

Yes

Yes - improved support

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

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

Table 26.2 Oracle Database Data Types Supported

Oracle Database Type

python-oracledb Constant Name

Notes

Supported Python Types

VARCHAR2

DB_TYPE_VARCHAR

No relevant notes

bytes, str

NVARCHAR2

DB_TYPE_NVARCHAR

No relevant notes

bytes, str

NUMBER, FLOAT

DB_TYPE_NUMBER

No relevant notes

bool, int, float, decimal.Decimal

DATE

DB_TYPE_DATE

No relevant notes

datetime.date, datetime.datetime

BOOLEAN (PL/SQL and Oracle Database 23ai SQL)

DB_TYPE_BOOLEAN

No relevant notes

Any type convertible to bool

BINARY_DOUBLE

DB_TYPE_BINARY_DOUBLE

No relevant notes

bool, int, float, decimal.Decimal

BINARY_FLOAT

DB_TYPE_BINARY_FLOAT

No relevant notes

bool, int, float, decimal.Decimal

TIMESTAMP

DB_TYPE_TIMESTAMP

No relevant notes

datetime.date, datetime.datetime

TIMESTAMP WITH TIME ZONE

DB_TYPE_TIMESTAMP_TZ

No relevant notes

datetime.date, datetime.datetime

TIMESTAMP WITH LOCAL TIME ZONE

DB_TYPE_TIMESTAMP_LTZ

No relevant notes

datetime.date, datetime.datetime

INTERVAL YEAR TO MONTH

DB_TYPE_INTERVAL_YM

No relevant notes

oracledb.IntervalYM

INTERVAL DAY TO SECOND

DB_TYPE_INTERVAL_DS

No relevant notes

datetime.timedelta

RAW

DB_TYPE_RAW

No relevant notes

bytes, str

LONG

DB_TYPE_LONG

No relevant notes

bytes, str

LONG RAW

DB_TYPE_LONG_RAW

No relevant notes

bytes, str

ROWID

DB_TYPE_ROWID

No relevant notes

bytes, str

UROWID

DB_TYPE_ROWID, DB_TYPE_UROWID (only supported in python-oracledb Thin mode)

May show DB_TYPE_UROWID in metadata. See Query Metadata Differences.

bytes, str

CHAR

DB_TYPE_CHAR

No relevant notes

bytes, str

BLOB

DB_TYPE_BLOB

No relevant notes

oracledb.LOB, bytes, str

CLOB

DB_TYPE_CLOB

No relevant notes

oracledb.LOB, bytes, str

NCHAR

DB_TYPE_NCHAR

No relevant notes

bytes, str

NCLOB

DB_TYPE_NCLOB, DB_TYPE_LONG_NVARCHAR (if fetching NCLOB as a string)

No relevant notes

oracledb.LOB, bytes, str

BFILE

DB_TYPE_BFILE

No relevant notes

Cannot be set

JSON

DB_TYPE_JSON

No relevant notes

Any type convertible to Oracle JSON

REF CURSOR (PL/SQL OR nested cursor)

DB_TYPE_CURSOR

No relevant notes

oracledb.Cursor

PLS_INTEGER

DB_TYPE_BINARY_INTEGER

No relevant notes

bool, int, float, decimal.Decimal

BINARY_INTEGER

DB_TYPE_BINARY_INTEGER

No relevant notes

bool, int, float, decimal.Decimal

REF

n/a

Not supported in python-oracledb Thin mode

n/a

XMLType

DB_TYPE_XMLTYPE

May need to use xmltype.getclobval() to fetch in python-oracledb Thick mode. See Using XMLTYPE Data

bytes, str

User-defined types (object type, VARRAY, records, collections, SDO_*types)

DB_TYPE_OBJECT

No relevant notes

OBJECT of specific type

VECTOR

DB_TYPE_VECTOR

No relevant notes

array.array

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.