.. _exception:

.. currentmodule:: oracledb

*******************
Catching Exceptions
*******************

All exceptions raised by python-oracledb are inherited from
:attr:`oracledb.Error`.  See :ref:`exceptions` and :ref:`exchandling` for
information about attributes.

See :ref:`errorhandling` for differences between python-oracledb Thin and
:ref:`Thick <enablingthick>` 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:

.. code-block:: python

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

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

.. code-block:: python

    try:
        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)
    else:
        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

.. _errorhandling:

Error Handling in Thin and Thick Modes
======================================

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

The python-oracledb Thin mode generates error messages with the prefix "DPY".

In python-oracledb :ref:`Thick <enablingthick>` mode:

- The Oracle Call Interface (OCI) libraries generate error messages with the
  prefix "ORA".

- The `ODPI-C <https://oracle.github.io/odpi/>`__ code layer generates error
  messages with the prefix "DPI".

- The python-oracledb Thick mode 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, 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 across 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 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
      ORA-12514)

    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.
