String constants and package state

I have previously discussed package state and avoiding the “ORA-04068: existing state of packages has been discarded” exception. Connor McDonald has pointed out that come Oracle 11.2 constants do not create package state, except when they involve a calculation. This post expands on that by noting that string constants always create package state (thanks to my work colleague Vlad for pointing this out, after finding out the hard way…).

First up, let’s take a look at how numeric constants don’t create package state. We start with the simplest of packages compiled in session 1:

   c_number   CONSTANT NUMBER(5) := 123;
END pkg_state;

and in another session, session 2, we run a short program that makes reference to the package constant:

   dbms_output.put_line ('Package value is: ' || TO_CHAR(pkg_state.c_number));

Not surprisingly we get the output:

Package value is: 123

If we go back to session 1 and recompile the package changing the constant to a different value:

   c_number   CONSTANT NUMBER(5) := 789;
END pkg_state;

and rerun our simple output script back in session 2 we don’t get ORA-04068 but instead our session displays the new value:

Package value is: 789

Now let’s repeat the entire exercise but with a string constant. In session 1:

   c_string   CONSTANT VARCHAR2(5) := 'ABC';
END pkg_state;

Running this in session 2:

   dbms_output.put_line ('Package value is: ' || pkg_state.c_string);

we get:

Package value is: ABC

Compiling the package in session 1 to:

   c_string   CONSTANT CHAR(5) := 'XYZ';
END pkg_state;

gives us the following when we rerun our display routine in session 2:

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

This occurs with Oracle 11.2 and 12.1. So, while in these versions have taken steps to limit constants from creating package state, Connor’s post and this post show that it doesn’t apply to all constants. Hence some care needs to be taken when planning your deployments. You wouldn’t want to be in a situation where you think you don’t have package state but in reality you do. Right Vlad?

ORA-01001: invalid cursor with REF CURSOR output parameter

A short post to note an Oracle database bug I encountered recently. Whilst testing some code the exception:

ORA-01001: invalid cursor

was produced. The error was originating from a pipelined function. Tracking things down it seems that Oracle will throw this exception when the following occur:

  • A PL/SQL routine passes out a ref cursor variable
  • The ref cursor passed out uses a pipelined function
  • The pipelined function itself has a ref cursor as as input parameter

Having replicated the problem on Linux, AIX and WIndows I believe it is not platform specific. It occurs in database versions 11.2 and 12.1. Earlier versions have not been tested and so may be affected too.

The bug appears to be similar to Bug 2968664 : ORA-1001 IN FUNCTION CALLED FROM SQL WHICH FETCHES FROM A REFCURSOR PARAMETER but that bug is noted to be fixed in version

I created a simple test package, imaginatively named pipeline_test, for Oracle Support to replicate the problem. The package contains 3 callable routines:

  • run_test1 – a routine with an OUT ref cursor parameter that matches the criteria noted earlier. Calling this routine throws ORA-01001
  • run_test2 – a routine that opens a ref cursor similar to run_test1 but this routine consumes that cursor rather than passing it out to a caller. This routine does not generate ORA-01001.
  • run_test3 – A routine that calls run_test1 to obtain a ref cursor and then consumes it, similar to run_test2. This routine does generate ORA-01001.

The package and test script are available for download. The setup.sql file creates the package and collection object required by the pipelined function while the run.sql script is a basic SQL*Plus script to call the test routines.

For my situation I was to work around the problem by substituting a collection for the ref cursor that the pipelined function used. I had some luck with hinting the query that used the pipelined function with the MATERIALIZE hint.

I’ll update this post with the details that I get back from Oracle Support. At the moment they have acknowledged that there is a problem and they are investigating.

Update 01 May 2015: Oracle have created a new bug, Bug 20405099, to track this problem. However, access to the bug details on Oracle Support is restricted so you’ll have to take my word on it. 🙂