PL/SQL Exceptions  «Prev  Next»

Implicit User Defined Exceptions - Exercise

Learning Exceptions

Objective: Build a PL/SQL block that handles exceptions.
Rearrange the steps in this process.

Instructions

Below are seven steps required to build a PL/SQL block:
  1. Declare a variable v_order_id.
  2. Declare a variable v_amount and assign it the value 100.
  3. Increment the value of v_order_id by 1.
  4. Insert a record within the order table.
  5. Commit the transaction.
  6. Add error handling code to check that order_id is not duplicated within the order table .
  7. Add error handling code to check for any other errors.

These steps are presented out of order. Place the steps into the correct order, with the first step at the top of the list.
When you think you have the steps in the correct order, click Submit. You will be told whether the order is correct.
When you are ready for an explanation, click the Submit button.

Exercise scoring

You will receive 13 points for this exercise. The exercise is auto-scored; when you have completed the exercise, click the Submit button to receive full credit.

  • Declare a variable v_order_id.
  • Declare a variable v_amount and assign it the value 100.
  • Increment the value of v_order_id by 1.
  • Insert a record within the order table.
  • Commit the transaction.
  • Add error handling code to check that order_id is not duplicated within the order table .
  • Add error handling code to check for any other errors.
    • DECLARE
v_order_id NUMBER;
v_amount NUMBER(10,2) := 100;
BEGIN
v_order_id := v_order_id + 1;
INSERT INTO order(order_id, order_value)
VALUES (v_order_id, v_amount);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE ('Cannot insert record into the
Order table as order_id already exists.'); 
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Cannot continue processing
as error encountered.');
END;