What is Exception Handling in PL/SQL?
An exception occurs when the PL/SQL engine encounters an instruction which it cannot execute due to an error that occurs at run-time. These errors will not be captured at the time of compilation and hence these needed to handle only at the run-time.
For example, if PL/SQL engine receives an instruction to divide any number by ‘0’, then the PL/SQL engine will throw it as an exception. The exception is only raised at the run-time by the PL/SQL engine.
Exceptions will stop the program from executing further, so to avoid such condition, they need to be captured and handled separately. This process is called as Exception-Handling, in which the programmer handles the exception that can occur at the run time.
In this tutorial, you will learn the following topics-
- Exception-Handling Syntax
- Types of Exception
- Predefined Exceptions
- User-defined Exception
- PL/SQL Raise Exception
- Important points to note in Exception
Exception-Handling Syntax
Exceptions are handled at the block, level, i.e., once if any exception occurs in any block then the control will come out of execution part of that block. The exception will then be handled at the exception handling part of that block. After handling the exception, it is not possible to resend control back to the execution section of that block.
The below syntax explains how to catch and handle the exception.
BEGIN <execution block> . . EXCEPTION WHEN <exceptionl_name> THEN <Exception handling code for the “exception 1 _name’' > WHEN OTHERS THEN <Default exception handling code for all exceptions > END;
Syntax Explanation:
- In the above syntax, the exception-handling block contains series of WHEN condition to handle the exception.
- Each WHEN condition is followed by the exception name which is expected to be raised at the run time.
- When any exception is raised at runtime, then the PL/SQL engine will look in the exception handling part for that particular exception. It will start from the first ‘WHEN’ clause and, sequentially it will search.
- If it found the exception handling for the exception which has been raised, then it will execute that particular handling code part.
- If none of the ‘WHEN’ clause is present for the exception which has been raised, then PL/SQL engine will execute the ‘WHEN OTHERS’ part (if present). This is common for all the exception.
- After executing the exception, part control will go out of the current block.
- Only one exception part can be executed for a block at run-time. After executing it, the controller will skip the remaining exception handling part and will go out of the current block.
Note: WHEN OTHERS should always be at the last position of the sequence. The exception handling part present after WHEN OTHERS will never get executed as the control will exit from the block after executing the WHEN OTHERS.
Types of Exception
There are two types of Exceptions in Pl/SQL.
- Predefined Exceptions
- User-defined Exception
Predefined Exceptions
Oracle has predefined some common exception. These exceptions have a unique exception name and error number. These exceptions are already defined in the ‘STANDARD’ package in Oracle. In code, we can directly use these predefined exception name to handle them.
Below are the few predefined exceptions
| Exception | Error Code | Exception Reason |
|---|---|---|
| ACCESS_INTO_NULL | ORA-06530 | Assign a value to the attributes of uninitialized objects |
| CASE_NOT_FOUND | ORA-06592 | None of the ‘WHEN’ clause in CASE statement satisfied and no ‘ELSE’ clause is specified |
| COLLECTION_IS_NULL | ORA-06531 | Using collection methods (except EXISTS) or accessing collection attributes on a uninitialized collections |
| CURSOR_ALREADY_OPEN | ORA-06511 | Trying to open a cursor which is already opened |
| DUP_VAL_ON_INDEX | ORA-00001 | Storing a duplicate value in a database column that is a constrained by unique index |
| INVALID_CURSOR | ORA-01001 | Illegal cursor operations like closing an unopened cursor |
| INVALID_NUMBER | ORA-01722 | Conversion of character to a number failed due to invalid number character |
| NO_DATA_FOUND | ORA-01403 | When ‘SELECT’ statement that contains INTO clause fetches no rows. |
| ROW_MISMATCH | ORA-06504 | When cursor variable data type is incompatible with the actual cursor return type |
| SUBSCRIPT_BEYOND_COUNT | ORA-06533 | Referring collection by an index number that is larger than the collection size |
| SUBSCRIPT_OUTSIDE_LIMIT | ORA-06532 | Referring collection by an index number that is outside the legal range (eg: -1) |
| TOO_MANY_ROWS | ORA-01422 | When a ‘SELECT’ statement with INTO clause returns more than one row |
| VALUE_ERROR | ORA-06502 | Arithmetic or size constraint error (eg: assigning a value to a variable that is larger than the variable size) |
| ZERO_DIVIDE | ORA-01476 | Dividing a number by ‘0’ |
User-defined Exception
In Oracle, other than the above-predefined exceptions, the programmer can create their own exception and handle them. They can be created at a subprogram level in the declaration part. These exceptions are visible only in that subprogram. The exception that is defined in the package specification is public exception, and it is visible wherever the package is accessible. <
Syntax: At subprogram level
DECLARE <exception_name> EXCEPTION; BEGIN <Execution block> EXCEPTION WHEN <exception_name> THEN <Handler> END;
- In the above syntax, the variable ‘exception_name’ is defined as ‘EXCEPTION’ type.
- This can be used as in a similar way as a predefined exception.
Syntax:At Package Specification level
CREATE PACKAGE <package_name> IS <exception_name> EXCEPTION; . . END <package_name>;
- In the above syntax, the variable ‘exception_name’ is defined as ‘EXCEPTION’ type in the package specification of <package_name>.
- This can be used in the database wherever package ‘package_name’ can be called.
PL/SQL Raise Exception
All the predefined exceptions are raised implicitly whenever the error occurs. But the user-defined exceptions needs to be raised explicitly. This can be achieved using the keyword ‘RAISE’. This can be used in any of the ways mentioned below.
If ‘RAISE’ is used separately in the program, then it will propagate the already raised exception to the parent block. Only in exception block can be used as shown below.
CREATE [ PROCEDURE | FUNCTION ]
AS
BEGIN
<Execution block>
EXCEPTION
WHEN <exception_name> THEN
<Handler>
RAISE;
END;
Syntax Explanation:
- In the above syntax, the keyword RAISE is used in the exception handling block.
- Whenever program encounters exception “exception_name”, the exception is handled and will be completed normally
- But the keyword ‘RAISE’ in the exception handling part will propagate this particular exception to the parent program.
Note: While raising the exception to the parent block the exception that is getting raised should also be visible at parent block, else oracle will throw an error.
- We can use keyword ‘RAISE’ followed by the exception name to raise that particular user-defined/predefined exception. This can be used in both execution part and in exception handling part to raise the exception.
CREATE [ PROCEDURE | FUNCTION ] AS BEGIN <Execution block> RAISE <exception_name> EXCEPTION WHEN <exception_name> THEN <Handler> END;
Syntax Explanation:
- In the above syntax, the keyword RAISE is used in the execution part followed by exception “exception_name”.
- This will raise this particular exception at the time of execution, and this needs to be handled or raised further.
Example 1: In this example, we are going to see
- How to declare the exception
- How to raise the declared exception and
- How to propagate it to the main block
DECLARE Sample_exception EXCEPTION; PROCEDURE nested_block IS BEGIN Dbms_output.put_line(‘Inside nested block’); Dbms_output.put_line(‘Raising sample_exception from nested block’); RAISE sample_exception; EXCEPTION WHEN sample_exception THEN Dbms_output.put_line (‘Exception captured in nested block. Raising to main block’); RAISE, END; BEGIN Dbms_output.put_line(‘Inside main block’); Dbms_output.put_line(‘Calling nested block’); Nested_block; EXCEPTION WHEN sample_exception THEN Dbms_output.put_line (‘Exception captured in main block'); END: /
Track2Training 
You must be logged in to post a comment.