-- Here is a simple example to show error handling in Oracle PL/SQL.
-- By executing the anonymous PL/SQL code shown below we get 
-- ORA-01476: divisor is equal to zero

SET SERVEROUTPUT ON;

DECLARE
 v_ref1   NUMBER := 1;
 v_ref2   NUMBER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('v_ref1/v_ref2 = '||v_ref1/v_ref2);
END;
/

-- By introducing exception block, we can mask the error, very risky - NOT RECOMMENDED
-- It will show the output as 
-- "PL/SQL procedure successfully completed."
-- masking the true error.  The error has to be raised or handled

DECLARE
 v_ref1   NUMBER := 1;
 v_ref2   NUMBER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('v_ref1/v_ref2 = '||v_ref1/v_ref2);
EXCEPTION
 WHEN OTHERS THEN  NULL;   --NOT RECOMMENDED - TOO RISKY
END;
/

-- By using the DBMS_UTILITY options, the error and location (line number) are 
-- displayed.  It will show that error occured and with message 
-- "PL/SQL procedure successfully completed."
-- Obviously, zero divide has to be handled such that they are avoided 
-- by zero data validation, alternate processes flow etc.

DECLARE
 v_ref1   NUMBER := 1;
 v_ref2   NUMBER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('v_ref1/v_ref2 = '||v_ref1/v_ref2);
EXCEPTION
 WHEN OTHERS THEN 
   DBMS_OUTPUT.PUT(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
   DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_CALL_STACK);
END;
/

-- To fail the script or PL/SQL code, just use RAISE, which will abend 
-- the data management process.  Logging and monitoring can be added to 
-- manage error handling.

DECLARE
 v_ref1   NUMBER := 1;
 v_ref2   NUMBER := 0;

BEGIN
  DBMS_OUTPUT.PUT_LINE('v_ref1/v_ref2 = '||v_ref1/v_ref2);
EXCEPTION
 WHEN OTHERS THEN   RAISE;
END;
/