[Chapter 16] 16.6 Package InitializationChapter 16Packages 16.6 Package Initialization The first time your application makes a reference to a package element, the entire package (in pre-compiled form) is loaded into the SGA of the database instance, making all objects immediately available in memory. You can supplement this automatic instantiation of the package code with the automatic execution of initialization code for the package. This initialization code is contained in the optional initialization section of the package body. The initialization section consists of all statements following the BEGIN statement through the END statement for the entire package body. It is called the initialization section because the statements in this section are executed only once, the first time an object in the package is referenced (a program is called, a cursor is opened, or a variable is used in an assignment, to name a few possibilities). The initialization section initializes the package; it is commonly used to set values for variables declared and referenced in the package. The initialization section is a powerful mechanism: PL/SQL detects automatically when this code should be run. You do not have to explicitly execute the statements, and you can be sure they are run only once. 16.6.1 Drawbacks of Package InitializationThere are some disadvantages to the initialization section. It can be dangerous to have your tool perform actions for you that are not explicitly triggered by a user or developer action. It is harder to trace actions triggered automatically by the tool ("Now where does that variable get set? How did that record get inserted into that table? I don't see it in any of my code!"). Executable statements buried in initialization sections are much harder to maintain; what are the chances that a new developer will think to search the ends of packages for the cause of (or solution to) a problem? In my experience, the initialization section is rarely used. By and large, you spend most of your package development time in the declaration area of the package body, since you use the package mostly to define modules, which can then be called outside of the package. 16.6.2 Use Initialization Section for Complex LogicUse the initialization section only when you need to set the initial values of package elements using rules and complex logic that cannot be handled in the default value syntax for variables. You do not need an initialization section to set the value of the constant earliest_date to today's date. Instead, simply declare the variable with a default value. The straightforward declaration in the package specification looks like this: PACKAGE config_pkg IS earliest_date CONSTANT DATE := SYSDATE; END config_pkg;and should always be used in place of something like this:PACKAGE config_pkg IS earliest_date DATE; END config_pkg; PACKAGE BODY config_pkg /* || This package body only exists to provide an initial value for the || earliest_date variable. This could have been done in the declaration || itself. This is not a justifiable use of an initialization section. */ IS BEGIN earliest_date := SYSDATE; END config_pkg;16.6.3 Side EffectsAvoid setting the values of package global data from other packages within the initialization section. This precaution could prevent havoc in code execution and confusion for maintenance programmers. As the following example demonstrates, keep the initialization section code focused on the current package so it can get its job done. Remember: this code is executed whenever your application first tries to use the package element. You don't want to have your users sitting idle while the package performs some snazzy, expensive setup computations that could be parceled out to different packages or even triggers in the application. PACKAGE BODY company IS BEGIN /* || Initialization section of company_pkg updates the global || package data of another package. This is a no-no! */ SELECT SUM (salary) INTO employee_pkg.max_salary FROM employee; END company;If your initialization requirements do not fit within the above guidelines, you should consider alternatives to the initialization section, such as grouping your startup statements together into a procedure in the package. Give the procedure a name like init_environment. Then, at the appropriate initialization point in your application, call the init_environment procedure to set up your session. 16.6.4 Load Session Data in Initialization SectionA perfectly legitimate use of the initialization section is shown below for the session_pkg. This package contains information about the current user session -- the name of the user, the Oracle account name, user preferences, and so forth. All the package global variables are set the very first time any of the variables are referenced in an application's code. I need to use an initialization section because most (but not all) of the user information is stored in a table. The package specification declares all the variables and sets whatever values it can: PACKAGE session_pkg IS user_name VARCHAR2 (80); user_id VARCHAR2 (10) := USER; show_lov VARCHAR2 (1); show_toolbar VARCHAR2 (1); printer VARCHAR2 (30); END session_pkg;The package body selects the data from the table to fill in the remaining values. If no match is found for the current user, an exception section traps that problem and assigns default values for an "unregistered" user. If any other exception is raised, then RAISE_APPLICATION_ERROR communicates the problem back to the calling program and most likely halts execution of the application, as shown in this example: PACKAGE BODY session_pkg /* || Look, Ma! No declarations in the package body at all! || Just an initialization section to support the specification. */ IS BEGIN SELECT first_name || ' ' || last_name, show_lov_flag, show_toolbar_flag, default_printer INTO user_name, user_id, show_lov, show_toolbar, printer FROM user_config WHERE user_id = USER; EXCEPTION WHEN NO_DATA_FOUND THEN /* No record in config table for this user. */ user_name:= 'NOT REGISTERED'; show_lov:= 'Y'; show_toolbar:= 'Y'; printer:= 'lpt1'; WHEN OTHERS THEN /* Display generic error for unknown problem */ RAISE_APPLICATION_ERROR (-20000, 'Problem obtaining user profile for ' || USER); END session_pkg; 16.5 Package Data17. Calling PL/SQL Functions in SQL Copyright (c) 2000 O'Reilly & Associates. All rights reserved.