EXAMPLE ON PRAGMA EXCEPTION_INIT BASED ON FOREIGN KEY CONSTRAINT

EXAMPLE 1;

DECLARE
CHILD_FOUND EXCEPTION;
BEGIN
DELETE FROM DEPT WHERE DEPTNO=&DEPTNO;
EXCEPTION
WHEN CHILD_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SORRY WE CANNOT DELETE THIS RECORD FROM DEPT TABLE BECAUSE SOME EMPLOYEES ARE WORKING IN THE DEPTNO');
END;

EXAMPLE 2:

DECLARE
PARENT_NOT_FOUND    EXCEPTION;
PRAGMA EXCEPTION_INIT(PARENT_NOT_FOUND,-02291);
BEGIN
INSERT INTO EMP(ENAME,SAL,DEPTNO) VALUES(&EMPNO,'&ENAME',&SAL,&DEPTNO);
EXCEPTION
WHEN PARENT_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SORRY WE CANNOT INSERT THIS RECORD INTO THIS TABLE  BECAUSE NO SUCH DEPT EXISTED IN THE COMPANY');
END;