[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

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.

Exception name Description
cx_Oracle.InterfaceError cx_An exception to the problem when using the Oracle interface. As an example, cx_It occurs when the usage of Oralce API is incorrect.
cx_Oracle.DataError Occurs when there is a problem with the data content, such as division by zero or overflow of digits.
cx_Oracle.OperationalError ORA-DB internal errors like 600 and ORA-Occurs in the case of a communication error such as 3135.
cx_Oracle.IntegrityError Occurs when there are data integrity issues such as referential integrity constraint violations.
cx_Oracle.InternalError Occurs in the event of an internal error. ORA-Pre-defined internal errors such as 600 are cx_Oracle.Since it will be an OperationalError, it corresponds to an internal problem that does not result in these error codes. One example is when you access an invalid cursor.
cx_Oracle.ProgrammingError An exception to programming issues. As an example, it occurs when there is a problem with the SQL statement to be issued.
cx_Oracle.NotSupportedError Cx that does not exist_Occurs when you call an Oracle method.
cx_Oracle.Warning Although it is defined because it exists in the DB API, cx_It is practically not used by Oracle.

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.

Variable name Description
_Error.code Oracle Database error number
_Error.offset Error offset
_Error.message Error message
_Error.context Error context information
_Error.isrecoverable A bool type that is a recoverable error. To use this variable, Oracle Server/12 for both clients.Must be 1 or later. False is stored whenever the version condition is not met

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

Recommended Posts

[Introduction to cx_Oracle] (12th) DB Exception Handling
[Introduction to cx_Oracle] (17th) Date type handling
[Introduction to cx_Oracle] (5th) Handling of Japanese data
[Introduction to cx_Oracle] (8th) cx_Oracle 8.0 release
[Introduction to Udemy Python3 + Application] 65. Exception handling
[Introduction to cx_Oracle] (Part 7) Handling of bind variables
[Introduction to cx_Oracle] Overview of cx_Oracle
Series: Introduction to cx_Oracle Contents
[Introduction to cx_Oracle] (Part 6) DB and Python data type mapping
Exception handling
I tried to summarize Python exception handling
Introduction to MQTT (Introduction)
Introduction to Scrapy (1)
Introduction to Scrapy (3)
Introduction to Supervisor
Introduction to Tkinter 1: Introduction
[Introduction to cx_Oracle] (Part 3) Basics of Table Reference
Introduction to PyQt
Introduction to Scrapy (2)
Python exception handling
boto3 exception handling
[Linux] Introduction to Linux
[Introduction to cx_Oracle] (15th) Creating a result set in a format other than tuples
Introduction to Scrapy (4)
Introduction to discord.py (2)
Python exception handling
Introduction to discord.py
[Introduction to cx_Oracle] (Part 11) Basics of PL / SQL Execution
[Introduction to cx_Oracle] (Part 13) Connection using connection pool (client side)
Introduction to Web Scraping
Introduction to Nonparametric Bayes
Introduction to Python language
Introduction to TensorFlow-Image Recognition
Introduction to OpenCV (python)-(2)
Python, about exception handling
Introduction to PyQt4 Part 1
Introduction to Dependency Injection
Introduction to Private Chainer
Introduction to machine learning
Fizzbuzz with exception handling
[Introduction to cx_Oracle] (Part 4) Fetch and scroll of result set