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.

Table 23.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 Identity and Access Management (IAM) Tokens

No

Yes - in connection string with appropriate Oracle Client

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 AUTH_MODE_SYSDBA is supported in Thick mode

Yes

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

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 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, OBJECT, 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 (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 Statement and Bulk Loading)

Yes

Yes

Yes

SQL and PL/SQL type and collections (see Fetching Oracle Database Objects and Collections)

No

Yes

Yes

Query column metadata

Yes

Yes

Yes

Client character set support

UTF-8 - see Character Sets and Globalization

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

Yes

Yes

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

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

Yes

Yes

Yes

INTERVAL YEAR TO MONTH data type (see DB_TYPE_INTERVAL_YM)

No

No

No

Oracle 12c JSON

Yes

Yes

Yes

Oracle 21c JSON data type (see DB_TYPE_JSON)

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)

No

No

No

BFILE data type (see DB_TYPE_BFILE)

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.

Table 23.2 Oracle Database Data Types Supported

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

cannot be set

UROWID

DB_TYPE_ROWID

Yes. May show DB_TYPE_UROWID in metadata. See Query Metadata Differences.

cannot be set

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 xmltype.getclobval() to fetch.

n/a

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

DB_TYPE_OBJECT

Not supported in python-oracledb Thin mode

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.