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 ;