ERROR CODE			Errors Handling Visit Our Blog

Oracle Exception Handling

Oracle Predefined Exceptions, User Defined Exception, OTHERS errors handling

DECLARE
v_ErrorNumber NUMBER;
v_ErrorMessage VARCHAR2(500);
e_UserDefined EXCEPTION;
v_user_test number :=2;
BEGIN
/* PLSQL Code */
If v_user_test > 1 then
RAISE e_UserDefined;
End If;
EXCEPTION
--Oracle Predefined Exceptions
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Select Into Statement No rows returned.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('Select Into Statement Return More Than One Row');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('Program attempts to divide a number by zero.');
--User Defined Exception
WHEN e_UserDefined THEN
DBMS_OUTPUT.PUT_LINE ('User Defined Exception XYZ');
--Handle all others errors
WHEN OTHERS THEN
v_ErrorNumber := SQLCODE;
v_ErrorMessage := SQLERRM;
--Print error information
DBMS_OUTPUT.PUT_LINE('Error Number '||v_ErrorNumber||' Message '||v_ErrorMessage);
/*
--or/and insert into Error Log Table
Insert Into
ERROR_LOG
(ERROR_CODE,MESSAGE,DATE,USER_NAME)
values
(
v_ErrorNumber,
v_ErrorMessage,
sysdate, --Current Date/time
USER --User executing program unit
);
*/
END;