[PYTHON] [Introduction to cx_Oracle] (12th) DB Exception Handling

Serial table of contents

Series: Introduction to cx_Oracle Table of Contents

Verification environment

--Using Oracle Cloud

• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

cx_Oracle.DatabaseError The exception cx_Oracle.DatabaseError is an exception to the problem that occurred when using basic cx_Oracle. cx_Oracle.DatabaseError itself is a subclass of cx_Oracle.Error, which is a subclass of the Python standard Error. There are many other cx_Oracle exceptions, but they are defined as subclasses of one of these exceptions. Use cx_Oracle.DatabaseError if you want to handle all cx_Oracle exceptions, regardless of basics or content.

sample12a.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_aaa"
SQL = """
        select object_id, owner, object_name, object_type
          from all_objects
         order by object_id
         fetch first 5 rows only
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        cursor = connection.cursor()
        cursor.execute(SQL)
        for row in cursor:
                print(row)
        cursor.close()

$ python sample12a.py
Traceback (most recent call last):
  File "sample12a.py", line 13, in <module>
    with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve the connect identifier specified

In sample12a.py, a TNS connector that does not exist in the contents of the DESTINATION variable on the 4th line is specified. Therefore, if you execute it, a connection error will always occur. As in this sample, if no special error handling is performed in coding, an exception of cx_Oracle.DatabaseError will occur. The content of the exception message is the Oracle Database error number (ORA-xxxxx, "ORA-12154" in the execution example) corresponding to the error content and the error message corresponding to the error number ("TNS: could not resolve the connect" in the execution example. identifier specified "). The error message is in English because the environment variable NLS_LANG was not specified when running this sample. If you want to receive Japanese error messages, set NLS_LANG.

List of exceptions

In addition to cx_Oracle.Error and cx_Oracle.DatabaseError, exceptions according to individual events and exceptions specified by the DB API are defined.

Variables that can be handled by exception handling

In exception handling, it is possible to refer to the following read-only variable information. These are collectively included in the args tuple.

As an example, the above variables are used outside the example as shown below. In this sample, PL / SQL's user-defined exception function is used to intentionally generate the ORA-600 that everyone loves (???).

sample12b.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
declare
  e600 exception;
  pragma exception_init(e600, -600);
begin
  raise e600;
end;
"""
try:
    connection = cx_Oracle.connect(USERID, PASSWORD, DESTINATION)
    cursor = connection.cursor()
    cursor.execute(SQL)
except cx_Oracle.OperationalError as ex:
    error, = ex.args
    print("An error has occurred. Please contact the administrator with the error code and message.")
    print("Error code: ", error.code)
    print("Error message: ", error.message)
finally:
    cursor.close()
    connection.close()

$ python sample12b.py
An error has occurred. Please contact the administrator with the error code and message.
Error code:  600
Error message:  ORA-00600:Internal error code,argument: [600], [], [], [], [], [], [], [], [], [], [], []
ORA-06512:Line 6