Error and Exception Handling In Oracle PL/SQL
In this article, I’ll demonstrate how your Oracle PL/SQL programs can use errors and exception handlers in Oracle and how to write exception handlers to handle exceptions. PL/SQL exception and how to write exception handler to handle exceptions.
What Is an Exception?
In PL/SQL, an Exception is a warning or error condition called an exception. Exceptions can be internally defined (by the run-time system) or user-defined. Errors can be classified as described in the below table. Exceptions are designed for run-time error handling, rather than compile-time error handling. Errors that occur during the compilation phase are detected by the PL/SQL engine and reported back to the user. The program cannot handle these, since the program has not yet run. There are two types of exceptions: user-defined and predefined.
|Error Type||Reported By||How Handled|
|Compile-time||PL/SQL compiler||Interactively: compiler reports errors, and you have to correct them.|
|Run-time||PL/SQL run-time engine||Programmatically: exceptions are raised and caught by exception handlers.|
User-Defined Exceptions: A user-defined exception is an error that the programmer defines. User-defined exceptions are declared in the declarative section of a PL/SQL block. Just like variables, exceptions have a type (EXCEPTION) and a scope. For example,
DECLARE e_duplicatePerson EXCEPTION;
Here, e_duplicatePerson is an identifier that will be visible until the end of this block and the scope of an exception is the same as the scope of any other variable or cursor in the same declarative section.
Predefined Exceptions: Oracle has predefined several exceptions that correspond to the most common Oracle errors. It is not necessary to declare them in the declarative section like a user-defined exception. Example – INVALID_CUSROR, ZERO_DIVIDE, INVALID_NUMBER, VALUE_ERROR.
When the error associated with an exception occurs, the exception is raised. User-defined exceptions are raised explicitly via the RAISE statement, while predefined exceptions are raised implicitly when their associated Oracle error occurs.
DECLARE e_duplicatePerson EXCEPTION; -- IDs for three authors v_person1 books.author1%TYPE; v_person2 books.author2%TYPE; BEGIN SELECT person1, person2 INTO v_person1, v_person2 FROM per_person_f WHERE title = 'Manager'; /* Ensure that there are no duplicates */ IF (v_person1 = v_person2) THEN RAISE e_duplicatePerson; END IF; END;
When an exception is raised, control passes to the exception section of the block. An exception handler contains the code that is executed when the error associated with the exception occurs, and the exception is raised. Here is the basic syntax of the exception-handling section:
BEGIN -- executable section ... -- exception-handling section EXCEPTION WHEN exception_name1 THEN -- exception_handler1 WHEN exception_name2 THEN -- exception_handler1 WHEN OTHERS THEN -- other_exception_handler END;
In this example exception_name1 and exception_name2 are exceptions. If the exception
exception_name1 occurred, the
exception_handler1 runs. If the exception
exception_name2 occurred, the
exception_handler2 executes. In case any other exception raises, then the
Examining the Error Stack: Although only one exception can be raised at a time, the actual error message text SQLCODE returns the current error code, and SQLERRM returns the current error message text. For a user-defined exception, SQLCODE returns 1, and SQLERRM returns “User-defined Exception”.
TOO_MANY_ROWS exception example
Consider the persons table has the following data:
DECLARE v_fname VARCHAR2 (15); BEGIN SELECT last_name INTO v_fname FROM persons WHERE last_name = 'User'; DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_fname); END; /
We know there are 3 persons with a last name as ‘User’ so the above code fails. Below is the error code:
Error report - ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 01422. 00000 - "exact fetch returns more than requested number of rows" *Cause: The number specified in exact fetch is less than the rows returned. *Action: Rewrite the query or change number of rows requested
You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example:
set serveroutput on; DECLARE v_fname VARCHAR2 (15); BEGIN SELECT last_name INTO v_fname FROM persons WHERE last_name = 'User'; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple rows. Consider using a cursor.'); END; /
This is how you can handle exceptions very easily in PL/SQL.
That’s all about this article.
Request you to subscribe to my YouTube channel for regular updates.