|Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)
Part Number E17126-03
This chapter explains how to bundle related PL/SQL code and data into a package, whose contents are available to many applications.
Oracle Database PL/SQL Packages and Types Reference for information about the many product-specific packages that Oracle Database supplies
"DROP PACKAGE Statement", which drops a stored package from the database
A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. You can think of a package as an application.
A package always has a specification, which declares the public items that can be referenced from outside the package. You can think of the package specification as the application programming interface (API). For more information about the package specification, see "Package Specification".
If the public items include cursors or subprograms, then the package must also have a body. The body must define queries for public cursors and code for public subprograms. The body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package. Finally, the body can have an initialization part, whose statements initialize variables and do other one-time setup steps. You can change the body without changing the specification or the references to the public items; therefore, you can think of the package body as a black box. For more information about the package body, see "Package Body".
In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram by using a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts. For details, see "Function Declaration and Definition" and "Procedure Declaration and Definition".
AUTHID clause of the package specification determines whether the subprograms and cursors in the package run with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker. For more information, see "Invoker's Rights and Definer's Rights (AUTHID Property)".
Packages let you encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions in named PL/SQL modules. You can make each package easy to understand, and make the interfaces between packages simple, clear, and well defined. This practice aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specifications. You can code and compile specifications without their bodies. Next, you can compile standalone stored subprograms that reference the packages. You need not fully define the package bodies until you are ready to complete the application.
Packages let you share the your interface information in the package specification, and hide the implementation details in the package body. Hiding the implementation details in the body has these advantages:
You can change the implementation details without affecting the application interface.
Application users cannot develop code that depends on implementation details that you might want to change.
Package public variables and cursors can persist for the life of a session. They can be shared by all subprograms that run in the environment. They let you maintain data across transactions without storing it in the database. (For the situations in which package public variables and cursors do not persist for the life of a session, see "Package State".)
The first time you invoke a package subprogram, Oracle Database loads the whole package into memory. Subsequent invocations of other subprograms in same the package require no disk I/O.
Packages prevent cascading dependencies and unnecessary recompiling. For example, if you change the body of a package function, Oracle Database does not recompile other subprograms that invoke the function, because these subprograms depend only on the parameters and return value that are declared in the specification.
A package specification declares public items. The scope of a public item is the schema of the package. A public item is visible everywhere in the schema. To reference a public item that is in scope but not visible, qualify it with the package name. (For information about scope, visibility, and qualification, see "Scope and Visibility of Identifiers".)
Each public item declaration has all information that you need to use the item. For example, suppose that a package specification declares the function
factorial this way:
FUNCTION factorial (n INTEGER) RETURN INTEGER; -- returns n!
The declaration shows that
factorial needs one argument of type
INTEGER and returns a value of type
INTEGER, which is all you must know to invoke
factorial. You need not know how
factorial is implemented (for example, whether it is iterative or recursive).
Types, variables, constants, subprograms, cursors, and exceptions used by multiple subprograms
A type defined in a package specification is either a PL/SQL user-defined subtype (described in "User-Defined PL/SQL Subtypes") or a PL/SQL composite type (described in Chapter 5, "PL/SQL Collections and Records").
Associative array types of standalone stored subprogram parameters
You cannot declare an associative array type at schema level. Therefore, to pass an associative array variable as a parameter to a standalone stored subprogram, you must declare the type of that variable in a package specification. Doing so makes the type available to both the invoked subprogram (which declares a formal parameter of that type) and to the invoking subprogram or anonymous block ( which declares a variable of that type). See Example 10-2.
Variables that must remain available between subprogram invocations in the same session
Subprograms that read and write public variables ("get" and "set" subprograms)
Provide these subprograms to discourage package users from reading and writing public variables directly.
Subprograms that invoke each other
You need not worry about compilation order for package subprograms, as you must for standalone stored subprograms that invoke each other.
Overloaded subprograms are variations of the same subprogram. That is, they have the same name but different formal parameters. For more information about them, see "Overloaded Subprograms".
To create a package specification, use the "CREATE PACKAGE Statement".
In Example 10-1, the specification for the package
trans_data declares two public types and three public variables.
Example 10-1 Simple Package Specification
CREATE OR REPLACE PACKAGE trans_data AS TYPE TimeRec IS RECORD ( minutes SMALLINT, hours SMALLINT); TYPE TransRec IS RECORD ( category VARCHAR2(10), account INT, amount REAL, time_of TimeRec); minimum_balance CONSTANT REAL := 10.00; number_processed INT; insufficient_funds EXCEPTION; END trans_data; /
In Example 10-2, the specification for the package
aa_pkg declares an associative array type,
aa_type. Then, the standalone stored procedure
print_aa declares a formal parameter of type
aa_type. Next, the anonymous block declares a variable of type
aa_type, populates it, and passes it to the procedure
print_aa, which prints it.
Example 10-2 Passing Associative Array to Standalone Subprogram
CREATE OR REPLACE PACKAGE aa_pkg IS TYPE aa_type IS TABLE OF INTEGER INDEX BY VARCHAR2(15); END; / CREATE OR REPLACE PROCEDURE print_aa ( aa aa_pkg.aa_type ) IS i VARCHAR2(15); BEGIN i := aa.FIRST; WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE (aa(i) || ' ' || i); i := aa.NEXT(i); END LOOP; END; / DECLARE aa_var aa_pkg.aa_type; BEGIN aa_var('zero') := 0; aa_var('one') := 1; aa_var('two') := 2; print_aa(aa_var); END; /
1 one 2 two 0 zero
If a package specification declares cursors or subprograms, then a package body is required; otherwise, it is optional. The package body and package specification must be in the same schema.
Every cursor or subprogram declaration in the package specification must have a corresponding definition in the package body. The headings of corresponding subprogram declarations and definitions must match word for word, except for white space.
To create a package body, use the "CREATE PACKAGE BODY Statement".
In Example 10-3, the headings of the corresponding subprogram declaration and definition do not match word for word; therefore, PL/SQL raises an exception, even though
Example 10-3 Matching Package Specification and Body
CREATE PACKAGE emp_bonus AS PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE); END emp_bonus; / CREATE PACKAGE BODY emp_bonus AS -- DATE does not match employees.hire_date%TYPE PROCEDURE calc_bonus (date_hired DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE ('Employees hired on ' || date_hired || ' get bonus.'); END; END emp_bonus; /
Warning: Package Body created with compilation errors.
Show errors (in SQL*Plus):
Errors for PACKAGE BODY EMP_BONUS: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/13 PLS-00323: subprogram or cursor 'CALC_BONUS' is declared in a package specification and must be defined in the package body
CREATE OR REPLACE PACKAGE BODY emp_bonus AS PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS BEGIN DBMS_OUTPUT.PUT_LINE ('Employees hired on ' || date_hired || ' get bonus.'); END; END emp_bonus; /
Package body created.
The cursors and subprograms declared in the package specification and defined in the package body are public items that can be referenced from outside the package. The package body can also declare and define private items that cannot be referenced from outside the package, but are necessary for the internal workings of the package.
Finally, the body can have an initialization part, whose statements initialize public variables and do other one-time setup steps. The initialization part runs only the first time the package is referenced. The initialization part can include an exception handler.
You can change the package body without changing the specification or the references to the public items.
When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.
Assigning initial values to public constants
Assigning initial values to public variables whose declarations specify them
Executing the initialization part of the package body
The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.
Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. The package state persists for the life of a session, except in these situations:
The package is
For details, see "SERIALLY_REUSABLE Packages".
The package body is recompiled.
If the body of an instantiated, stateful package is recompiled (either explicitly, with the "ALTER PACKAGE Statement", or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.
After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost. (For information about initialization, see "Package Instantiation and Initialization".)
Any of the session's instantiated packages are invalidated and revalidated.
All of a session's package instantiations (including package states) can be lost if any of the session's instantiated packages are invalidated and revalidated. For information about invalidation and revalidation of schema objects, see Oracle Database Advanced Application Developer's Guide.
As of Oracle Database 11g Release 2 (220.127.116.11), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.
A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the PL/SQL optimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see "PL/SQL Optimizer".
If a package is not
SERIALLY_REUSABLE, its package state is stored in the user global area (UGA) for each user. Therefore, the amount of UGA memory needed increases linearly with the number of users, limiting scalability. The package state can persist for the life of a session, locking UGA memory until the session ends. In some applications, such as Oracle Office, a typical session lasts several days.
If a package is
SERIALLY_REUSABLE, its package state is stored in a work area in a small pool in the system global area (SGA). The package state persists only for the life of a server call. After the server call, the work area returns to the pool. If a subsequent server call references the package, then Oracle Database reuses an instantiation from the pool. Reusing an instantiation re-initializes it; therefore, changes made to the package state in previous server calls are invisible. (For information about initialization, see "Package Instantiation and Initialization".)
Note:Trying to access a
SERIALLY_REUSABLEpackage from a database trigger, or from a PL/SQL subprogram invoked by a SQL statement, raises an error.
To create a
SERIALLY_REUSABLE package, include the
SERIALLY_REUSABLE pragma in the package specification and, if it exists, the package body.
Example 10-4 creates two very simple
SERIALLY_REUSABLE packages, one with only a specification, and one with both a specification and a body.
See Also:"SERIALLY_REUSABLE Pragma"
Example 10-4 Creating SERIALLY_REUSABLE Packages
-- Create bodiless SERIALLY_REUSABLE package: CREATE OR REPLACE PACKAGE bodiless_pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END; / -- Create SERIALLY_REUSABLE package with specification and body: CREATE OR REPLACE PACKAGE pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END; / CREATE OR REPLACE PACKAGE BODY pkg IS PRAGMA SERIALLY_REUSABLE; BEGIN n := 5; END; /
SERIALLY_REUSABLE package, the work unit is a server call. You must use its public variables only within the work unit.
Note:If you make a mistake and depend on the value of a public variable that was set in a previous work unit, then your program can fail. PL/SQL cannot check for such cases.
In Example 10-5, the bodiless packages
pkg_sr are the same, except that
pkg is not. Each package declares public variable
n with initial value 5. Then, an anonymous block changes the value of each variable to 10. Next, another anonymous block prints the value of each variable. The value of
n is still 10, because the state of
pkg persists for the life of the session. The value of
n is 5, because the state of
pkg_sr persists only for the life of the server call.
Example 10-5 Effect of SERIALLY_REUSABLE Pragma
CREATE OR REPLACE PACKAGE pkg IS n NUMBER := 5; END pkg; / CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; n NUMBER := 5; END sr_pkg; / BEGIN pkg.n := 10; sr_pkg.n := 10; END; / BEGIN DBMS_OUTPUT.PUT_LINE('pkg.n: ' || pkg.n); DBMS_OUTPUT.PUT_LINE('sr_pkg.n: ' || sr_pkg.n); END; /
pkg.n: 10 sr_pkg.n: 5
After the work unit (server call) of a
SERIALLY_REUSABLE package completes, Oracle Database does the following:
An explicit cursor in a
SERIALLY_REUSABLE package remains open until either you close it or its work unit (server call) ends. To re-open the cursor, you must make a new server call. A server call can be different from a subprogram invocation, as Example 10-6 shows.
In contrast, an explicit cursor in a package that is not
SERIALLY_REUSABLE remains open until you either close it or disconnect from the session.
Example 10-6 Cursor in SERIALLY_REUSABLE Package Open at Call Boundary
DROP TABLE people; CREATE TABLE people (name VARCHAR2(20)); INSERT INTO people (name) VALUES ('John Smith'); INSERT INTO people (name) VALUES ('Mary Jones'); INSERT INTO people (name) VALUES ('Joe Brown'); INSERT INTO people (name) VALUES ('Jane White'); CREATE OR REPLACE PACKAGE sr_pkg IS PRAGMA SERIALLY_REUSABLE; CURSOR c IS SELECT name FROM people; END sr_pkg; / CREATE OR REPLACE PROCEDURE fetch_from_cursor IS name_ VARCHAR2(200); BEGIN IF sr_pkg.c%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is open.'); ELSE DBMS_OUTPUT.PUT_LINE('Cursor is closed; opening now.'); OPEN sr_pkg.c; END IF; FETCH sr_pkg.c INTO name_; DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_); FETCH sr_pkg.c INTO name; DBMS_OUTPUT.PUT_LINE('Fetched: ' || name_); END fetch_from_cursor; /
First call to server:
BEGIN fetch_from_cursor; fetch_from_cursor; END; /
Cursor is closed; opening now. Fetched: John Smith Fetched: Mary Jones Cursor is open. Fetched: Joe Brown Fetched: Jane White
New call to server:
BEGIN fetch_from_cursor; fetch_from_cursor; END; /
Cursor is closed; opening now. Fetched: John Smith Fetched: Mary Jones Cursor is open. Fetched: Joe Brown Fetched: Jane White
For more information about the packages that Oracle Database supplies, see Oracle Database PL/SQL Packages and Types Reference.
Keep your packages general so that future applications can reuse them.
Design and define the package specifications before the package bodies.
In package specifications, declare only items that must be visible to invoking programs.
This practice prevents other developers from building unsafe dependencies on your implementation details and reduces the need for recompilation.
If you change the package specification, you must recompile any subprograms that invoke the public subprograms of the package. If you change only the package body, you do not have to recompile those subprograms.
Declare public cursors in package specifications and define them in package bodies, as in Example 10-7.
This practice lets you hide cursors' queries from package users and change them without changing cursor declarations.
This practice has these advantages:
The code for computing the initial values can be more complex and better documented.
If computing an initial value raises an exception, the initialization part can handle it with its own exception handler.
In Example 10-7, the declaration and definition of the cursor
c1 are in the specification and body, respectively, of the package
emp_stuff. The cursor declaration specifies only the data type of the return value, not the query, which appears in the cursor definition (for complete syntax and semantics, see "Explicit Cursor").
Example 10-7 Separating Cursor Declaration and Definition in Package
CREATE PACKAGE emp_stuff AS CURSOR c1 RETURN employees%ROWTYPE; -- Declare cursor END emp_stuff; / CREATE PACKAGE BODY emp_stuff AS CURSOR c1 RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE salary > 2500; -- Define cursor END emp_stuff; /
Example 10-8 creates a table,
log, and a package,
emp_admin, and then invokes package subprograms from an anonymous block. The package has both specification and body.
The specification declares a public type, cursor, and exception, and three public subprograms. One public subprogram is overloaded (for information about overloaded subprograms, see "Overloaded Subprograms").
The body declares a private variable, defines the public cursor and subprograms that the specification declares, declares and defines a private function, and has an initialization part.
The initialization part (which runs only the first time the anonymous block references the package) inserts one row into the table
log and initializes the private variable
number_hired to zero. Every time the package procedure
hire_employee is invoked, it updates the private variable
Example 10-8 Creating emp_admin Package
-- Log to track changes (not part of package): DROP TABLE log; CREATE TABLE log ( date_of_action DATE, user_id VARCHAR2(20), package_name VARCHAR2(30) ); -- Package specification: CREATE OR REPLACE PACKAGE emp_admin AS -- Declare public type, cursor, and exception: TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER); CURSOR desc_salary RETURN EmpRecTyp; invalid_salary EXCEPTION; -- Declare public subprograms: FUNCTION hire_employee ( last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER ) RETURN NUMBER; -- Overload preceding public subprogram: PROCEDURE fire_employee (emp_id NUMBER); PROCEDURE fire_employee (emp_email VARCHAR2); PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER); FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp; END emp_admin; / -- Package body: CREATE OR REPLACE PACKAGE BODY emp_admin AS number_hired NUMBER; -- private variable, visible only in this package -- Define cursor declared in package specification: CURSOR desc_salary RETURN EmpRecTyp IS SELECT employee_id, salary FROM employees ORDER BY salary DESC; -- Define subprograms declared in package specification: FUNCTION hire_employee ( last_name VARCHAR2, first_name VARCHAR2, email VARCHAR2, phone_number VARCHAR2, job_id VARCHAR2, salary NUMBER, commission_pct NUMBER, manager_id NUMBER, department_id NUMBER ) RETURN NUMBER IS new_emp_id NUMBER; BEGIN new_emp_id := employees_seq.NEXTVAL; INSERT INTO employees ( employee_id, last_name, first_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id ) VALUES ( new_emp_id, hire_employee.last_name, hire_employee.first_name, hire_employee.email, hire_employee.phone_number, SYSDATE, hire_employee.job_id, hire_employee.salary, hire_employee.commission_pct, hire_employee.manager_id, hire_employee.department_id ); number_hired := number_hired + 1; DBMS_OUTPUT.PUT_LINE('The number of employees hired is ' || TO_CHAR(number_hired) ); RETURN new_emp_id; END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM employees WHERE employee_id = emp_id; END fire_employee; PROCEDURE fire_employee (emp_email VARCHAR2) IS BEGIN DELETE FROM employees WHERE email = emp_email; END fire_employee; -- Define private function, available only inside package: FUNCTION sal_ok ( jobid VARCHAR2, sal NUMBER ) RETURN BOOLEAN IS min_sal NUMBER; max_sal NUMBER; BEGIN SELECT MIN(salary), MAX(salary) INTO min_sal, max_sal FROM employees WHERE job_id = jobid; RETURN (sal >= min_sal) AND (sal <= max_sal); END sal_ok; PROCEDURE raise_salary ( emp_id NUMBER, amount NUMBER ) IS sal NUMBER(8,2); jobid VARCHAR2(10); BEGIN SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = emp_id; IF sal_ok(jobid, sal + amount) THEN -- Invoke private function UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; ELSE RAISE invalid_salary; END IF; EXCEPTION WHEN invalid_salary THEN DBMS_OUTPUT.PUT_LINE ('The salary is out of the specified range.'); END raise_salary; FUNCTION nth_highest_salary ( n NUMBER ) RETURN EmpRecTyp IS emp_rec EmpRecTyp; BEGIN OPEN desc_salary; FOR i IN 1..n LOOP FETCH desc_salary INTO emp_rec; END LOOP; CLOSE desc_salary; RETURN emp_rec; END nth_highest_salary; BEGIN -- initialization part of package body INSERT INTO log (date_of_action, user_id, package_name) VALUES (SYSDATE, USER, 'EMP_ADMIN'); number_hired := 0; END emp_admin; / -- Invoke packages subprograms in anonymous block: DECLARE new_emp_id NUMBER(6); BEGIN new_emp_id := emp_admin.hire_employee ( 'Belden', 'Enrique', 'EBELDEN', '555.111.2222', 'ST_CLERK', 2500, .1, 101, 110 ); DBMS_OUTPUT.PUT_LINE ('The employee id is ' || TO_CHAR(new_emp_id)); emp_admin.raise_salary (new_emp_id, 100); DBMS_OUTPUT.PUT_LINE ( 'The 10th highest salary is '|| TO_CHAR (emp_admin.nth_highest_salary(10).sal) || ', belonging to employee: ' || TO_CHAR (emp_admin.nth_highest_salary(10).emp_id) ); emp_admin.fire_employee(new_emp_id); -- You can also delete the newly added employee as follows: -- emp_admin.fire_employee('EBELDEN'); END; /
Result is similar to:
The number of employees hired is 1 The employee id is 212 The 10th highest salary is 12075, belonging to employee: 168 There are now 107 employees.
A package named
STANDARD defines the PL/SQL environment. The package specification declares public types, variables, exceptions, subprograms, which are available automatically to PL/SQL programs. For example, package
STANDARD declares function
ABS, which returns the absolute value of its argument, as follows:
FUNCTION ABS (n NUMBER) RETURN NUMBER;
The contents of package
STANDARD are directly visible to applications. You need not qualify references to its contents by prefixing the package name. For example, you might invoke
ABS from a database trigger, stored subprogram, Oracle tool, or 3GL application, as follows:
abs_diff := ABS(x - y);
If you declare your own version of
ABS, your local declaration overrides the public declaration. You can still invoke the built-in function by specifying its full name:
abs_diff := STANDARD.ABS(x - y);
Most built-in functions are overloaded. For example, package
STANDARD contains these declarations:
FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2; FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2; FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2; FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;
PL/SQL resolves an invocation of
TO_CHAR by matching the number and data types of the formal and actual parameters.