Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks