ORA-04068: existing state of packages has been discarded

Error ORA-04068: existing state of packages has been discarded comes with package having variables defined in package specification or body. Whenever there are variables/constants defined in package specification or body that package is said to have a state. Upon first calling the package, the "state" is created in memory to hold the values of those variables etc.

This error is received when following changes are performed on package which has state :

•  If an object that the package depends upon e.g. a table is altered in some way e.g. dropped and recreated, then because of the database dependencies, the package takes on an INVALID status.
• If there is some compilation error in package specification or body due to which it's state goes invalid.
•  Value of any variable in the package specification or body is changed and package is compiled, in a different session.
• If we make any modification in package specification or body in a different session. Even if the change doesn't make the state INVALID.

If we compile such a package specification then all the procedures/packages referring procedure/function/variable of that procedure will give this error.

When you next make a call to the package, Oracle looks at the status and sees that it is invalid, then determines that the package has a "state". Because something has altered that the package depended upon, the state is taken as being out of date and is discarded, thus causing the "Package state has been discarded" error message.

NOTE: We will never receive this error if we do not have any variable/constant defined in a package.

Solution:

• Move all constants and variables into a stand-alone package specification and reference those from your initial package. Thus when the status of your original package is invalidated for whatever reason, it has no package state and can be recompiled automatically, however the package containing the variables/constants will not become invalidated as it has no dependencies, so the state that is in memory for that package will remain and can continue to be used.
• Make the package SERIALLY_REUSABLE. No state will be saved for the package and oracle will create new state of the package for every call

Simulation of ORA-04068: existing state of packages has been discarded error:

SQL> set serveroutput on

SQL> create table dependonme (x number)
  2  / 

Table created.

SQL>
SQL> insert into dependonme values (5)
  2  / 

1 row created.

SQL>
SQL> create or replace package mypkg is
 v_statevar number := 5;
procedure myproc;
    end mypkg;
    / 

Package created.

SQL>
SQL> create or replace package body mypkg is
     -- this means my package has a state
  
      procedure myproc is
        myval number;
      begin
        select x
        into myval
        from dependonme;

     -- myval := myval * v_statevar;
      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
    end;
   end mypkg;
 / 

Package body created.

SQL>
SQL> exec mypkg.myproc
My Result is: 25

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

1. Changing the Value of a variable declared in package specification:

I have a variable declared in package specification. This variable is not being used anywhere.
a). In same session: 
SQL>  create or replace package mypkg is
  2      v_statevar number := 15;
  3      procedure myproc;
  4      end mypkg;
  5      /

Package created.

SQL>  exec mypkg.myproc;
My Result is: 5

Change of value in same session didn't make any error.

b). In a second session.
Chaning the value in session 2:
create or replace package mypkg is
   v_statevar number := 25;
   procedure myproc;
   end mypkg;
/

Executing procedure in previos session 1:
SQL>  exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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

NOTE: Just compiling and not changing the value will not give this error. If value of variable is changed in session 2 only then we will receive this error.

2. Changing  the Value of a variable declared in package body:

Similarly if the variable is declared in Package Body rather than in specification and if we make changes to that variable in package body in a different session, then again we will receive this error.

SQL> create or replace package mypkg is
  2
  3         procedure myproc;
  4          end mypkg;
  5      /

Package created.

SQL> create or replace package body mypkg is
  2        v_statevar number := 105;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

SQL>  exec mypkg.myproc;
My Result is: 525

PL/SQL procedure successfully completed.

SQL> create or replace package body mypkg is
  2        v_statevar number := 205;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

SQL> exec mypkg.myproc;
My Result is: 1025

PL/SQL procedure successfully completed.

Session 2: Compiling the package body with different value set for variable in it, in session 2 will generate this error.

SQL>    create or replace package body mypkg is
  2        v_statevar number := 305;
  3
  4        procedure myproc is
  5          myval number;
  6        begin
  7          select x
  8          into myval
  9          from dependonme;
 10
 11        myval := myval * v_statevar;
 12        DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 13      end;
 14     end mypkg;
 15   /

Package body created.

Session 1: Executing the dependent procedure again.

SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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


3. Making changes to object on which procedure in package body is dependend

a) Droping the table used in procedure body which maked the package body INVALID. These operation can be performed in same session.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

SQL> drop table dependonme;

Table dropped.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        INVALID

SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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

However if we did not have any variable defined then we would got the same error which is recieved on second call of this procedure.

SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

*
ERROR at line 1:
ORA-04063: package body "SCOTT.MYPKG" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
ORA-06512: at line 1


NOTE: 
 Testing this same scenario but by declaring the variable in specificaiton rather then body would give the same result. So variable defined anywhere in package specification or body, when state of body get invalid, we will receieve this error.


4. Making changes to logic of procedure in package body 

b). In Same session. No error will be generated if we change the logic in procedure.

SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select sal
  6          into myval
  7          from emp
  8     where empno = 7369;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000

PL/SQL procedure successfully completed.

SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select x
  6          into myval
  7          from dependonme;
  8
  9         myval := myval * mypkg.v_statevar;
 10         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11       end;
 12     end mypkg;
 13   /

Package body created.

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 500

PL/SQL procedure successfully completed.

b). Changing the package logic in a different session will generate this error.

Session 1:
-----------
SQL> exec mypkg.myproc;
My Result is: 500

PL/SQL procedure successfully completed.

Session 2:
-----------
SQL>  create or replace package body mypkg is
  2           procedure myproc is
  3          myval number;
  4        begin
  5          select sal
  6          into myval
  7          from emp
  8     where empno = 7369;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

Session 1:
-----------
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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

SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 80000

PL/SQL procedure successfully completed.


5. Checking the procedure specification variable change with two session
It has been observed that whenever there is a change in variable value in package specification, the package body got invalid and all the session who calls the procedure in this package starts showing "ORA-04068: existing state of packages has been discarded" error.

Session 1:
-----------
SQL> create or replace package mypkg is
  2   v_statevar number := 40;
  3   procedure myproc;
  4   end mypkg;
  5  /

Package created.

SQL> create or replace package body mypkg is
  2        procedure myproc is
  3          myval number;
  4        begin
  5          select x
  6          into myval
  7          from dependonme;
  8
  9       --  myval := myval * mypkg.v_statevar;
 10         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11       end;
 12     end mypkg;
 13   /

Package body created.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        VALID

Session 2:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5

PL/SQL procedure successfully completed.

Session 3:
-----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
My Result is: 5

PL/SQL procedure successfully completed.

Session 1:
----------
SQL>  create or replace package mypkg is
  2    v_statevar number := 40;
  3    procedure myproc;
  4    end mypkg;
  5   /

Package created.

SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG';

OBJECT_NAME                    OBJECT_TYPE         STATUS
------------------------------ ------------------- -------
MYPKG                          PACKAGE             VALID
MYPKG                          PACKAGE BODY        INVALID

Session 2
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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

Session 3
----------
SQL> set serveroutput on
SQL> exec mypkg.myproc;
BEGIN mypkg.myproc; END;

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

6. Package Body Depended on Package Specification
This example shows package body is dependent on package specification and any change in it will make package body invalid. However we do not receive error while referring that package body procedure in same session because Package body is complied when we call the procedure.

SQL>  create or replace package mypkg is
  2       v_statevar number := 40;
  3        end mypkg;
  4      /

Package created.

SQL>  create or replace package body mypkg is
  2            procedure myproc is
  3              myval number;
  4            begin
  5              select x
  6              into myval
  7              from dependonme;
  8  
  9           --  myval := myval * mypkg.v_statevar;
 10          DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 11        end;
 12      end mypkg;
 13    /

Package body created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS
----------                    ------- 
MYPKG                          VALID                               MYPKG                          VALID                                            
SQL>  create or replace package mypkg is
  2       v_statevar number := 140;
  3        end mypkg;
  4      /

Package created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                               
--------------                -------  
MYPKG                          VALID     
MYPKG                          INVALID                                          
SQL>  create or replace package body mypkg is
  2            procedure myproc is
  3  
  4            begin
  5                 DBMS_OUTPUT.PUT_LINE('My Result is: ');
  6        end;
  7      end mypkg;
  8    /

Package body created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                               
-----------------             -------                               
MYPKG                          VALID                               
MYPKG                          VALID                                            
SQL>  create or replace package mypkg is
  2       v_statevar number := 240;
  3        end mypkg;
  4      /

Package created.

SQL> select object_name, status from user_objects where object_name='MYPKG';

OBJECT_NAME                    STATUS                             
---------------               -------                               
MYPKG                          VALID                             MYPKG                          INVALID                                          

Conclusion:
"ORA-04068: existing state of packages has been discarded" error is received only on a package which has variable defined in its package specification or body. We will get this error in following situaions:

•  If value of variable defined in specification or body, is modified in a different session.
•  Package specification or body got INVALID.
•  Package specification or body is modified in a different session.

Solution:

With pragma SERIALLY_REUSABLE package

Making the package SERIALLY_REUSABLE will avoid this error.
Session 1:
-----------
SQL> create or replace package mypkg is
  2  pragma SERIALLY_REUSABLE;
  3  v_statevar number := 5;
  4  procedure myproc;
  5  end mypkg;
  6  /

Package created.

SQL> create or replace package body mypkg is
  2  pragma SERIALLY_REUSABLE;
  3           procedure myproc is
  4          myval number;
  5        begin
  6          select x
  7          into myval
  8          from dependonme;
  9
 10         myval := myval * mypkg.v_statevar;
 11         DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
 12       end;
 13     end mypkg;
 14   /

Package body created.

SQL> exec mypkg.myproc;
My Result is: 25

PL/SQL procedure successfully completed.

Session 2: Modifying the variable value

SQL>  create or replace package mypkg is
  2   pragma SERIALLY_REUSABLE;
  3   v_statevar number := 50;
  4   procedure myproc;
  5   end mypkg;
  6   /

Package created.

Session 1:

SQL> exec mypkg.myproc;
My Result is: 250

PL/SQL procedure successfully completed.

Why to Use Oracle Packages - Uses/Advantages

Most PL/SQL-based applications consist of hundreds of thousands of lines of code—and many likely run into the millions—that meet detailed, ever-changing user requirements. Business logic is implemented primarily in procedures and functions, but PL/SQL developers need to decide where to put those procedures and functions. In this article, I explore how to use packages to organize and expose an application’s functionality in ways that make it easier to build, maintain, and optimize the code base.

A package is a grouping or packaging of elements of PL/SQL code into a named program unit, stored in the database. Packages provide a structure (both logically and physically) in which you can organize your programs and other PL/SQL elements such as cursors, TYPEs, and variables. They also offer significant functionality, including the ability to hide logic and data from view and to define and manipulate “global” or session-persistent data.

A package always has a specification, which defines the package items that can be referenced from outside the package. Most packages also have a body, which implements any subprograms (procedures and functions) in the package. The body can also contain private items, such as package-level variables and types, which can be referenced only from within the package. A package body can also have an initialization section, which developers use to implement complex setup steps and validation for a package.

Why Packages?

The package is a powerful and important element of the PL/SQL language. It should, in fact, be the fundamental building block of any PL/SQL-based application. What makes the package so powerful and important? Consider its advantages: 

Group related functionality. One of the most fundamental benefits of a package is that it can group related functionality and then make that functionality available through an API. Rather than having to look through hundreds of thousands of separate procedures and functions, you find the package that contains the functionality you need (which should be easily identifiable by its name) and then drill down to the subprograms you need within it. And when you need to add new programs to your application, the package offers a “home” for that functionality. Without packages, developers have a much harder time understanding and maintaining their code.

Hide implementation details. With a package, you choose what to put into the specification for everyone to see and use and what to put into the body, which is visible only to the owner of the package. This ability to hide implementation details has two critical benefits: First, you can change the implementation without changing the specification. This means that all the programs that call procedures and functions in the package do not have to change. This feature greatly increases the flexibility you have for enhancing your code while limiting the impact on the application. Second, you can hide implementation details you don’t want users of the package to be aware of.

Improve performance. The first time you invoke an element in a package, Oracle Database loads the entire package into memory. Subsequent references to other elements in the same package require no additional disk I/O. Also, package-level variables can be used as a session-level cache, offering a variety of ways to reduce the time it takes to access data.

Minimize program unit recompilation. External programs (not defined in the package) can call only subprograms listed in the specification. If you change and recompile the package body, those external programs will not be invalidated. Minimizing the need to recompile code is a critical factor in administering large bodies of application logic.

There are many reasons to use packages, but first you need to know how to build them and take advantage of their unique features. In this article, I start with a simple package that demonstrates the power of this application building block. Then I explore two key features—package-level data and overloading—in more detail.

A Simple Package Example

Suppose that my employees table is defined as follows: 

SQL> desc employees

Name             Type
————————————     —————————————
EMPLOYEE_ID      NUMBER(38)
FIRST_NAME       VARCHAR2(30)
LAST_NAME        VARCHAR2(50)

I need to write a procedure, process_employee, that retrieves the “full name” (last name comma first name) of an employee and then performs other tasks. That’s easy enough to do in PL/SQL, as shown in Listing 1.

Code Listing 1: The process_employee procedure 

CREATE OR REPLACE PROCEDURE process_employee (
   employee_id_in IN employees.employee_id%TYPE)
IS
   l_fullname VARCHAR2(100);
BEGIN
   SELECT last_name || ',' || first_name
     INTO l_fullname
     FROM employees
    WHERE employee_id = employee_id_in;
    ...
END; 

Although this code may work fine today, it contains several “time bombs” that could cause problems in months or years to come, including the following: 

• The length of the l_fullname variable is fixed at 100. I would have preferred to use %TYPE to declare the variable, but the full name is a derived value and there is no column with which I can attach %TYPE. So I just figured that 100 would be “big enough.” And it will be—until the DBA increases the maximum length of either of these columns.
• The formula that produces a “full name” has been placed right inside this procedure. And there’s a good chance that the same concatenation appears over and over again in my code. Why not? It’s such a simple formula! Ah, but what do I do when the users call me with some news: “We’ve changed our minds. We want to see the names as first name space last name in all reports and messages.” Uh-oh. Now I have to scour my code for all the last-comma-first constructions.
• Finally, because it is so easy to write SQL inside PL/SQL programs, the query itself will likely appear in many places in my application. Repeating SQL statements can make it very hard to optimize the performance of my code and keep up with changing user requirements.

It would be wonderful if I could write this procedure in a way that avoids all the hard-coding and repetition: write these things once—one definition of a “full name” datatype, one representation of the formula, and one version of the query—and then call them wherever they are needed. A package offers the perfect repository for such single-source implementations.

Consider the package specification in Listing 2.

Code Listing 2: The employee_pkg specification 

1    CREATE OR REPLACE PACKAGE employee_pkg
2    AS
3        SUBTYPE fullname_t IS VARCHAR2 (100);
4     
5        FUNCTION fullname (
6           last_in  employees.last_name%TYPE,
7           first_in  employees.first_name%TYPE)
8           RETURN fullname_t;
9     
10        FUNCTION fullname (
11           employee_id_in IN employees.employee_id%TYPE)
12           RETURN fullname_t;
13    END employee_pkg;

The employee_pkg specification essentially lists the different code elements that will be available for use outside the package. In line 3, I declare my own datatype called fullname_t that is a subtype of the VARCHAR2 type. In lines 5–8, I define a function called fullname that accepts a last name and a first name and returns the full name. (Note that the formula itself—last comma first—is not visible in the package specification. That’s a good thing, as you will soon see.) In lines 10–12, I declare a second function, also called fullname. This version accepts a primary key for an employee and returns the full name for that employee. Having two functions with the same name is an example of overloading, which I explore in more detail below.

Even though I have not yet implemented the two functions in this package specification, I can now revisit the process_employee procedure and see how I can use these packaged elements, which are referenced in the form package_name.element_name. As shown in Listing 3, I use the subtype to declare the l_name variable, and I call the function instead of writing a query directly inside the function.

Code Listing 3: Block calling the employee_pkg package 

CREATE OR REPLACE PROCEDURE process_employee (
   employee_id_in IN employees.employee_id%TYPE)
IS
   l_name employee_pkg.fullname_t;
   employee_id_in   employees.employee_id%TYPE := 1;
BEGIN
   l_name := employee_pkg.fullname (employee_id_in);
   ...
END;

As a result, the code implementing the formula and the SQL query have been moved out of my procedure; the resulting code is cleaner and simpler. The same will be true of all the other programs that need to work with the full name of an employee.

Best of all, if I need to change the formula for fullname or expand the maximum size of the fullname_t datatype, I will simply make the appropriate change to the package and recompile it. But all the programs that use the packaged elements will not need to change.

Listing 4 is the implementation of the body of employee_pkg.

Code Listing 4: The employee_pkg package body 

1    CREATE OR REPLACE PACKAGE BODY employee_pkg
2    AS
3       FUNCTION fullname (
4          last_in employees.last_name%TYPE,
5          first_in employees.first_name%TYPE
6       )
7          RETURN fullname_t
8       IS
9       BEGIN
10         RETURN last_in || ', ' || first_in;
11      END;
12     
13      FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
14         RETURN fullname_t
15      IS
16         l_fullname fullname_t;
17      BEGIN
18         SELECT fullname (last_name, first_name) INTO l_fullname
19           FROM employees
20          WHERE employee_id = employee_id_in;
21     
22         RETURN l_fullname;
23       END;
24    END employee_pkg;

Lines 3–11 implement the first fullname function, which is really nothing more than a wrapper around the last-comma-first formula. Lines 13–23 implement the second fullname function, which gets the first and last name for the specified employee and combines them by calling the first fullname function.

So what happens when my users call and say, “First-space-last, please!”? I will not groan and work late into the night, hunting down occurrences of “|| ‘, ‘ ||” in my code. Instead, I will change the implementation of my employee_pkg.fullname function in about five seconds flat, recompile the package body, and astound my users by announcing that they are ready to go.

And what happens when I upgrade to Oracle Database 11g and discover the amazing PL/SQL function result cache? I simply add the RESULT_CACHE clause to the function headers, recompile the package, and then enjoy the greatly reduced CPU cycles needed to repeatedly retrieve the full name for the same employee.

Ah, the power of hiding implementation details in packages!

Package-Level Data

Package-level data consists of variables and constants that are defined at the package level—that is, not within a particular function or procedure in the package. The following package specification, for example, declares one variable and one constant at the package level: 

CREATE OR REPLACE PACKAGE plsql_limits
IS
   c_varchar2_length CONSTANT 
      PLS_INTEGER := 32767;
   g_start_time PLS_INTEGER;
END;

When you declare a variable within a subprogram or a block, it is a local variable. Its scope is restricted to that block, and when the block terminates, the memory for that variable is released.

The scope of package data, however, is not an individual program or block, but rather the package as a whole. In the PL/SQL runtime architecture, package data structures persist (hold their values) for the duration of a session (rather than the duration of execution of a particular program).

If package data is declared inside the package body, that data will persist for the session but can be accessed only by elements defined in the package itself (private data). If, on the other hand, package data is declared inside the package specification, that data will persist for the session and will be directly accessible (for both reading and modifying) by any program that has EXECUTE authority on that package (public data).

Let’s take a look at how package-level data can be of use to you. The DBMS_UTILITY package offers a function named GET_CPU_TIME that can be used to calculate the elapsed time of your code with hundredth-of-a-second precision. The anonymous block in Listing 5 demonstrates how to use this function to determine how long it takes to do nothing 10,000 times.

Code Listing 5: DBMS_UTILITY.GET_CPU_TIME measures 

DECLARE
   l_start   PLS_INTEGER;
BEGIN
   /* Get and save the starting time. */
   l_start := DBMS_UTILITY.get_cpu_time;

   /* Run your code. */
   FOR indx IN 1 .. 10000
   LOOP
      NULL;
   END LOOP;

   /* Subtract starting time from current time. */
   DBMS_OUTPUT.put_line (
      DBMS_UTILITY.get_cpu_time - l_start);
END;
/

That’s simple enough, but it also means that a developer who wants to take advantage of this utility has to declare a local variable to hold the starting time, remember and write the formula (end minus start), and then display the value.

Surely most developers have better ways to spend their time. Perhaps a package could help. Consider, for example, the timer package (timer_pkg) in Listing 6.

Code Listing 6: The timer_pkg package 

CREATE OR REPLACE PACKAGE timer_pkg
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);
END timer_pkg;
/

CREATE OR REPLACE PACKAGE BODY timer_pkg
IS
   g_start_time   NUMBER := NULL;

   PROCEDURE start_timer
   IS
   BEGIN
      g_start_time := DBMS_UTILITY.get_cpu_time;
   END;

   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            message_in
         || ': '
         || TO_CHAR (DBMS_UTILITY.get_cpu_time - g_start_time));

      start_timer;
   END;
END timer_pkg;
/ 

Before I explain the timer_pkg implementation, let’s look at how the package makes it easier for developers to use the GET_CPU_TIME utility. My previous anonymous block, in Listing 5, now looks like this: 

BEGIN
   timer_pkg.start_timer;
   FOR indx IN 1 .. 10000
   LOOP
      NULL;
   END LOOP;
   timer_pkg.show_elapsed ('10000 Nothings');
END;
/

No need to declare a local variable, no need to understand the way the GET_CPU_TIME function works (or even that it exists). Just call the appropriate subprograms and see how well your code is performing. And the timer_pkg package makes it easy to display a message with the timing and automatically reset the start time after showing the elapsed time.

The timer_pkg package relies on a single package-level variable, g_start_time, to remember the starting time between calls to subprograms in the package. This is possible only because of the session-level persistence of package data.

This timer package is also a nice example of an API, a set of subprograms that offers an interface to underlying functionality. The package specification lists the tasks you can perform, and the package body hides the way the timer is implemented, taking advantage, in this case, of the GET_CPU_TIME function. If someday Oracle comes up with a better mechanism for doing this kind of elapsed time analysis, I will have to change only the package body and all of my users will be able to instantly take advantage of the new and improved technique.

Subprogram Overloading

Overloading occurs when you have two or more subprograms with the same name in a program unit. You can overload subprograms in any declaration section; that is, this feature is not available only in packages. You will, however, find that almost all overloading occurs inside packages. And that’s because overloading is most helpful in APIs.

Overloading can greatly simplify your life and the lives of other developers. When building an API, developers often find that they need to execute the same kind of logic but for different types of data or various scenarios that require different inputs. Without overloading, you would have to come up with a different name for each variation and users of your code would need to remember which is which

With overloading, the users of your package need to know only the one name that describes the functionality needed. The compiler then does the rest of the work: it analyzes the arguments passed to the subprogram and matches them up with an overloading.

Let’s take a look at how you can use overloading to make it easier to display information onscreen from within a PL/SQL block.

Oracle Database includes the DBMS_OUTPUT.PUT_LINE procedure to send text to system output. This procedure is not overloaded. There is a single implementation accepting a single VARCHAR2 value.

So if you pass an expression to DBMS_OUTPUT.PUT_LINE that evaluates to a VARCHAR2 value, it will work just fine. Because numbers, for example, are implicitly converted to a string, the following statement will result in the display of “100” onscreen: 

BEGIN
   DBMS_OUTPUT.PUT_LINE (100);
END;

 
If, on the other hand, you try to display a Boolean expression, you will get an error message: 

SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE (TRUE);
  3  END;
  4  /
   DBMS_OUTPUT.PUT_LINE (TRUE);
   *
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of 
arguments in call to ‘PUT_LINE’

 

This happens because PL/SQL does not support implicit conversions from Boolean to VARCHAR2 datatypes.

As a result, many developers write code like this when they want to display a Boolean: 

IF l_student_is_registered
THEN
   DBMS_OUTPUT.PUT_LINE ('TRUE');
ELSE
   DBMS_OUTPUT.PUT_LINE ('FALSE');
END IF;

Now there’s a recipe for reducing your productivity and introducing bloat to your code. Overloading to the rescue!

I would like to make it easy to display Boolean values and date values that include the time stamp (an implicit conversion of a date to a string using the default format mask ignores the time).

Without overloading, I need to come up with different procedure names for each type of data, as shown in Listing 7.

Code Listing 7: The my_output package without overloading 
CREATE OR REPLACE PACKAGE my_output
IS
   PROCEDURE put_string (value_in IN VARCHAR2);

   PROCEDURE put_boolean (value_in IN BOOLEAN);

   PROCEDURE put_date (
      value_in   IN DATE,
      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/


 
It’s not so hard to come up with the procedure names, but it is much more effort for a user of this functionality to remember or look up the correct name. Is the procedure for displaying a date named “put_date” or “put_datetime” or maybe “put_timestamp”?

With overloading, however, my package specification procedures are easy to remember or look up, as shown in Listing 8.

Code Listing 8: The my_output package with overloading 
CREATE OR REPLACE PACKAGE my_output
IS
   PROCEDURE put_line (value_in IN VARCHAR2);

   PROCEDURE put_line (value_in IN BOOLEAN);

   PROCEDURE put_line (
      value_in   IN DATE,
      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');
END my_output;
/

The listing includes three procedures, all with the same name, because they all do the same thing: display data. All I, as a user, have to remember is that I call a procedure to “put/display a line of data.” I pass it the data I want to display, and the PL/SQL compiler automatically figures out which of the overloadings is the right one.

I like it when smart software takes care of the “small details” so I can stay focused on implementing user requirements.

There are many rules and restrictions for overloading, which are beyond the scope of this article. Here are a few key points to keep in mind as you start building your own packages: 

• Overloadings almost always involve subprograms with different numbers and/or types of parameters, as you saw in the my_output package in Listing 8.
• You can overload a function and a procedure with the same parameter list.
• Make sure the datatypes of the parameters across overloadings are not too similar; you cannot, for example, overload two procedures whose only difference is that one uses a CHAR parameter whereas the other uses a VARCHAR2 parameter.

When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line, because you and others will find it much easier to use your programs.

The Building Blocks of Applications

It is possible to build applications in PL/SQL without using packages. You could, instead, create thousands of schema-level functions and procedures. If you take this path, however, I guarantee that you will come to regret it. You will soon discover that it is difficult to find code and even more challenging to change existing code in your applications.

I recommend that, instead, you always put your procedures and functions inside packages and move as much of the code in triggers and object types into packages as possible. You will then find your sprawling code base easier to manage, whether it is a matter of analyzing the impact of a proposed change or applying a fix with minimum impact on existing code.

Take full advantage of overloading to simplify the API you present to the users of the package. Explore ways you can use package-level data.

Package State and ORA-04068

When a package has at least one constant or variable declared at the package level, that package is said to have state or to be stateful. When a session uses a stateful package, Oracle Database uses session-specific PGA (Process Global Area) memory to store that session’s values for all package-level data.

If a stateful package is recompiled, all sessions that have used the package will fail with the ORA-04068 error the next time they try to access the package. That’s because the state of the package in each session is now out of date, so the package must be reinitialized.

Furthermore, once the ORA-04068 error has been raised, the state of all packages in the session—including, for example, DBMS_OUTPUT—is uninitialized. This usually means that the user must disconnect from the session and reconnect in order to use the application successfully.

The potential for ORA-04068 errors has meant that when IT departments must upgrade the code in their application, they need to first have all users log out from the application. In the 24/7 world of the internet, however, this is not something many companies can afford to do.

So in Oracle Database 11g Release 2, Oracle offers the Edition-Based Redefinition feature. With this feature, organizations can now “hot patch” their applications, even if doing so involves changes to table structures as well as changes to code. Users no longer need to disconnect while upgrades are taking place.

Source: http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13plsql-1872456.html