Oracle PL/SQL Exception Handling: Examples to Raise User-defined Exception

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.

1. Predefined Exceptions
2. 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:
/

Code Explanation:

• Code line 2: Declaring the variable 'sample_exception' as EXCEPTION type.
• Code line 3: Declaring procedure nested_block.
• Code line 6: Printing the statement "Inside nested block".
• Code line 7: Printing the statement "Raising sample_exception from nested block."
• Code line 8: Raising the exception using 'RAISE sample_exception'.
• Code line 10: Exception handler for exception sample_exception in the nested block.
• Code line 11: Printing the statement 'Exception captured in nested block. Raising to main block'.
• Code line 12: Raising the exception to main block (propagating to the main block).
• Code line 15: Printing the statement "Inside the main block".
• Code line 16: Printing the statement "Calling nested block".
• Code line 17: Calling nested_block procedure.

• Code line 19: Exception handler for sample_exception in the main block.
• Code line 20: Printing the statement "Exception captured in the main block."

Important points to note in Exception

• In function, an exception should always either return value or raise the exception further. else Oracle will throw 'Function returned without a value' error at run-time.
• Transaction control statements can be given at exception handling block.
• SQLERRM and SQLCODE are the in-built functions that will give the exception message and code.
• If an exception is not handled then by default all the active transaction in that session will be rolled back.
• RAISE_APPLICATION_ERROR (-<error_code>, <error_message>) can be used instead of RAISE to raise the error with user code and message. Error code should be greater than 20000 and prefixed with '-'.

Summary

After this chapter. you should be able to work for the following aspects of Pl SQL exceptions

• Handling the exceptions
• Define an exception
• Raise the exception
• Exception propagation