PL/SQL: Exceptional Handling
An exception is an error condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block in the program and an appropriate action is taken against the error condition. There are two types of exceptions:
- System-defined exceptions
- User-defined exceptions
Syntax for Exception Handling
The general syntax for exception handling is as follows. Here you can list down as many exceptions as you can handle. The default exception will be handled using WHEN others THEN −
<exception handling goes here >
WHEN exception1 THEN
WHEN exception2 THEN
WHEN exception3 THEN
WHEN others THEN
Let us write a code to illustrate the concept.
c_id customers.id%type := 8;
SELECT name, address INTO c_name, c_addr
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE (‘Name: ‘|| c_name);
DBMS_OUTPUT.PUT_LINE (‘Address: ‘ || c_addr);
WHEN no_data_found THEN
dbms_output.put_line(‘No such customer!’);
WHEN others THEN
When the above code is executed at the SQL prompt, it produces the following result −
No such customer!
PL/SQL procedure successfully completed.
The above program displays the name and address of a customer whose ID is given. Since there is no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.
Exceptions are raised by the database server automatically whenever there is any internal database error, but exceptions can be raised explicitly by the programmer by using the command RAISE. Following is the simple syntax for raising an exception:
IF condition THEN
WHEN exception_name THEN
You can use the above syntax in raising the Oracle standard exception or any user-defined exception. In the next section, we will give you an example on raising a user-defined exception. You can raise the Oracle standard exceptions in a similar way.