PL/SQL Exceptions  «Prev  Next»

Lesson 7Functions and procedures for exception handling
ObjectiveDescribe the use of functions and procedures within error handling.

Functions | Procedures Exception Handling

Functions in exception handling

When an exception occurs, you can identify the associated error code or error message by using the following two functions:
Function Description
SQLCODE Returns the numeric value for the error code. You can assign this to a NUMBER variable. Here are the return values and theirdescriptions:
SQLCODE ValueDescription
0No exception encountered
1User-defined exception
100NO_DATA_FOUND exception
< 0Other Oracle server error number
SQLERRM Returns character data containing the error message.

View the code below for an example where the error number and error message are displayed on the screen when the delete for the PRODUCT table fails. 
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
DELETE FROM PRODUCT WHERE PRODUCT_ID = 22;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE (‘SQLCode:
‘ || TO_CHAR(v_error_code) || ‘SQL Error Message: ‘ || v_error_message);
END;

The RAISE_APPLICATION_ERROR procedure allows you to communicate a predefined exception interactively by returning a nonstandard error code and error message. With RAISE_APPLICATION_ERROR, you can communicate errors to your application and avoid exceptions going by unhandled.
RAISE_APPLICATION_ERROR
RAISE_APPLICATION_ERROR

PL/SQL Block to handle Exceptions

Now that you have learned all about exception handlers, you can build a PL/SQL block that requires you to handle exceptions.
In the following simulation, you will work with a preexisting PL/SQL block. The current PL/SQL block processes orders and updates inventory. Inventory includes pet supplies for animals. Customers can purchase any item individually. The shipping and handling fee for every transaction is 0.10% of the sale cost, and there is a 5% state tax.
The PL/SQL block currently does the following:
  1. Accepts the product ID
  2. Gets the base sale price from the product table (based on the product ID)
  3. Calculates the total sale amount as the sum of base sale price, tax amount, and shipping and handling costs
  4. Displays the product sales price on the screen

To make this code more robust, you need to add an error handling routine. You need to catch the exceptions for the following conditions:
  1. If there is no product with the product ID supplied by the end user, raise the appropriate error.
  2. If the base sale price is less than or equal to zero, raise an error.
  3. Trap all other database errors that may arise in the PL/SQL block.

Hint

Use the NO_DATA_FOUND implicit exception if there is no product with the product ID supplied by the end user.
Define a sales price exception if the base sale price is less than or equal to zero.
Use the OTHERS implicit exception for all other database errors that you need to trap.

1) Exception Handling 1 2) Exception Handling 2 3) Exception Handling 3 4) Exception Handling 4 5) Exception Handling 5 6) Exception Handling 6 7) Exception Handling 7 8) Exception Handling 8 9) Exception Handling 9 10) Exception Handling 10 11) Exception Handling 11 12) Exception Handling 12 13) Exception Handling 13 14) Exception Handling 14 15) Exception Handling 15

Process Flow

  1. Review the code so far. Notice all variables that have been declared. Next, declare sale_price_exception.
  2. Syntax error
  3. sale_price_exception EXCEPTION;
  4. Incorrect syntax. Type sale_price_exception EXCEPTION;. End your command by clicking ENTER.
Functions Procedures Exception Handling
The next lesson concludes this module.