Another reason against exception swallowing (ORA-04068)

How many times do you see PL/SQL blocks like this?

EXCEPTION
WHEN OTHERS THEN
  dbms_output('Oopsy doopsy:'||SQLERRM);
END;

Merely looking at it makes me want to shower with my clothes on. Everyone knows that this is bad, but there’s something I had never considered about it until last week when my boss came over with an interesting observation.

Another team was testing a dbms_parallel_execute task that called a packaged procedure that lives on our DW database, my team is responsible for the DW database from a developer perspective and my boss was working with the other team to coordinate what they would be doing. Part way through the task, my boss realized that the version of the package on our development environment was missing a few tweaks, he recompiled it with the new code. What happened next was semi-expected, a chunk of the job failed with

ORA-04068: existing state of packages has been discarded

“Ah, we have a global variable in that package causing that error, but that’s okay, the next execution of the task will be fine and we can do a clean up of that one failed chunk” I thought.

Wrong! All subsequent executions of chunks in the task failed with the same reason (I’m not sure if it was exactly the same error or a slight tweak). “Hmm, that’s weird, maybe there’s some sort of flag that needs to be set when the client receives that error message so that Oracle knows it’s safe to use the package again” I thought out loud (with plenty of head scratching and hand waving). So I made a demo of how I expect dbms_parallel_execute runs tasks under the hood, this is the result of these demos on my home setup using

ANDY@orcl>select banner from v$version where rownum <2;

BANNER
-------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
(unpatched)

First the package that I’ll be calling in my task, it just needs to be a simple package with a procedure, the requirement is that has a global variable.

create or replace package global_var_pkg
is
procedure doStuff;
end global_var_pkg;
/
create or replace package body global_var_pkg
is
  global_var varchar2(30 char);
procedure doStuff is
begin 
  null;
end doStuff;
end global_var_pkg;
/

Does it work?

ANDY@orcl>exec  global_var_pkg.doStuff;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Does recompiling it cause the error? Is executing after the error allowed?
<second session>ANDY@orcl>alter package global_var_pkg compile;
Package altered.
<first session>ANDY@orcl>exec  global_var_pkg.doStuff;
BEGIN global_var_pkg.doStuf; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "ANDY.GLOBAL_VAR_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "ANDY.GLOBAL_VAR_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "ANDY.GLOBAL_VAR_PKG"
ORA-06512: at line 1

Elapsed: 00:00:00.03
ANDY@orcl>exec  global_var_pkg.doStuff;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
Everything normal, right?

Next we need a way of looping through executions of the procedure, when an exception is found it must log it to another table and the transaction should be rolled back. I couldn’t be bothered to set up a logging table so I naively decided that dbms_output would be fine because I’m using SQL*Plus. I just need to set serverout on and I knew that that was already the case (my glogin.sql does it for me).

declare
  sTask varchar2(400) := 'begin global_var_pkg.doStuff; end;';
begin
  for i in 1..5
  loop
    begin
      dbms_output.put_line('Start chunk');
      execute immediate sTask;
      dbms_output.put_line('End chunk');
      commit;
      dbms_lock.sleep(0.5);
    exception when others then
      dbms_output.put_line(sqlerrm||chr(13)||chr(10)||dbms_utility.format_error_backtrace);
      rollback;
    end;
  end loop;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:02.51

That’s weird, it took the amount of time you’d expect for something that slept 5 times at 0.5 seconds, but it didn’t output those dbms_output lines. Going back to the error we saw previously:

ORA-04068: existing state of packages has been discarded

That “packages” must have included dbms_output, it uses a global variable to enable its use. Makes sense, we should just need to reenable it:

set serverout on
declare
  sTask varchar2(400) := 'begin global_var_pkg.doStuff; end;';
begin
  for i in 1..5
  loop
    begin
      dbms_output.put_line('Start chunk');
      execute immediate sTask;
      dbms_output.put_line('End chunk');
      commit;
      dbms_lock.sleep(0.5);
    exception when others then
      dbms_output.put_line(sqlerrm||chr(13)||CHR(10)||dbms_utility.format_error_backtrace);
      rollback;
    end;
  end loop;
end;
/

Start chunk
End chunk
Start chunk
End chunk
Start chunk
End chunk
Start chunk
End chunk
Start chunk
End chunk

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.57
As expected. Now what happens when we recompile the package like before:
Start chunk
ORA-04061: existing state of  has been invalidated
ORA-04061: existing state of package "ANDY.GLOBAL_VAR_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package
"ANDY.GLOBAL_VAR_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "ANDY.GLOBAL_VAR_PKG"
ORA-06512: at line 1
ORA-06512: at line 8

Start chunk
ORA-04061: existing state of  has been invalidated
ORA-04061: existing state of package "ANDY.GLOBAL_VAR_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package
"ANDY.GLOBAL_VAR_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "ANDY.GLOBAL_VAR_PKG"
ORA-06512: at line 1
ORA-06512: at line 8
...
Hmm, I don’t see an ORA-04068 but the error messages do look very similar.
When I execute the code again, the same errors occur, the same amount of times. We execute the procedure manually again:
ANDY@orcl>exec global_var_pkg.doStuff
BEGIN global_var_pkg.doStuff; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "ANDY.GLOBAL_VAR_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package "ANDY.GLOBAL_VAR_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "ANDY.GLOBAL_VAR_PKG"
ORA-06512: at line 1

Elapsed: 00:00:00.03
ANDY@orcl>exec global_var_pkg.doStuff

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
We get our full error back, and we’re allowed to call procedure in the next execution. At this point the global variables seem to be reset to defaults as when we call our exception swallowing code we get:
PL/SQL procedure successfully completed.

Elapsed: 00:00:02.53

No dbms_output lines but it still takes 2.5 seconds.

Which got me thinking, maybe it is actually the result of that exception going to the client which causes our global variables to be reset allowing us to call the package again. A quick demo with dbms_output:

ANDY@orcl>set serverout on
ANDY@orcl>exec dbms_output.put_line('This should work')
This should work
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
ANDY@orcl>declare
  2    e4068 exception;
  3    pragma exception_init(e4068,-4068);
  4  begin
  5    raise e4068;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04068: existing state of packages has been discarded
ORA-06512: at line 5

Elapsed: 00:00:00.03
ANDY@orcl>exec dbms_output.put_line('This shouldn''t work')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

And what about being able to use our procedure again? Well, I executed the swallowing code, recompiled the package and executed the swallowing code again (I remembered I needed to set serverout on first). No surprises that the second execution gave me all those errors in my dbms_output. Next I called that raise e4068 code from above:

Start chunk
ORA-04061: existing state of  has been invalidated
ORA-04061: existing state of package "ANDY.GLOBAL_VAR_PKG" has been invalidated
ORA-04065: not executed, altered or dropped package
"ANDY.GLOBAL_VAR_PKG"
ORA-06508: PL/SQL: could not find program unit being called: "ANDY.GLOBAL_VAR_PKG"
ORA-06512: at line 1
ORA-06512: at line 8

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.18
ANDY@orcl>declare
  2    e4068 exception;
  3    pragma exception_init(e4068,-4068);
  4  begin
  5    raise e4068;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04068: existing state of packages has been discarded
ORA-06512: at line 5

Elapsed: 00:00:00.03
ANDY@orcl>declare
  2    sTask varchar2(400) := 'begin global_var_pkg.doStuff; end;';
  3  begin
  4    for i in 1..5
  5    loop
  6      begin
  7        dbms_output.put_line('Start chunk');
  8        execute immediate sTask;
  9        dbms_output.put_line('End chunk');
 10        commit;
 11        dbms_lock.sleep(0.5);
 12      exception when others then
 13        dbms_output.put_line(sqlerrm||chr(13)||CHR(10)||dbms_utility.format_error_backtrace);
 14        rollback;
 15      end;
 16    end loop;
 17  end;
 18  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.51

And we’re all too used to knowing what that 2.5 seconds elapsed time means but no output: I reset my dbms_output global variable again, but I was allowed to execute from that package again.

To summarize:

If you swallow the ORA-04068 error then your call to the package will continue to not work until you raise the error properly. I can imagine an attempted work around being to reconnect, but that is just plain silly, the real fix is to stop swallowing exceptions.

I was quite surprised by how this works, and I haven’t read about the behaviour before so I thought it worthy of sharing. Obviously, bad things happen when you swallow exceptions and this is definitely not the worst thing (violating ACID comes to mind), but it gives some insight to how the ORA-04068 exception actually does more than report the error.

I’d be interested in knowing of any other exceptions that may have other behaviours.

Andrew

Advertisements