Oracle Tips by Burleson
Administration of Packages
Under Oracle7, Oracle8, Oracle8i, and
Oracle9i, packages are collections of related functions, variables,
procedures, and external calls to functions and procedures. All of
the functions and procedures for a specific application can be
grouped under one or more packages and handled as units. A package
is loaded into shared memory whenever one of its parts is
referenced. The package stays in memory until the least recently
used (LRU) algorithm determines it hasn’t been recently used. You,
as DBA, can force an object to stay in the SGA by “pinning” it.
(Object pinning is covered in Chapter 13.) This use determination
applies to all database users, not just the originating user.
Packages allow public and private functions,
procedures, and variables. Public functions, procedures, and
variables are named in the package definition and are available to
all users with the right to access the package. Private procedures,
functions, and variables are not referenced in the package
definition, but are contained in the package body. Private
procedures, functions, and variables are only referenced by the
package internal objects. External functions and procedures were new
As hinted at above, the package consists of
two possible parts, a definition and a body, each of which is
created separately. The package definition contains the names of all
public functions, procedures, and variables; the package body
contains the PL/SQL and SQL code for all of the public and private
package objects. In the case of a package that has no private
functions, procedures, or variables, no package body is required.
However, each of the referenced public objects must exist. Not using
private objects allows the DBA and developers to maintain the
individual objects separately instead of as a single entity. If a
package has private objects, it must have a body.
DBA has enforced use of script files to create database functions
and procedures, creating the package body involves simply
concatenating the various scripts together and making minor changes
to the syntax of the statements. By the use of the DBA_SOURCE view,
the DBA can use dynamic SQL to create script listings.
See Code Depot