22. Catching Exceptions

All exceptions raised by python-oracledb are inherited from oracledb.Error. See Oracledb Exceptions and Oracledb._Error Objects for information about attributes.

See Error Handling in Thin and Thick Modes for differences between the python-oracledb Thin and Thick modes.

Applications can catch exceptions as needed. For example, when trying to add a customer that already exists in the database, the following could be used to catch the exception:

    cursor.execute("insert into customer values (101, 'Customer A')")
except oracledb.IntegrityError:
    print("Customer ID already exists")
    print("Customer added")

If information about the exception needs to be processed instead, the following code can be used:

    cursor.execute("insert into customer values (101, 'Customer A')")
except oracledb.IntegrityError as e:
    error_obj, = e.args
    print("Customer ID already exists")
    print("Error Code:", error_obj.code)
    print("Error Full Code:", error_obj.full_code)
    print("Error Message:", error_obj.message)
    print("Customer added")

This will print output like:

Customer ID already exists
Error Code: 1
Error Full Code: ORA-00001
Error Message: ORA-00001: unique constraint (CJ.PK) violated

22.1. Error Handling in Thin and Thick Modes

The Thin and Thick modes of python-oracledb return some errors differently.

The python-oracledb Thin mode code generates error messages with the prefix “DPY”.

In python-oracledb Thick mode:

  • The Oracle Call Interface (OCI) libraries generate error messages with the prefix “ORA”.

  • The ODPI-C code layer generates error messages with the prefix “DPI”.

  • The python-oracledb Thick mode code generates error messages with the prefix “DPY”.

Errors generated by the Oracle Database itself commonly have the error prefix “ORA”.

Some differences between python-oracledb Thin and Thick mode errors are shown in the examples below:

  • Binding: When binding is incorrect, the python-oracledb Thick mode may generate an Oracle Client library error such as:

    ORA-01008: not all variables bound

    In contrast, the python-oracledb Thin mode might generate:

    DPY-4010: a bind variable replacement value for placeholder ":1" was not provided
  • Connection messages: The python-oracledb Thin mode connection and networking is handled by Python itself. Some errors portable accross operating systems and Python versions have DPY-prefixed errors displayed by python-oracledb. Other messages are returned directly from Python and may vary accordingly. The traditional Oracle connection errors with prefix “ORA” are not shown. For example, the scenarios detailed below show how the connection and network error messages might differ between the python-oracledb Thin and Thick modes.

    • Scenario 1: The given host does not have a database listener running.

      python-oracledb Thin mode Error:

      DPY-6005: cannot connect to database. Connection failed with "[Errno 61]
      Connection refused"

      python-oracledb Thick mode Error:

      ORA-12541: TNS:no listener
    • Scenario 2: The requested connection alias was not found in the tnsnames.ora file.

      python-oracledb Thin mode Error:

      DPY-4000: cannot connect to database. Unable to find "{name}" in {file_name}

      python-oracledb Thick mode Error:

      ORA-12154: TNS:could not resolve the connect identifier specified
    • Scenario 3: The Oracle Database listener does not know of the requested service name.

      python-oracledb Thin mode Error:

      DPY-6001: cannot connect to database.  Service "{service_name}" is not
      registered with the listener at host "{host}" port {port}.  (Similar to

      python-oracledb Thick mode Error:

      ORA-12514: TNS:listener does not currently know of service requested in
      connect descriptor
  • Connection Pooling: The python-oracledb Thin mode pool is not based on the Oracle Call Interface (OCI) Session Pool and has its own DPY messages.