Using Procedures - Exercise

Contact Us or call 1-877-932-8228
Using Procedures - Exercise

Using Procedures

Duration: 15 to 20 minutes.
  1. Create a procedure called "divide_by_zero".
  2. Declare two variables named x and y of type number. Assign x the value of one and y the value of zero.
  3. In the body of the procedure assign x the value of x divided by y. (This will result in a divide by zero error - expected for this exercise).
  4. Add a line that outputs We will not get here that demonstrates that the divide by zero exception raised in the previous line will bypass the following lines.
  5. Add an exception section to handle the divide by zero error.
  6. Add a line to the exception section that outputs "USER MESSAGE: Error in: program unit name line line number".
  7. Add a line that prints out the call stack at the point the error occurred.
  8. Compile, run, and test and debug the procedure.

Solution:

Stored-Procedures-and-Functions/Solutions/divide_by_zero_procedure_solution.sql
CREATE OR REPLACE PROCEDURE divide_by_zero
IS
 x NUMBER:=1;
 y NUMBER:=0;
BEGIN
   x:=x/y;
   DBMS_OUTPUT.put_line('We will not get here.');
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.put_line('USER MESSAGE: Error in: ' || $$PLSQL_UNIT
                         || ' line ' || $$PLSQL_LINE);
    DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
/

-- Test it. Only the exception messages should be displayed.
begin
  divide_by_zero;
end;
/

Code Explanation

Next