Ignore lines that causes errors

Question

I have a big Oracle script with thousands of package call inside a BEGIN - END;

Is there a way to ignore the lines that causes error and continue executing the next lines? Some sort of "On Error Resume Next" in vb.

Answer 1

I don't think there is any magic one-liner that will solve this.

As others have, use a editor to automate the wrapping of each call within a BEGIN-EXCEPTION-END block might be quicker/easier.

But, if feel a little adventurous, or try this strategy:

Let's assume you have this:

BEGIN
  proc1;
  proc2;
  proc3;
.
.
.
  proc1000;
END;

You could try this (untested, uncompiled but might give you an idea of what to try):

DECLARE
   l_progress NUMBER := 0;
   l_proc_no  NUMBER := 0;
   e_proc_err  EXCEPTION;
   -- A 'runner' procedure than manegrs the counters and runs/skips dpending on these vals
   PROCEDURE run_proc ( pname IN VARCHAR2 ) IS
   BEGIN
      l_proc_no := l_proc_no + 1;
      IF l_proc_no >= l_progress
      THEN
         -- log 'Running pname'
         EXECUTE IMMEDIATE 'BEGIN ' || pname || '; END;' ;
         l_progress := l_progress + 1;
      ELSE
         -- log 'Skipping pname'
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         -- log 'Error in pname'
         l_progress := l_progress + 1;
         RAISE e_proc_err;
   END;
BEGIN
   l_progress := 0;
<<start>>
   l_proc_no := 0;
   run_proc ( 'proc1' );
   run_proc ( 'proc2' );
   run_proc ( 'proc3' );
.
.
   run_proc ( 'proc1000' );
EXCEPTION
   WHEN e_proc_err THEN
      GOTO start;
    WHEN OTHERS THEN
      RAISE;
END;

The idea here is to add a 'runner' procedure to execute each procedure dynamically and log the run, skip, error.

We maintain a global count of the current process number (l_proc_no) and overall count of steps executed (l_progress).

When an error occurs we log it, raise it and let it fall into the outer blocks EXCEPTION handler where it will restart via an (evil) GOTO.

The GOTO is placed such that the overall execution count is unchanged but the process number is reset to 0.

Now when the run_proc is called it sees that l_progress is greater than l_proc_no, and skips it.

Why is this better than simply wrapping a BEGIN EXCEPTION END around each call?

It might not be, but you make a smaller change to each line of code, and you standardise the logging around each call more neatly.

The danger is a potential infinite loop which is why I specify e_proc_err to denote errors within the called procedures. But it might need tweaking to make it robust.