ss

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 with Oracle8.

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.

If the 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