Error handling in PLSQL – An Overview
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.
Error handling:
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:
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
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 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. In the following figure, you can see the execution flows in a PL / SQL block when an error occurs. 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 said upper block, obviously if it exists.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
