10.5 How do I…Create a stored package?

Problem

I want to group related stored procedures and functions and let them share variables. I know that stored packages enable me to group related procedures and functions and have performance advantages. How do I create a stored package?

Technique

A stored package is a group of related procedures and functions stored together that share common variables as well as local procedures and functions.

A package contains two separate parts. First, the package specification contains information about the package contents. Procedures, functions, cursors, datatypes, and variables visible to the package user are all declared in the package specification. Second, the package body contains the code for the objects declared in the package specification and objects local to the package. The package specification and package body are compiled separately and stored in the data dictionary as two separate objects. The package body is optional and does not need to be created if the package specification does not contain any procedures or functions. The executable section in the package body is optional. The executable section in a package body is referred to as the initialization section because it contains initialization code that is executed whenever the package is run for the first time in a session. Objects within the package body but not declared in the package specification are treated as local objects in a package and are accessible only to subprograms in the package body.

NOTE - To call a packaged function from a SQL statement, the PRAGMA RESTRICT_REFERENCES must follow the function declaration in the package specification. This is used to assure that the function does not modify data. The syntax used for PRAGMA RESTRICT_REFERENCES is: PRAGMA RESTRICT_REFERENCES (function_name, WNDS);

Additionally, the WNPS, RNDS, RNPS optional arguments separated by commas can also be specified, if necessary. See Table 10.4 to decode the meaning of these arguments.

Table 10.4 PRAGMA RESTRICT_REFERENCES arguments

Argument Description WNDS Writes no database state RNDS Reads no database state WNPS Writes no package state RNPS Reads no package state Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the CHP10_18.SQL file, which contains the code to create a package specification. The results are shown in Figure 10.18.

Line 1 contains the keywords required to create the package specification. Lines 2 through 7 declare the procedures, functions, and variables visible to users of the package. Line 2 declares a procedure and line 3 declares a function. Lines 4 through 6 declare package variables available to procedures and functions within the package, as well as to users of the package.

The package body creates the procedures and functions declared in the package specification. The package can be used only after creating a package body.

2. Run the CHP10_19.SQL file in SQL*Plus. The file contains the code required to create the package body for the package developed in the previous step. The results are shown in Figure 10.19.

Line 1 presents the keywords required to create a package body. The procedures and function declared in the package specification must be contained in the package body, or errors occur. Lines 2 through 5 create the procedure declared in the package specification. Line 4 references a package variable created in the package specification. Package variables are global throughout the package. Lines 6 through 10 create the function declared in the package specification. Line 12 contains code that is run the first time a user executes the package in a session. Line 13 ends the block creating the package body.

The creation of the package body makes the package available to the current user or any account granted an EXECUTE privilege.

3. Run the CHP10_20.SQL file in SQL*Plus. The PL/SQL block in the file calls the procedures and functions in the stored package. The results are shown in Figure 10.20.

Line 2 assigns a value directly to a package variable. Variables defined in the package specification can be used by any user account with EXECUTE privilege on the package. Line 3 calls the package procedure, passing the required variable. Line 4 calls the package function in the DBMS_OUTPUT.PUT_LINE procedure to display the output of the function in SQL*Plus.

The package code displaying a message in SQL*Plus is executed when the package is executed for the first time in a user session. The call to the package procedure assigns a value to a package variable, which the pack-age function returns. This chain of events displays the parameter passed to the package procedure in SQL*Plus. A stored package enables you to share variables between subprograms and to create global variables.

How It Works

As stated earlier, a stored package requires two separate blocks: the package specification and the package body. Step 1 creates a package specification, which contains a procedure, function, and three variables. Variables defined in a package specification are global to the package and the session. Whenever a package variable is changed, the value is retained until it is changed again or until the session is terminated. Step 2 creates the package body. Each of the procedures and functions declared in the package specification are created in the package body. Failure to do so generates an error. The procedure and function both use the package variables created in the package specification. The procedure sets a package variable to a value and the function displays its value, showing how variables can be shared using packages. Step 3 presents a PL/SQL block that uses the stored package and executes it to display the package operation.

Comments

Stored packages let you group common procedures and functions and share variables between them. There are many benefits to packaging stored subprograms. When a stored package is accessed, the entire package is moved into the SGA and prepared to run. Any time you have related procedures that are likely to run together, they should be grouped in a stored package.