When is the code between a PL/SQL Package begin/end block executed?

Question

I have the PL/SQL code that is similar to the following snippet:

create or replace
package body MY_PACKAGE as
    type array_type is table of char index by varchar2(1);
    lookup_array array_type;

    function DO_SOMETHING(input nvarchar2) 
    return varchar2 as
    begin
        -- Do something here with lookup_array
    end DO_SOMETHING;

    procedure init_array as
    begin
        lookup_array('A') := 'a';
        lookup_array('B') := 'b';
            -- etc
    end init_array;
begin
    init_array;
end MY_PACKAGE;

It uses a static lookup array to process data supplied to DO_SOMETHING. My question is, when is init_array called and lookup_array loaded into memory? When the package is compiled? When it is called for the first time? Is it called more than once? Is there a better way to implement a static lookup array?

Thanks!

Answer 1

You can refer to this link: http://www.dba-oracle.com/plsql/t_plsql_lookup_tables.htm

"This means the procedure is executed during package initialization. As a result during the lifetime of the session, the procedure is never called manually unless a refresh of the cached table is required."

Answer 2

Q1. "When is init_array called and lookup_array loaded into memory? When the package is compiled? When it is called for the first time? Is it called more than once?"

init_array is called when any function or procedure in the package is called - i.e. "just in time". It will be called whenever the package state is lost (i.e. it may be called more than once per session).

This has implications for the scenario where package state is lost - e.g. when someone recompiles the package. In this scenario, the following sequence occurs:

1. Your session calls do_something - init_array is called first, then do_something executes - your session now has some memory allocated in its PGA to hold the array.

2. My session recompiles the package. At this stage, your session's memory that is allocated for that package is marked "invalid".

3. Your session calls do_something - Oracle detects that your session's memory is marked invalid, and issues ORA-04061 "existing state of xxx has been invalidated".

4. If your session calls do_something again, it proceeds without error - it first calls init_array and then executes do_something.

Q2. "Is there a better way to implement a static lookup array?"

I don't see any real problems with this approach, so long as you take into account the behaviour described above.

In some cases I've seen people put the init call at the start of each function/procedure that needs the array - i.e. whenever do_something is called, it checks to see if it needs to initialise, and if so calls init_array. The advantage of this approach is that you can customise init_array to only initialise the bits that that function/procedure needs - which might be advantageous if init_array does a lot of work - which might help to avoid a big one-time startup overhead for each session.