PROGRAM TO PERFORM THE USER DEFINED EXCEPTION WHICH CHECKS WHETHER THE NAME OF EMPLOYEES IS BLANK OR NOT DURING INSERTION IN ORACLE?

SQL> DECLARE

   2 ECODE EMP.EMPNO%TYPE;

   3 NAME EMP.ENAME%TYPE;

   4 ENAME_ERR EXCEPTION;

   5 BEGIN

   6 ECODE:= &ECODE;

   7 NAME:= '&NAME';

   8 IF NAME IS NULL THEN

   9 RAISE ENAME_ERR;

  10 END IF;

  11 INSERT INTO EMP(EMPNO,ENAME)VALUES(ECODE,  

       NAME);

  12 DBMS_OUTPUT.PUT_LINE('DATA ENTERED');

  13 EXCEPTION

  14 WHEN ENAME_ERR THEN

  15 DBMS_OUTPUT.PUT_LINE('NAME SHOULD NOT BE  

       BLANK');

  16 END;

  17 /

Enter value for ecode: 7499

old   6: ECODE:= &ECODE;

new   6: ECODE:= 7499;

Enter value for name:

old   7: NAME:= '&NAME';

new   7: NAME:= '';

NAME SHOULD NOT BE BLANK

PL/SQL procedure successfully completed.

SQL> @ BLANK.SQL;

Enter value for ecode: 502

old   6: ECODE:= &ECODE;

new   6: ECODE:= 502;

Enter value for name: GURPREET

old   7: NAME:= '&NAME';

new   7: NAME:= 'GURPREET';

DATA ENTERED

PL/SQL procedure successfully completed.