Friday, 21 June 2013

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.

No comments:

Post a Comment