Thursday, May 19, 2016

How to prevent a PL/SQL cursor loop from failing?

The PL/SQL language uses the following format:

DECLARE
<variable and cursor declaration>
BEGIN
<Write code here>
EXCEPTION
<optional>

END;
/

But what happens if we use a cursor and a loop and one of the records in there cause the loop to fail?

Exceptions work together with BEGIN ... END;
So we add that to the loop.

DECLARE

vMyVarchar varchar2(100);
vMynumber number(2) := 10;

cursor cMyCursor is
 select some,columns
 from sometables
 where someconditions = 'something';

BEGIN
   OPEN cMyCursor;
   LOOP
      BEGIN
         FETCH cMyCursor into vMyVarchar;
         EXIT WHEN cMyCursor%NOTFOUND;

         --Let's pretend thatvMyVarchar is an executable statement
         exec immediate vMyVarchar;
         EXCEPTION WHEN OTHERS THEN
            dbms_output.put_line(vMyVarchar);
         END;
EXCEPTION WHEN OTHERS CONTINUE;
END;
/

No comments :

Post a Comment