Oracle provides for handling of error messages in programming blocks through the use of exceptions. During the execution of a loop, the general exception clause at the end of a procedure, function or pl/sql block will exit the loop for handling of error, thus stopping the processing. However, you can change this behavior by placing an exception clause within the loop block itself. This procedure shows how to setup a loop with an exception clause, so the loop is not exited on error. NOTE: To just see the loop exception go to step 5.
- Create the following test table:
create table test_a(a number);
declare
n number:=1;
begin
while n<4
loop
insert into test_a(a) values(n);
n:=n+1;
end loop;
end;
/
- Now execute the following block to extract the data from table test_a
set serveroutput on
declare
n number;
begin
for rec in(select * from test_a)
loop
dbms_output.put_line(rec.a);
end loop;
end;
/
1
2
3
PL/SQL procedure successfully completed.
- Create an error by Insert the line “if mod(rec.a,2)=0 then n:=rec.a/0 else dbms_output.put_line(rec.a);” after the
set serveroutput on
declare
n number;
begin
for rec in(select * from test_a)
loop
if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;
end loop;
end;
/
1
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 6
SQL>
- Now insert the exception “exception when others then null;” at the end of the block and we see that the exception is handled by the loop exited before giving us the value of 3.
SQL> set serveroutput on
declare
n number;
begin
for rec in(select * from test_a)
loop
if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;
end loop;
exception when others then null;
end;
/
1
PL/SQL procedure successfully completed.
SQL>
- Wrap the internals of the for loop in its own block and move the exception clause inside the block, now you see the exception is handled and the loop continues, giving us the the value of 3.
SQL> set serveroutput on
declare
n number;
begin
for rec in(select * from test_a)
loop
begin
if mod(rec.a,2)=0 then n:=rec.a/0; else dbms_output.put_line(rec.a); end if;
exception when others then null;
end;
end loop;
end;
/
1
3
PL/SQL procedure successfully completed.
SQL>
- This completes handling of exceptions in a oracle loop which continues.
Larry Catt, OCP