cx_Oracle

Common errors

• ModuleNotFoundError: No module named 'cx_Oracle'

  cause The `cx_Oracle` package is not installed in the Python environment being used, or the Python interpreter cannot find it due to incorrect environment setup or path issues.
  fix Install the package using pip: `pip install cx_Oracle`. If using a virtual environment, ensure it is activated. Verify the correct Python interpreter is selected in your IDE.

• cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library

  cause cx_Oracle requires the Oracle Instant Client libraries to be installed and accessible via the system's library search path (e.g., PATH on Windows, LD_LIBRARY_PATH on Linux/macOS), and there is a mismatch in bitness (32-bit vs. 64-bit) between Python/cx_Oracle and the Instant Client.
  fix Download and install the correct 64-bit (or 32-bit to match Python) Oracle Instant Client 'Basic' package from Oracle's website. Add the directory containing `oci.dll` (Windows) or `libclntsh.so` (Linux) to your system's PATH or LD_LIBRARY_PATH environment variable, or explicitly set the library directory in your Python code using `cx_Oracle.init_oracle_client(lib_dir="/path/to/instantclient")`.

• cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

  cause The Oracle client cannot find or resolve the database service name specified in the connection string, typically due to an incorrect or missing entry in the `tnsnames.ora` file, or the `TNS_ADMIN` environment variable not pointing to the directory containing this file.
  fix Ensure the `tnsnames.ora` file exists, contains the correct service entry, and is accessible. Set the `TNS_ADMIN` environment variable to the directory where `tnsnames.ora` is located. Verify the connection string details (host, port, service name) are accurate if not using TNS aliases directly.

• cx_Oracle.DatabaseError: ORA-01017: invalid username/password; logon denied

  cause The provided username or password for connecting to the Oracle database is incorrect, or there are authentication issues such as case sensitivity mismatches or expired passwords.
  fix Double-check the username and password for typos. Verify if the Oracle database has case-sensitive passwords enabled and adjust accordingly. Test credentials using a tool like SQL*Plus to confirm they work outside of Python.

• cx_Oracle.DatabaseError: ORA-00900: invalid SQL statement

  cause This error occurs when an SQL*Plus command (like `SHOW PARAMETER` or `EXECUTE` for PL/SQL blocks) is passed to `cursor.execute()`, which expects only valid SQL DML/DDL statements or PL/SQL anonymous blocks prefixed with `BEGIN...END;`.
  fix Rewrite SQL*Plus commands into their equivalent SQL or PL/SQL. For `SHOW PARAMETER`, query `V$PARAMETER`. For executing stored procedures or functions, use `cursor.callproc()` or wrap the call in a `BEGIN...END;` anonymous block for `cursor.execute()`.

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

  import cx_oracle

  correct:

  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.")