Friday, 13 October 2017

ORACLE PLSQL Exception Handling



SQL> create table a (x number (10), y number(10);
Table created.

SQL> insert into a values(1, 5);
SQL> insert into a values(2, 10);
SQL> insert into a values(3, 15);
SQL> insert into a values(3, 0);
SQL> insert into a values(4, 20);
SQL> insert into a values(5, 25);
SQL> commit;
SQL> select x, y from a order by x;

X Y
---------- ----------
1 5
2 10
3 15
3 0
4 20
5 25
SQL>
declare
v_temp number:= 30;
v_tot number;
v_x number;
v_y number;
cursor c_temp is
select x, y from a order by x;
begin
open c_temp;
loop
fetch c_temp into v_x, v_y;
exit when c_temp%notfound;
v_tot:=v_x+v_temp/v_y;
dbms_output.put_line('Total is :'||v_tot);
end loop;
close c_temp;
exception
when others then
dbms_output.put_line('Found Oracle error: '||SQLERRM);
end;
/

Total is :7
Total is :5
Total is :5
Found Oracle error: ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

Only Partial Records are Printed. 

Below Code Prints : Complete Numbers 

declare
  v_temp number:= 30;
  v_tot number;
  v_x number;
  v_y number;
  cursor c_temp is
    select x, y from a order by x;
begin
  open c_temp;
  loop
    fetch c_temp into v_x, v_y;
    exit when c_temp%notfound;
    begin
      v_tot:=v_x+v_temp/v_y;
      dbms_output.put_line('Total is :'||v_tot);
    exception
      when others then
      dbms_output.put_line('Found Oracle error: '||SQLERRM);
    end;
  end loop;
  close c_temp;
end;

No comments:

Post a Comment