Ora 04061 описание ошибки

On one of Oracle DB instances I am working on I am observing a different than normal behavior when recompiling packages.

Typically, (as in question Frequent error in Oracle ORA-04068: existing state of packages has been discarded) following error on first call is expected after PL/SQL package recompilation:

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

But the second call should work fine, assuming the package has no errors of course. This behavior was present previously in that environment. In the meantime we upgraded from 11g R2 to 12c R1, and enabled edition based redefinition.

Now the problem I am experiencing is that I keep getting just:

ORA-04061: existing state of package body "PACKAGE.NAME" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"PACKAGE.NAME"
ORA-06512: at line 1

So no ORA-04068 anymore, and only way to fix it is to reconnect the session or calling DBMS_SESSION.RESET_PACKAGE() manually (but I don’t control all code that may be affected anyway), otherwise the problem persists on every call.

Are there any DB parameters that control this that could got tweaked? The problem is not specific to any particular PL/SQL package and it seems that it can be triggered by normal package invalidation when something it references, changes.

Thank you in advance.

In Oracle application Workflow related errors are very common specially related to PO Approval or Requisition approval. Recently we got ORA-04061: Existing State Of Package Body has been invalidated for APPS.PO_GA_PVT and APPS.PO_WF_PO_NOTIFICATION. All PO related Workflow notifications were erroring out and users were not able to approve PO from emails and from the system.

Error Message:

[WF_ERROR] ERROR_MESSAGE=3835: Error ‘-20002 — ORA-20002: 2018: Unable to generate the notification XML. Caused by: 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body «APPS.XXUS_PO_WF_PO_NOTIFICATION» has been invalidated ORA-04065: not executed, altered or dropped package body «APPS.XXUS_PO_WF_PO_NOTIFICATION» ORA-06508: PL/SQL: could not find program unit being called: «APPS.XXUS_PO_WF_PO_NOTIFICATION» wf_notification.GetAttrDoc2(3618679, PO_LINES_DE’ encountered during execution of Generate function ‘WF_XML.Generate’ for event ‘oracle.apps.wf.notification.send’. ERROR_STACK= WF_MAIL.GetLOBMessage3(3618679, WFMAIL, 2020: Error when getting notification content. Caused by: ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body «APPS.XXUS_PO_WF_PO_NOTIFICATION» has been invalidated ORA-04065: not executed, altered or dropped package body «APPS.XXUS_PO_WF_PO_NOTIFICATION» ORA-06508: PL/SQL: could not find program unit being called: «APPS.XXUS_PO_WF_PO_NOTIFICATION» wf_notification.GetAttrDoc2(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetAttrDoc(3618679, PO_LINES_DETAILS, text/plain) Wf_Notification.GetText(3618679, text/plain) Wf_Notification.GetBody(3618679, text/plain) WF_NOTIFICATION.GetFullBody(nid => 3618679, disptype => text/plain) WF_MAIL.GetLOBMessage3(nid => 3618679, r_ntf_pref => MAILATTH), Step -> Getting text/plain body) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) WF_XML.Generate(oracle.apps.wf.notification.send, 3618679) Wf_Event.setMessage(oracle.apps.wf.notification.send, 3618679, WF_XML.Generate) Wf_Event.dispatch_internal()

In the database both the package and body were in valid state. We completed following steps as per metalink note to resolve this issue but couldn’t resolve this issue.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN (even when there were no invalids)
3. Start the Notification Mailer and agent listener

Now to resolve this issue we need to do following steps in addition to the above steps.

1. Shutdown the Notification Mailer and agent listener
2. Recompile the APPS schema through ADADMIN
3. connect / as sysdba

SQL>alter system set aq_tm_processes=0 scope=memory;

You need to wait until the q00* and qmnc* processeses are no longer running. You can check via

ps -ef grep q00 and ps -ef grep qmn

SQL> alter system flush shared_pool;

SQL> alter system set aq_tm_processes=1 scope=memory;

4. Start the Notification Mailer and agent listener

Once we did all the above steps users were able to receive mails related to PO and Requisition and they were also able to approve PO from Workflow Notification Emails.

A nasty problem has been haunting me for a while now in my codebase: Every time I run my full utPLSQL test suite, one specific test-package fails every test with ORA-04061 - Existing state of Package has been invalidated. But when I run this specific test package, everything works fine.

Today I followed the rabbit hole and to get a better understanding of what’s going on I tried to extract what I learned into a simple example in my favourite universe.

The situation

Let’s assume we have a table of popular Sith and instead of allowing direct manipulation of the table with have a package sith_manager that handles adding new popular Sith.

create table popular_sith (
  id integer primary key not null,
  name varchar2(200)
);

create sequence popular_sith_seq start with 1;

create or replace package sith_manager as
  procedure add_popular_sith( i_name varchar2 );
end;
/

create or replace package body sith_manager as

  /* To better reveal our intention and to keep
     things that might change separated from the
     implementation, we use a constant for the default
     sith name
  */
  gc_default_name constant varchar2(200) := 'Darth'||' Ora';

  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, gc_default_name)
      );
  end;
end;
/

To validate that in case of passing NULL the default Sith-Name is used, we create a Unit-Test.

There is one difficulty, though: We don’t know what ID our test-entry will get because the add_popular_sith-function uses an underlying sequence. For name is not unique, how can we know which entry to test?

(For some folks pointed it out: I am very aware that this solution is not well-testable and that there are several better ways to deal with the problem. This example only exists to superficially create the scenario I want to demonstrate.
There will be a follow-up blog post that shows alternative, better solutions to solve this problem.)

So let’s control the sequence and make sure it will give a specific ID when we run our test.
For Unit-Tests should only be installed on dev and test environments, that’s not as problematic as it would be in a production environment. However, there are better ways to deal with this kind of situation which I will cover in a future blog post.

create or replace package ut_sith_manager as
  -- %suite(Sith Manager)
  -- %suitepath(darkSide.management)

  procedure control_sequence;

  -- %test(ADD_POPULAR_SITH uses default name on NULL param)
  -- %beforetest(control_sequence)
  procedure add_sith_default_on_null_param;

  -- %afterall
  procedure reset_sequence;
end;
/

create or replace package body ut_sith_manager as

  /* To control which ID our test-entry will be get,
     we control the underlying sequence
   */
  procedure control_sequence as
    pragma autonomous_transaction;
  begin
    /* I am aware that this is not a good way to reset
       sequences. But the whole purpose of this example
       *is* to invalidate the dependent object.
       For a better approach google
       "oracle reset sequence tom kyte"
     */
    execute immediate 'drop sequence popular_sith_seq';
    execute immediate 'create sequence popular_sith_seq' || 
      ' minvalue -100 start with -100';
  end;

  /* After the test is done, we want to set the
     sequence back to its highest value
   */
  procedure reset_sequence as
    pragma autonomous_transaction;
    l_max_id integer;
  begin
    select nvl(max(id),0)+1 into l_max_id from popular_sith;
    execute immediate 'drop sequence popular_sith_seq';
    execute immediate 'create sequence popular_sith_seq'||
      ' start with '||l_max_id;
  end;

  procedure add_sith_default_on_null_param
  as
    l_actual_row popular_sith%rowtype;
  begin
    -- Act
    sith_manager.add_popular_sith(null);

    -- Assert
    select * into l_actual_row
      from popular_sith
      where id = -100;
    ut.expect(l_actual_row.name).to_equal('Darth Ora');
  end;
end;
/

> utplsql run user/pw@localhost -cq -p="ut_sith_manager"
darkside
  management
    Sith Manager
      ADD_POPULAR_SITH uses default name on NULL param [,045 sec]
 
Finished in ,061706 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

So far, so good. Everything works fine.

The Problem

However, if we query for invalid objects after running the test, we will see the following entry:

select 
  object_name, 
  status, 
  last_ddl_time 
from user_objects 
where status <> 'VALID';

Our sith_manager package has been invalidated because of the drop and recreation of the sequence it is referencing. Not a big problem, though, because it’s automatically recompiled when we use it the next time.

Let’s add a sith_generator to create a lot of popular sith then:

create or replace package sith_generator as
  procedure generate_popular_sith(
    i_name varchar2,
    i_count integer
  );
end;
/

create or replace package body sith_generator as
  procedure generate_popular_sith(
    i_name varchar2,
    i_count integer
  ) as
  begin
    if nvl(i_count,0) > 0 then
      for i in 1..i_count loop
        sith_manager.add_popular_sith(
          i_name || ' ' || i
        );
      end loop;
    end if;
  end;
end;
/

Again, we can not check the output directly, but at least we can control the input name and therefore write our test accordingly:

create or replace package ut_sith_generator as
  -- %suite(Sith Generator)
  -- %suitepath(darkSide)

  -- %test(GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>")
  procedure generate_popular_sith;
end;
/

create or replace package body ut_sith_generator as

  procedure generate_popular_sith as
    c_actual sys_refcursor;
    c_expect sys_refcursor;
  begin
    -- Act
    sith_generator.generate_popular_sith('MyTest_Popular_Sith', 3);

    -- Assert
    open c_actual for
      select name from popular_sith
        where name like 'MyTest_Popular_Sith%'
        order by name;
    open c_expect for
      select column_value name
        from table(sys.odcivarchar2list(
          'MyTest_Popular_Sith 1',
          'MyTest_Popular_Sith 2',
          'MyTest_Popular_Sith 3'
          ));
    ut.expect(c_actual).to_equal(c_expect);
  end;

end;
/

> utplsql run user/pw@localhost -cq -p="ut_sith_generator"
darkside
  Sith Generator
    GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>" [,018 sec]
 
Finished in ,021618 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)

But what happens when we call both our tests in one run, e.g. by their shared Suitepath darkside?

> utplsql run user/pw@localhost -cq -p=":darkside"
darkside
  Sith A Generator
    GENERATE_POPULAR_SITH creates correct number of Sith with name "<GIVEN_NAME> <COUNTER>" [,026 sec]
  management
    Sith-Manager
      ADD_POPULAR_SITH uses default name on NULL param [,009 sec] (FAILED - 1)
 
Failures:
 
  1) add_sith_default_on_null_param
      ORA-04061: Existing state of package body "SITHDB.SITH_MANAGER" has been invalidated
      ORA-04065: not executed, altered or dropped package body "SITHDB.SITH_MANAGER"
      ORA-06508: PL/SQL: could not find program unit being called: "SITHDB.SITH_MANAGER"
      ORA-06512: in "SITHDB.UT_SITH_MANAGER", line 36
      ORA-06512: in "SITHDB.UT_SITH_MANAGER", line 36
      ORA-06512: in line 6
Finished in ,047853 seconds
2 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)

Oh my Lord Sith – we are in a situation where both of our packages run just fine on their own, but when sith_generator is run before sith_manager in the same session, we have failing tests – one of the worst circumstances to be in (especially when your whole suite of tests needs some time to run).

But what happens? What is the problem?

There are two things in combination that cause the error:

1. sith_manager contains so-called PL/SQL package state, because it uses a global package variable: gc_default_name. The constant keyword does not prevent the PL/SQL compiler from treating the variable as global state as soon as there is any kind of computation (in our case concatenating 2 strings).

2. sith_manager is invalidated by our ut_sith_manager unit-test.

ut_sith_generator first calls sith_generator which calls and initializes sith_manager. At this point, sith_manager has active global state.
Second, ut_sith_manager invalidates sith_manager causing all active global state to be reset.
For the compiler can not know whether that’s problematic or not, it will cause the ORA-04061 to inform that there has been state that’s now gone.

The Solution

For this problem is caused by the combination of two things, we can divide possible solutions into two categories:

• Avoid package to have global state
• Avoid invalidation of package

In this blog post, I will concentrate on the elimination of global state. There will be a follow-up post that dives into the possibilities to prevent the packages to be invalidated.

Option 1: Use Constant Values that are recognized by the Compiler

If you are on Oracle 12.2 or higher, it will be enough to change the value of our constant to eliminate our global state:

create or replace package body sith_manager as

  gc_default_name constant varchar2(200) := 'Darth Ora';

  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, gc_default_name)
      );
  end;
end;
/

The PL/SQL compiler might be able to correctly recognize constants as such – as long as you don’t do any kind of computation.

Option 2: PRAGMA SERIALLY_REUSABLE

By adding pragma serially_reusable to the sith_manager package, we can also avoid global state to be created:

create or replace package sith_manager as
  pragma serially_reusable;

  procedure add_popular_sith( i_name varchar2 );
end;
/

create or replace package body sith_manager as
  pragma serially_reusable;
  gc_default_name constant varchar2(200) := 'Darth'||' Ora';

  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, gc_default_name)
      );
  end;
end;
/

What happens is that any global package state that might be created is immediately deleted after each call.

The problem is, that this renders the whole package impossible to be called from SQL:

create or replace package sith_manager as
  pragma serially_reusable;

  procedure add_popular_sith( i_name varchar2 );

  function id_by_name( i_name varchar2 )
    return varchar2;
end;
/

create or replace package body sith_manager as
  pragma serially_reusable;
  gc_default_name constant varchar2(200) := 'Darth'||' Ora';

  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, gc_default_name)
      );
  end;

  function id_by_name( i_name varchar2 )
    return varchar2
  as
    l_result integer;
  begin
    select id into l_result
      from popular_sith
      where name = i_name;
    return l_result;
  exception when no_data_found then
    return null;
  end;
end;
/

call sith_manager.add_popular_sith('Darth Vader');
select sith_manager.id_by_name('Darth Vader') from dual;

The last select will cause ORA-06534: Cannot access Serially Reusable package "SITHDB.SITH_MANAGER".

There are also other limitations, for example it’s not possible to have pipelined table functions in such a package.

Option 3: Move Constants to a separate Package

This is not exactly “avoiding global state”, but avoiding global state in the package that will become invalidated:

By moving the constants to a separate package we make sure that any invalidation of the package that holds the implementation has no impact on the configuration stuff we hold in global state.

create or replace package sith_manager_const as
  gc_default_name constant varchar2(200) := 'Darth'||' Ora';
end;
/

create or replace package sith_manager as
  procedure add_popular_sith( i_name varchar2 );
end;
/

create or replace package body sith_manager as
  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, sith_manager_const.gc_default_name)
      );
  end;
end;
/

While it might be inconvenient to be forced to have these constants now public, it prevents the ORA-04061 very effectively.

There can also be benefits to have shared public constants, but be mindful when introducing more coupling.

Option 4: Use Functions

Another option is to replace the package variable with a function that returns the fixed value:

create or replace package sith_manager as
  function gc_default_name
    return varchar2 deterministic;

  procedure add_popular_sith( i_name varchar2 );
end;
/

create or replace package body sith_manager as

  function gc_default_name
    return varchar2 deterministic as
  begin
    return 'Darth '||'Ora';
  end;

  procedure add_popular_sith( i_name varchar2 )
  as
  begin
    insert into popular_sith (id, name )
      values (
        popular_sith_seq.nextval,
        nvl(i_name, gc_default_name)
      );
  end;
end;
/

To use the function in SQL (even in SQL used inside the Package), we need to make it public. Alternatively we could store the function result in a local variable first, in that case we wouldn’t need to make the function public.

I am also concerned that this kind of use might obfuscate the intention of the code. Make sure to introduce strong naming conventions among your team to make it clear when a function’s only purpose is to serve as kind of constant.

(Not An) Option 5: DBMS_SESSION.RESET_PACKAGE

It might be tempting to think that using dbms_session.reset_package will solve our global state problem.

This procedure resets all package state and we can even force to reinitialize it:

dbms_session.reset_package();
dbms_session.modify_package_state( dbms_session.reinitialize );

But for utPLSQL stores a lot of temporary information in its internal package state, this will inevitably lead to an empty test output.

Conclusion

Do you have more ideas how to deal with ORA-04061 in such a setting? Then reach out to me – or wait for my follow-up post on the “Avoid invalidation of the package” options 😉

As always you can find a full example on my github repository.

Update 2020-04-08: Added disclaimer about the example purposefully not being well-testable.
Update 2020-04-08: Added Option 4: Use Functions.
Update 2020-04-08: Added (Not An) Option 5: DBMS_SESSION.RESET_PACKAGE