cx_Oracle

Warnings

• breaking cx_Oracle has been officially obsoleted by `python-oracledb`. New projects are strongly encouraged to use `python-oracledb` for better performance, easier installation (thin mode by default, no Oracle Client libraries needed), and continued feature development.
  Fix: For new projects, `pip install oracledb` and use `import oracledb`. For existing cx_Oracle applications, refer to the `python-oracledb` migration guide.
• breaking Starting with cx_Oracle 8.0, Python 2.x is no longer supported. cx_Oracle 8.x and later only supports Python 3 (specifically 3.6-3.10 for version 8.3.0).
  Fix: Ensure your environment uses Python 3.6 or a later supported version.
• breaking The default encoding for all character data changed to UTF-8 in cx_Oracle 8.0. While this aligns with Python 3 best practices, applications relying on older default encodings might see unexpected behavior if not explicitly handled.
  Fix: Explicitly specify `encoding='UTF-8'` in `cx_Oracle.connect()` or `SessionPool()` calls if not already doing so, or if you were relying on a different default. Review character handling in your application.
• gotcha cx_Oracle requires Oracle Client libraries to be installed separately on the system. If these libraries are not found in the system's search path (e.g., `PATH` on Windows, `LD_LIBRARY_PATH` on Linux), you will encounter errors like `DPI-1047: Cannot locate a 64-bit Oracle Client library`.
  Fix: Download and install Oracle Instant Client for your operating system and ensure its directory is either in your system's `PATH`/`LD_LIBRARY_PATH` or explicitly provided to `cx_Oracle.init_oracle_client()`.
• gotcha Since cx_Oracle 8.0, the recommended and preferred method for locating Oracle Client libraries is to call `cx_Oracle.init_oracle_client()` at the start of your application, passing the path to the client libraries. This is especially important for macOS and is an alternative to setting system environment variables on Windows.
  Fix: Use `cx_Oracle.init_oracle_client(lib_dir="/path/to/instant/client")` in your Python code before establishing any connections. This avoids potential conflicts with system-wide environment variables.
• gotcha When installing cx_Oracle via pip on Windows, you might encounter issues if Python and the Oracle Client libraries are not both 32-bit or both 64-bit. Mismatched architectures will lead to loading errors.
  Fix: Ensure that your Python installation and the Oracle Client libraries (e.g., Instant Client) are both either 32-bit or 64-bit.

Install

• pip install cx_Oracle Install cx_Oracle

Imports

• cx_Oracle

  import cx_Oracle

  The module name uses a capital 'O' for Oracle. Using 'cx_oracle' (lowercase 'o') will result in a `ModuleNotFoundError`.

Quickstart

This quickstart demonstrates how to establish a connection to an Oracle database, execute a simple query, and fetch results using `cx_Oracle`. It includes `cx_Oracle.init_oracle_client()` for explicit Oracle Client library path configuration and uses environment variables for sensitive connection details.

import cx_Oracle
import os

# Set the path to the Oracle Instant Client libraries
# This is recommended for programmatically setting the library path since cx_Oracle 8.0
# On Windows, example: r"C:\oracle\instantclient_19_11"
# On Linux/macOS, example: r"/opt/oracle/instantclient_19_11"
# Replace with your actual Instant Client directory or set ORACLE_CLIENT_LIB_DIR env var
oracle_client_lib_dir = os.environ.get('ORACLE_CLIENT_LIB_DIR', '')
if oracle_client_lib_dir:
    try:
        cx_Oracle.init_oracle_client(lib_dir=oracle_client_lib_dir)
        print(f"Initialized Oracle Client from: {oracle_client_lib_dir}")
    except cx_Oracle.Error as e:
        print(f"Warning: Could not initialize Oracle Client from {oracle_client_lib_dir}. Error: {e}")
        print("Ensure Oracle Client libraries are correctly installed and path is valid.")

# Connection details (use environment variables for security in production)
username = os.environ.get('ORACLE_DB_USER', 'hr')
password = os.environ.get('ORACLE_DB_PASSWORD', 'welcome')
# Example DSN: "hostname:port/servicename" or "localhost:1521/XEPDB1"
dsn = os.environ.get('ORACLE_DB_DSN', 'localhost:1521/XEPDB1') 

try:
    # Establish a connection to the Oracle Database
    connection = cx_Oracle.connect(username, password, dsn)
    print("Successfully connected to Oracle Database.")

    # Create a cursor object
    cursor = connection.cursor()

    # Execute a simple SQL query
    cursor.execute("SELECT SYSDATE FROM DUAL")

    # Fetch and print the results
    for row in cursor:
        print(f"Current Oracle DB Date: {row[0]}")

    # Close the cursor and connection
    cursor.close()
    connection.close()
    print("Connection closed.")

except cx_Oracle.Error as e:
    error_obj, = e.args
    print(f"Oracle Database Error: Code {error_obj.code} - {error_obj.message}")
    print("Please check your connection details and Oracle Client setup.")