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

If you have only one BEGIN END section, then you can use EXCEPTION WHEN OTHERS THEN NULL.

SQL> declare
    v_var pls_integer;
    begin
    select 1 into v_var from dual;
    -- now error
    select 'A' into v_var from dual;
    exception when others then null;
    end;
SQL> /

PL/SQL procedure successfully completed.

SQL> declare
   v_var pls_integer;
    begin
    select 1 into v_var from dual;
    -- now error
    select 'A' into v_var from dual;
    --exception when others then null;
    end;
    /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 6


SQL>

Answer 2

The whole concept of "ignore errors" is a bug, and a lie if any errors occur. That is not to say you cannot trap errors and continue processing, just that you MUST handle the errors. For example, assume the use case: "Data has been loaded into a stage table from multiple .csv files. Now load into the tables A and Table B according to ....".

create procedure
Load_Tables_A_B_from_Stage(process_message out varchar2)
is
Begin 
    For rec in (select * from stage)
    loop
       begin 
           insert into table_a (col1, col2) 
           values (rec.col_a1, col_a2);

           insert into table_b (col1, col2) 
           values (rec.col_b1, col_b2);
       exception 
           when others then null;
       end;
    end loop;
    process_message := 'Load Tables A,B Complete';
end ;

Now suppose a user created the a .csv file entered "n/a" in numeric columns where there was no value or the value was unknown. The result of this all too common occurrence is all such rows were not loaded, but you have no way to know that until the user complains their data was not loaded even though you told them it was. Further you have no way of determining the problem.
A much better approach is to "capture and report".

create procedure
Load_Tables_A_B_from_Stage(process_message out varchar2)
is
    load_error_occurred boolean := False;
Begin 
    For rec in (select * from stage)
    loop
       begin 
           insert into table_a (col1, col2) 
           values (rec.col_a1, rec.col_a2);
       exception 
           when others then 
                log_load_error('Load_Tables_A_B_from_Stage', stage_id, sqlerrm);
                load_error_occurred := True;
       end; 
       
       begin 
           insert into table_b (col1, col2) 
           values (rec.col_b1, rec.col_b2);
       exception 
           when others then 
                log_load_error('Load_Tables_A_B_from_Stage', stage_id, sqlerrm);
                load_error_occurred := True;
       end;

    end loop;
    
    if load_error_occurred then
       process_message := 'Load Tables A,B Complete: Error(s) Detected';    
    else
       process_message := 'Load Tables A,B Complete: Successful No Error(s)';
    end if;
end Load_Tables_A_B_from_Stage ; 
 

Now you have informed the user of the actual status, and where you are contacted you can readily identify the issue.

User here is used in the most general sense. It could mean a calling routine instead of an individual. Point is you do not have to terminate your process due to errors but DO NOT ignore them.

Answer 3

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.