Tuesday, August 11, 2020
dbametrix
More
    Home Sql Tutorial Error handling in PL/SQL - Overview

    Error handling in PL/SQL – Overview

    Detail explanation of error handling in PL/SQL with user-defined errors.

    Even if we were able to write an absolutely perfect PL / SQL program, it is highly likely that something could go wrong and errors occur during execution. The way our code responds to these errors often determines the difference between a properly working application and one that gives users and those in charge of its ongoing problems.

    This is the first article in a series that I will write about error handling in PLSQL. In them you can read about: the different types of exceptions that can occur; when, how and why exceptions are generated; how to define our own exceptions; how to handle exceptions when they occur; and how it is possible to inform users when a problem appears.

    PL / SQL Exceptions Overview:

    - Advertisement -
    dbametrix

    In PL / SQL there are three different categories of exceptions: internally defined, predefined, and user-defined.

    Internally defined exceptions: These are exceptions generated internally by the Oracle database. These types of exceptions always return an error code, but they do not have a name assigned unless we assign one to them in our PLSQL code.

    An example of this type of exception would be:

    ORA-00060: deadlock detected while waiting for a resource

    - Advertisement -
    dbametrix

    Predefined exceptions: These are internally defined exceptions that already have a name assigned by PL / SQL itself (see this article). Most of these types of exceptions are defined in a standard package provided by the Oracle database itself, a package where many of the common elements of the PLSQL programming language are defined and, within these common elements, the names of the most commonly occurring exceptions.

    An example of this type of exception would be:

    ORA-01413: no data found

    (name: NO_DATA_FOUND)

    User-defined exceptions: These are exceptions that the developer has declared in the DECLARE section of a program unit. These types of exceptions can be associated with an internally defined exception (giving it a specific name) or with a specific error in our PL / SQL program (see this article).

    All exceptions have a code and an associated error message. When we are handling an exception, the Oracle database provides specific functions to retrieve those values:

    •     SQLCODE: Returns the error code, basically a number that identifies the type of error. This function cannot be used within an SQL statement.
    •     SQLERRM: Returns the error message. These messages typically contain information about the name of the column or object that is associated with the error. This function cannot be used inside an SQL statement either.
    •     DBMS_UTILITY.FORMAT_ERROR_STACK: Same as SQLERRM but it can be included in an SQL statement.
    •     DBMS_UTILITY.FORMAT_ERROR_BACKTRACE: Returns the line on which the error occurs. This feature was first added in Version 10g Release 2 of the Oracle databases and is helpful in identifying the cause of the error.
    •     DBMS_UTILITY.FORMAT_CALL_STACK: Returns the sequence of all procedure and function calls until the error occurred. It answers the question “How did the PLSQL code get here?”, Showing the path followed in our code until the moment we called this function.

    We have already seen in some articles that a PL / SQL block has three different sections: DECLARE (declaration of cursors and variables), BEGIN (executable code) and EXCEPTION (exception handling). When an exception occurs in the BEGIN section, none of the remaining statements to execute in that section are executed. Rather, control passes to the corresponding EXCEPTION section.

    The good thing about this operation is that the handling of all the exceptions related to the same PLSQL block is concentrated in a single section, making it easier for developers to understand and maintain the logic of error handling. The error generation process and structure of the EXCEPTION section will be described in more depth in subsequent articles.
    As an advance, I will indicate that the EXCEPTION section must include WHEN clauses to catch the errors that we are interested in handling, the code inside the WHEN will normally take information about the generated error and put it in the program log. Later, the program can be continued or stopped, generating another exception.

    If the EXCEPTION section does not catch the error or the section simply does not exist, the error will propagate out of the block to the upper block and the error will be handled in the EXCEPTION section of the said upper block, obviously, if it exists.

    1+
    - Advertisement -
    dbametrix
    - Advertisment -
    dbametrix

    Most Popular

    Oracle Tuning Basics Part-2

    This is the 2nd part of Oracle database tuning basics including depth of every aspects.

    Oracle Tuning Basics Part-1

    The article explains Oracle database performance tuning basics details.

    When you finding new job

    The article guides you 10 things to remember when you are searching a new job to improve yourself including skills and better growth for every DBA.

    Recover database from dump File

    Detail explanation and step by step guide for how to recover database using import dump file manually.

    Recent Comments