Oracle Tips: Handling PL/SQL Errors

Run-time errors arise from design faults, coding mistakes, hardware failures, and many other sources. Although you cannot anticipate all possible errors, you can plan to handle certain kinds of errors meaningful to your PL/SQL program.

With many programming languages, unless you disable error checking, a run-time error such as stack overflow or division by zero stops normal processing and returns control to the operating system. With PL/SQL, a mechanism called exception handling lets you “bulletproof” your program so that it can continue operating in the presence of errors.

 
--Raise error in update and insert
IF SQL%ROWCOUNT  =0 THEN
  OUT_POST_ID   := -1;
  OUT_ERROR_MSG := 'failed';
  RAISE_APPLICATION_ERROR(-20101, 'No records inserted');
ELSE
  OUT_POST_ID   := L_max_postID;
  OUT_ERROR_MSG := 'success';
END IF;
 
--Raise error in select
IF SQL%NOTFOUND THEN
  RAISE_APPLICATION_ERROR (-20201, ' Record  does not exist');
END IF;
 
--Raise error in end of procedure
EXCEPTION
-- more code here
WHEN OTHERS THEN
  OUT_postID  := -1;
  OUT_ERROR_MSG := 'ERROR: '||SQLCODE||' '||SUBSTR(SQLERRM,1,1000);
  ROLLBACK;
  RAISE;
END ;

via: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS007