Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




PL/SQL Packages
Oracle Tips by Burleson

PL/SQL allows developers to group stored procedures and functions into packages. Ideally, packages are groups of related procedures and functions, each handling a small part of a larger task. Organizing code into packages is quite useful when trying to modularize a system design.

A package specification (or spec) specifies the interface for the procedures and functions that will be contained in the package body. It goes almost without saying that the interfaces for procedures and functions defined in the package spec must be mirrored in the package body.

In addition to grouping related procedures and functions into a cohesive unit, packages also enable developers to define variables and data types that are global to all the procedures and functions contained inside a package (a good example of this is the Build_SUID_Matrix package contained in Appendix D, which defines a global cursor that is used by multiple procedures within the package body). These variables can then be used and reused to store data for reference between modules, allow all the modules to work with the same data, keep track of errors, and perform a multitude of other functions.

A typical package spec might look like the one shown in Listing 2.20.

Listing 2.20 A sample package spec.

  -- Holds the name of the current object inside the package
  vCurrentContext            varchar2 (61);
  YES               CONSTANT char(1) := 'Y';
  NO                CONSTANT char (1) := 'N';
  PROCEDURE Calculate_Semester_GPA (SSN IN     number);
  FUNCTION Get_Student_GPA (SSN IN    number) RETURN number;
END Update_Student;

The variable vCurrentContext is global within the package and can be referenced by both the procedure and function listed (as well as any other procedures or functions that exist in the package body).

Procedures and functions declared in the package spec are public and can be seen by other procedures and functions. However, the package body can also contain procedures and functions that are not declared as part of the package spec; these procedures and functions are private and can only be referenced by procedures and functions within the package body.

Like stored procedures and functions that stand alone, objects inside packages are executed with the privileges of the user who created the package.

Calling a packaged procedure or function is quite simple, as shown in the following code sample:


Update_Student.Calculate_Semester_GPA (ssn => nSSN);


nGPA := Update_Student.Get_Student_GPA (ssn => nSSN);

The only difference between calling a standalone stored procedure and calling a stored procedure inside a package is that the procedure inside the package must be prefaced with the name of the package.

Packages are covered more thoroughly in Chapter 4.


This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter