Nikolay Baranenko Nikolay Baranenko - 3 months ago 22
SQL Question

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

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;

Answer

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?

Comments