Initializing Packages

Packages can contain data structures that persist for your entiresession (this topic is covered in more detail in the later section Section 17.4). The first time your session makes use of a package (whether by calling a program defined in the package, reading or writing a variable, or using a locally declared variable TYPE), Oracle initializes that package. This involves one or all of the following steps:

1. Instantiate any package-level data (such as a number variable or a string constant).

2. Assign default values tovariables and constants as specified in their declarations.

3. Execute a block of code, called the initialization section, that is specifically designed to "initialize" the package, complementing the preceding steps.

Oracle executes these steps just once per session, and not until you need that information (i.e., on the "first touch" of that package).

A package may be reinitialized in a session if that package was recompiled since last use or if the package state for your entire session was reset, as is indicated by the following error: ORA-04068: existing state of packages has been discarded

The initialization section of a package consists of all the statements following the BEGIN statement at the end of the package (and outside any procedure or function's definitions) and through to the END statement for the entire package body. Here is what an initialization section in favorites_pkg might look like:

CREATE OR REPLACE PACKAGE BODY favorites_pkgIS g_most_popular PLS_INTEGER; PROCEDURE show_favorites (list_in IN codes_nt) ... END; FUNCTION most_popular RETURN fav_info_rct ... END; PROCEDURE analyze_favorites (year_in IN INTEGER) ... END; -- Initialization sectionBEGIN g_most_popular := c_chocolate; -- Use new Oracle9i EXTRACT to get year number from SYSDATE! analyze_favorites (EXTRACT (YEAR FROM SYSDATE));END favorites_pkg;

The initialization section is a powerful mechanism: PL/SQL automatically detects when this code should be run. You do not have to explicitly execute the statements, and you can be sure that they are run only once. Why would you use an initialization section? The following sections explore some specific reasons.