hi,
I have written this procedure to understand the concept of exception but may be i am not getting correctly or missing something: as in this case my m_firstname is null then it should raise
RAISE_APPLICATION_ERROR(-20000, 'NUMBER TOO LARGE');
while it goes on 'no_data_found' error.
CREATE OR REPLACE PACKAGE BODY PKG_EXCEPTION IS
PROCEDURE PR_EXCEPTION_EXT (V_EMP_ID IN INTEGER,
V_FIRSTNAME IN VARCHAR2) IS
E_DUPLICATE EXCEPTION;
M_FIRSTNAME VARCHAR2(250);
BEGIN
SELECT FIRSTNAME, P_ID
INTO M_FIRSTNAME,M_P_ID
FROM EMP
WHERE P_ID= V_EMP_ID;
if M_FIRSTNAME IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'NUMBER TOO LARGE');
ELSIF
M_FIRSTNAME = V_FIRSTNAME THEN
DBMS_OUTPUT.PUT_LINE('RAISE EXCEPTION');
RAISE E_DUPLICATE;
ELSE
UPDATE EMP SET
FIRSTNAME = V_FIRSTNAME
WHERE P_ID = V_EMP_ID;
DBMS_OUTPUT.PUT_LINE('RECORD INSERTED');
--COMMIT;
END IF;
EXCEPTION
WHEN E_DUPLICATE THEN
DBMS_OUTPUT.PUT_LINE('DUPLICATE');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE:='||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLCODE:='||SQLERRM);
END PR_EXCEPTION_EXT;
END PKG_EXCEPTION;
/
EXEC PKG_EXCEPTION.PR_EXCEPTION_EXT (50001,'ABIDA') ;