Definition:
In Oracle PL/SQL, a PACKAGE is a group of programmatic constructs combined ("packaged") into a single unit. The PL/SQL elements that may be included in a package are cursors, variables, exceptions, PL/SQL records and collection types, procedures, and functions.
A package exists in two parts:
Package Specification: This contains the public constructs and acts as the interface to the package. Note that it contains only constructs prototyping without any logical code.
Package Body: This contains the definition of the constructs prototyped in the spec. It may also contain the private or locally defined program units, which can be used within the scope of the package body only.
Packages demonstrate Encapsulation, Data hiding, Subprogram overloading and Modularization. In terms of performance, packages potentially have both positive and negative effect. Upon the the first call to the package, the entire Package is loaded into the System Global Area (SGA), which may degrade overall performance.
After the package is loaded, however, if any subprogram in the package needs to be invoked, it requires no further disk I/O. Packaged subprograms also stop cascading dependencies, which avoids unnecessary compilation.
Example Syntax:
CREATE [OR REPLACE] PACKAGE [NAME] IS
[PRAGMA]
[PUBLIC CONSTRUCTS]
[SUBPROGRAM1 PROTOTYPE]
[SUBPROGRAM2 PROTOTYPE]
...
[SUBPROGRAM N PROTOTYPE]
END;
CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
[LOCAL CONSTRUCTS]
[CONSTRUCT1 DEFINITION]
[CONSTRUCT2 DEFINITION]
...
[CONSTRUCT N DEFINITION]
[BEGIN...END]
END;
Example Usage:
CREATE OR REPLACE PACKAGE PKG_UTIL IS
PROCEDURE P_ENAME(P_VAR VARCHAR2);
END;
/
Package created.
CREATE OR REPLACE PACKAGE BODY PKG_UTIL IS
PROCEDURE P_ENAME(P_VAR VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_VAR);
END;
END PKG_UTIL;
/
Package body created.
SQL> EXEC PKG_UTIL.P_ENAME('PSOUG');
PSOUG
PL/SQL procedure successfully completed.
Related Links:
Related Code Snippets:
- Create Package - A sample of the two necessary components of an Oracle package.
