How handle exception about invalidate call procedure from body Oracle Scheduler Job?

Question

I had version Oracle DB - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production.

Is it possible in the body ORACLE scheduler job somehow exclude full stop on an error when one of procedures has invalidation?

p.s. I tried to solve, BUT invalidation procedure for exmple EXAMPLE1_POINTS stopped working scheduler job

Error

ORA-06550: line 19, column 3:
PLS-00905: object MON.EXAMPLE1_POINTS is invalid
ORA-06550: line 19, column 3:
PL/SQL: Statement ignored

scheduler job body

begin

  begin
  EXAMPLE1_POINTS('Да','новый"');
  EXCEPTION
  when OTHERS then null;
  end;

  begin
  EXAMPLE2_POINTS('Да','средний"');
  EXCEPTION
  when OTHERS then null;
  end;

  begin
  EXAMPLE3_POINTS('Да','старый"');
  EXCEPTION
  when OTHERS then null;
  end;

end;

Rene Rene · Accepted Answer · 2016-09-01T11:40:27

All packages and functions need to be valid in order for your code to run. However you can run this as dynamic sql.

declare
  l_statement varchar2(32767);
begin

  l_statement := 'begin
                     EXAMPLE1_POINTS(''Да'',''новый"'');
                  end;';

  begin
     execute immediate l_statement;
  exception
     when others 
     then 
        log_error(sqlerrm); -- some error logging mechanism.
  end;

  -- repeat for other functions.
  --
end;

And try to avoid "when others then null". If you don't care if your code has run or not, why run it in the first place?

How handle exception about invalidate call procedure from body Oracle Scheduler Job?

1 Answers