EXCEPTIONS

EXCEPTIONS.


  Exception is an abnormal condition that causes the termination of the program in the middle of the   execution.

Exception Handler:
        It is a unit of plsql code that oracle engine raised when an exception is occurred.
 When an exception is occurred,oracle engine identifies it and raises the exception to the exception handler which scans the plsql program for the occurrence of exception section and if found the exception handler then checks for the occurrence of the word when followed by a predefined or user defined exception,which inturn followed by a then keyword and action associated with that exception.

Exceptions are of 2 types:
1) Predefined exception.
2) User defined exception. à these can be rasied explicitly by the user.

Some predefined exceptions examples:
1)      Zero divide by      à Occurs when the divisor is zero.
2)      No_data_found   àOccurs when the sql select statement fetching the records into variables retrieves no rows.
3)      Too_many_rows àOccurs when the sql select statement fetching the records into variables retrieves more then one row.
4)      Others                             àAny other type exceptions rasied will be handled using this exception.

NOTE: Exception should be always placed between begin and end block and exactly before the end.

Syntax:  BEGIN
                   --Any sql Statement.
                 EXCEPTION
                  WHEN Exception Name THEN
                       Any action to be taken place (like printing a error message)
                END;

In the above syntax there can be many when exception clauses and if we use others exception clause then it should be placed at last always.
If iam using no_data_found,too_many_rows and others exception then the seq should be always as per below example


BEGIN
                   --Any sql Statement.
                 EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       Any action to be taken place (like printing a error message);
                WHEN TOO_MANY_ROWS THEN
                       Any action to be taken place (like printing a error message);
               WHEN OTHERS THEN
                       Any action to be taken place (like printing a error message);
                END;

But if I write in below way that will error….
BEGIN
                   --Any sql Statement.
                 EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                       Any action to be taken place (like printing a error message);
               WHEN OTHERS THEN
                       Any action to be taken place (like printing a error message);
               WHEN TOO_MANY_ROWS THEN
                       Any action to be taken place (like printing a error message);
                END;


BEGIN
                   --Any sql Statement.
                 EXCEPTION
               WHEN OTHERS THEN
                       Any action to be taken place (like printing a error message);
                  WHEN NO_DATA_FOUND THEN
                       Any action to be taken place (like printing a error message);
                WHEN TOO_MANY_ROWS THEN
                       Any action to be taken place (like printing a error message);
                END;


SQLERRM: Sql Error Message…
  It is an oracle predefined keyword which holds oracle error message when any exception raises.








Ex:1 
create or replace procedure xx_emp_dtls(p_empno number)
is
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
ln_dname dept.dname%type;
ln_loc   dept.loc%type;
ln_flag   char(1):= 'N';
begin

 BEGIN
  select empno,ename,sal,dname,loc
  into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
  from emp e,dept d
  where empno = p_empno
  and e.deptno = d.deptno;
  exception
   when no_data_found then
   dbms_output.put_line('No data exists for the passed empno'||p_empno);
   when too_many_rows then
   dbms_output.put_line('too many rows for the passed');
   when others then
   dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
  END;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is --'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
 exception
  when others then
  dbms_output.put_line('Error in the xx_emp_dtls proceudre'||substr(sqlerrm,1,250));
end;
/





In the below example we are using the deptno as a parameter and trying to fetch the records for that deptno
But as per real time one deptno can always have more then one row which select query could always fail and go in to exception. In order to handle such kind of these we always need to print them in a loop.
A select query written in the plsql block should always retrun a single row values

Ex2:  create or replace procedure xx_emp_dtls(p_deptno number)
is
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
ln_dname dept.dname%type;
ln_loc   dept.loc%type;
ln_flag   char(1):= 'N';
begin
 BEGIN
  select empno,ename,sal,dname,loc
  into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
  from emp e,dept d
  where e.deptno = p_deptno
  and e.deptno = d.deptno;
  exception
   when no_data_found then
   dbms_output.put_line('No data exists for the passed empno'||p_deptno);
   when too_many_rows then
   dbms_output.put_line('too many rows for the passed');
   when others then
   dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
  END;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is --'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
 exception
  when others then
  dbms_output.put_line('Error in the xx_emp_dtls proceudre'||substr(sqlerrm,1,250));
end;
/



Print the no of records exists for the passed deptno and also print the employee dtls for the passed empno…


create or replace procedure xx_dtls(p_deptno dept.deptno%type
                                    ,p_empno emp.empno%type)
is
ln_cnt number;
ln_empno emp.empno%type;
ln_ename emp.ename%type;
ln_sal   emp.sal%type;
ln_dname dept.dname%type;
ln_loc   dept.loc%type;
BEGIN

  select count(*)
  into   ln_cnt
  from  emp
  where deptno = p_deptno;
 dbms_output.put_line('The no of records is '||ln_cnt);

-- printing the emp details
 BEGIN
  select empno,ename,sal,dname,loc
  into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
  from emp e,dept d
  where e.empno = p_empno
  and e.deptno = d.deptno;
dbms_output.put_line('The ename is --'||ln_ename);
dbms_output.put_line('The sal   is --'||ln_sal);
dbms_output.put_line('The empno is --'||ln_empno);
dbms_output.put_line('The dpartment name is --'||ln_dname);
dbms_output.put_line('The location is --'||ln_loc);
  exception
   when no_data_found then
    null;
   when others then
   dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
  END;

exception
 when others then
 dbms_output.put_line('Error in the xx_dtls procedure '||substr(sqlerrm,1,250));
END;

Exercise:  modify the written procedure by handling the exceptions in a right approach

Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and grade.


Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and result.


Write a procedure to print the student information and their total marks and grade.
show student marks,result,grade even if the student information doesnot exists
by passing input parameter as sno and marks. The student marks should be greater then marks parameter value.




User defined Exception: This is a exception which is declared in the declaration section by the user and rasied explicitly by using raise keyword when ever we require..


Syntax:  variable_name EXCEPTION.

Ex: create or replace procedure xx_dtls(p_deptno dept.deptno%type
                                     ,p_empno emp.empno%type)
 is
 ln_cnt number;
 ln_empno emp.empno%type;
 ln_ename emp.ename%type;
 ln_sal   emp.sal%type;
 ln_dname dept.dname%type;
 ln_loc   dept.loc%type;
 u_exp    EXCEPTION;
 BEGIN
   select count(*)
   into   ln_cnt
   from  emp
   where deptno = p_deptno;
  dbms_output.put_line('The no of records is '||ln_cnt);
 if ln_cnt = 0 then
 raise u_exp;
 end if;
 -- printing the emp details
  BEGIN
   select empno,ename,sal,dname,loc
   into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
   from emp e,dept d
   where e.empno = p_empno
   and e.deptno = d.deptno;
 dbms_output.put_line('The ename is --'||ln_ename);
 dbms_output.put_line('The sal   is --'||ln_sal);
 dbms_output.put_line('The empno is --'||ln_empno);
 dbms_output.put_line('The dpartment name is --'||ln_dname);
 dbms_output.put_line('The location is --'||ln_loc);
   exception
    when no_data_found then
     null;
    when others then
    dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
   END;
 exception
  when u_exp  then
  dbms_output.put_line('user defined exception rasied');
  when others then
  dbms_output.put_line('Error in the xx_dtls procedure '||substr(sqlerrm,1,250));
 END;
 /


Write a procedure to print the no of records for the passed deptno and print emp dtails for the passes emp no. before printing check if the flag value is Y. if it is other then Y or null or too many rows in the table then raise user defined exception and print a appropriate message.

Create table ckh_tble(flag char(1));


create or replace procedure xx_dtls(p_deptno dept.deptno%type
                                     ,p_empno emp.empno%type)
 is
 ln_cnt number;
 ln_empno emp.empno%type;
 ln_ename emp.ename%type;
 ln_sal   emp.sal%type;
 ln_dname dept.dname%type;
 ln_loc   dept.loc%type;
 u_exp    EXCEPTION;
ln_flag char(1);
 BEGIN
    begin
     select nvl(flag,’N’)
     into   ln_flag
     from   check_flag
     exception
       when no_data_found then
       ln_flag := 'N' ;
       when others then
        ln_flag := 'N' ;
    end;
if ln_flag = 'N' then
 raise u_exp;
end if;
   select count(*)
   into   ln_cnt
   from  emp
   where deptno = p_deptno;
  dbms_output.put_line('The no of records is '||ln_cnt);
 -- printing the emp details
  BEGIN
   select empno,ename,sal,dname,loc
   into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
   from emp e,dept d
   where e.empno = p_empno
   and e.deptno = d.deptno;
 dbms_output.put_line('The ename is --'||ln_ename);
 dbms_output.put_line('The sal   is --'||ln_sal);
 dbms_output.put_line('The empno is --'||ln_empno);
 dbms_output.put_line('The dpartment name is --'||ln_dname);
 dbms_output.put_line('The location is --'||ln_loc);
   exception
    when no_data_found then
     null;
    when others then
    dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
   END;
 exception
  when u_exp  then
  dbms_output.put_line('user defined exception rasied  please check the chk_tble');
  when others then
  dbms_output.put_line('Error in the xx_dtls procedure '||substr(sqlerrm,1,250));
 END;
/

Insert into table as value – Y – success for printing
Insert into table as value – Y,N etc multiple combinations – raise exception
Insert into table as value – N – raise exception
Insert into table as value – Null value. – raise exception..

Dynamic message handling for the above scenarios….


create or replace procedure xx_dtls(p_deptno dept.deptno%type
                                     ,p_empno emp.empno%type)
 is
 ln_cnt number;
 ln_empno emp.empno%type;
 ln_ename emp.ename%type;
 ln_sal   emp.sal%type;
 ln_dname dept.dname%type;
 ln_loc   dept.loc%type;
 u_exp    EXCEPTION;
 ln_msg   varchar2(1000);
ln_flag char(1);
 BEGIN
    begin
     select nvl(flag,'X')
     into   ln_flag
     from   check_flag;
     exception
       when no_data_found then
       ln_flag := 'N' ;
       ln_msg  := 'No value exists in the table for the flag';
       when others then
        ln_flag := 'N' ;
            ln_msg  := 'Too many rows or others exception raised';
    end;


if ln_flag in ('N','X') then
    if ln_flag = 'X' then
    ln_msg := 'Null value exists for the flag in the check table';
    end if;
 raise u_exp;
end if;
   select count(*)
   into   ln_cnt
   from  emp
   where deptno = p_deptno;
  dbms_output.put_line('The no of records is '||ln_cnt);
 -- printing the emp details
  BEGIN
   select empno,ename,sal,dname,loc
   into  ln_empno,ln_ename,ln_sal,ln_dname,ln_loc
   from emp e,dept d
   where e.empno = p_empno
   and e.deptno = d.deptno;
 dbms_output.put_line('The ename is --'||ln_ename);
 dbms_output.put_line('The sal   is --'||ln_sal);
 dbms_output.put_line('The empno is --'||ln_empno);
 dbms_output.put_line('The dpartment name is --'||ln_dname);
 dbms_output.put_line('The location is --'||ln_loc);
   exception
    when no_data_found then
     null;
    when others then
    dbms_output.put_line('Others exception raised'||substr(sqlerrm,1,250));
   END;
 exception
  when u_exp  then
  dbms_output.put_line(ln_msg);
  when others then
  dbms_output.put_line('Error in the xx_dtls procedure '||substr(sqlerrm,1,250));
 END;

/

Post a Comment