Exception in PL/SQL
- An error occurred during the execution of program is called exception in PL/SQL.
- PL/SQL provides the facility to catch errors by declaring conditions in exception block in the program and necessary action to be taken to rectify the error.
- Exception can be user defined (these are logical error defined by user) or internally defined.
For example: The division by zero error.
- Internal exceptions are raised automatically by the runtime system.
- User defined exception should be raised explicitly by RAISE statements.
<define exception handling here >
WHEN exception1 THEN
WHEN exception2 THEN
WHEN exception3 THEN
WHEN others THEN
Advantages of PL/SQL Exceptions
Example: Illustration of exception handling.
- It is possible to handle potential errors from many statements by using a single exception handler.
- If user needs to check for error at every point, it is solved by adding an exception handler to PL/SQL block in the program.
- To check error at a specific spot is possible by enclosing a single statement or a group of statements inside its own exception handler.
- Isolating error handling method makes the rest of the program easier to read and understand.
Consider the following table titled 'Employee'.
Write a PL/SQL program to give message that no such a employee is present in a table.
c_id employee.id%type := 9;
SELECT name, designation INTO c_name, c_addr
WHERE id = c_id;
dbms_output.put_line ('Name: '| | c_name);
dbms_output.put_line ('Designation: ' | | c_addr);
WHEN no_data_found THEN
dbms_output.put_line('No such Employee exist!');
WHEN others THEN
No such Employee exist!
- In the above example, the given input value for employee id= 9, which is not present in the database.
- If we provide input value for id=3, the output will be:
- The Oracle engine provides a procedure RAISE_APPLICATION_ERROR that allows user to issue user-defined error message.
- When an error makes it undesirable or impossible to finish processing, the PL/SQL block and subprogram raise an error.
- User can write raise statements for a given exception anywhere within the scope of that exception.
is a negative integer in the range -2000 to -20999 & Message
is a character string upto 2048 bytes in length.
Example: Raising exception by using RAISE statement.
number_on_hand NUMBER := 0;
IF number_on_hand < 1 THEN
RAISE out_of_stock; -- raise an exception that we defined
WHEN out_of_stock THEN
-- handle the error
dbms_output.put_line('Encountered out-of-stock error.');
Encountered out-of-stock error.