PL/SQL Exception

EXCEPTION

This is the third part of the PL/SQL program. (Other two are Declaration and Execution).

The exception starts with exception keyword and contains exception handlers to handle errors that occur during processing. The error handling code in the exception section is executed only when an error occurs.

·         NO_DATA_FOUND -> When no rows returned by a select statement.

·         Too_Many_Rows -> when more than one row returned by a select statement.

·         Value_error -> Size contraint error.

·         Zero_divide-> attempt to divide by zero.

·         dup_val_on_index

------------------------------------------------------------------------------

--Eg. for "no_data_found" Exception

declare

     e emp %rowtype;

begin

     select * into e from emp where empno=&empno;

     dbms_output.put_line(e.ename);

Exception

     when no_data_found then

     dbms_output.put_line('no rows selected. invalid employee no.');

end;

/

------------------------------------------------------------------------------

-Eg. for "zero_divide" Exception

declare

     a number;

     b number;

     c number;

begin

     a:=&a;

     b:=&b;

     c:=a/b;

     dbms_output.put_line('The value of c is: ' ||c);

exception

when zero_divide then

dbms_output.put_line('Number can not be devided by zero');

end;

/

5-TABLE

DECLARE

  v_empno number(4):=&eno;

  v_ename varchar2(6):='&ename';

BEGIN

  insert into emp(empno,ename) values(v_empno,v_ename);

END;

------------------------------------------------------------------------------

DECLARE

  v_empno emp.empno %type;

  v_ename emp.ename %type;

BEGIN

  v_empno:=&empno;

  v_ename:='&ename';

  insert into emp(empno,ename) values(v_empno,v_ename);

END;

------------------------------------------------------------------------------

DECLARE

  e emp %rowtype;

BEGIN

  e.empno:=&empno;

  e.ename:='&ename';

  insert into emp(empno,ename) values(e.empno,e.ename);

END;

----------------------------------------------------------------------------------

DECLARE

   v_empno emp.empno %type;

   v_ename emp.ename %type;

   v_job emp.job %type;

BEGIN

   select empno,ename,job into v_empno,V_ename,v_job from emp where empno=7369;

   dbms_output.put_line('EMP ID:'||v_empno);

   dbms_output.put_line('Name  :'||v_ename);

   dbms_output.put_line('Job   :'||v_job);

END;

-----------------------------------------------------------------------

DECLARE

   e emp %rowtype;

BEGIN

   select * into e from emp where empno=&empno;

   dbms_output.put_line('EMP ID: '||e.empno);

   dbms_output.put_line('NAME  : '||e.ename);

   dbms_output.put_line('JOB   : '||e.job);

END;

------------------------------------------------------------------------

BEGIN

   for i in (select * from emp)

   Loop

     --dbms_output.put_line('EMP ID:' ||i.empno);

     --dbms_output.put_line(' NAME  :' ||i.ename);

     --dbms_output.put_line('JOB   :' ||i.job);

     --dbms_output.put_line('SALARY:' ||i.sal);

  dbms_output.put_line(i.empno||' '||i.ename||' '||i.job||' '||i.sal);

   End Loop;

END;