CODEWITHSUNDEEP
oracle-databaseplsql
im_chc
im_chc

Reputation: 1075

How to make an oracle cursor continue after it fetches a failed row?

Executing the following PL/SQL block:

declare
   cursor c1 is
      select rownum r1,
      5/
      case when r = 5 then 0 else 1 end
      dzero, r from
      (
      SELECT RowNum r FROM dual CONNECT BY level <= 10
      ) 
      ;
BEGIN

  dbms_output.enable(buffer_size => NULL);

  begin
     FOR c1_rec in c1
     LOOP
          dbms_output.put_line('val: ' || c1_rec.r1);
     END LOOP;
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error: '||SQLCODE||' -ERROR- '||SQLERRM);
  end;

END;

Output is:

val: 1
val: 2
val: 3
val: 4
Error: -1476 -ERROR- ORA-01476: divisor is equal to zero

Given that the query of c1 unchanged, how to make the cursor continue to fetch the 6-10th rows, so that the output continue to show the followings?

val: 6
val: 7
val: 8
val: 9
val: 10

Upvotes: 2

Views: 277

Answers (1)

pablomatico
pablomatico

Reputation: 2242

I think there's no way of catching that exception. The right thing to do would be to use NULLIF to avoid the division by zero but you would have to change the query:

DECLARE
  CURSOR c1 IS
    SELECT rownum r1,
           5/NULLIF(CASE WHEN r = 5 THEN 0 ELSE 1 END, 0) dzero, 
           r 
    FROM (SELECT RowNum r FROM dual CONNECT BY level <= 10) ;
BEGIN
  dbms_output.enable(buffer_size => NULL);

  FOR c1_rec in c1 LOOP
    dbms_output.put_line('val: ' || c1_rec.r1);
  END LOOP;
END;

Upvotes: 2

Related Questions