Oracle Packages

In Oracle Packages a set of elements: procedures, functions, type definitions; declarations of variables, constants can be combined into a package.

After writing, the PL/SQL package is compiled and then saved in an Oracle database, where its contents can be used by many applications.

What is Oracle PL/SQL package?

An Oracle PL/SQL package is a schema object that groups logically related types, elements and subroutines. A package usually consists of two parts: a specification and a body, although sometimes a body is not needed. A specification is the interface for your applications.

A package specification declares the types, variables, constants, exceptions, cursors and subroutines available for use.

The package body fully defines cursors and subprograms and implements the specification.

You can debug, improve, or change the body of a package without changing the interface (specification) of the package.

Use the CREATE PACKAGE operator to create packages.

CREATE PACKAGE syntax in Oracle PL/SQL

CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER]]
{IS | AS}
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition_id ...]
[record_type_definition_id ...]
[subtype_definition_id ...]
[collection_declaration_id ...]
[constant_declaration_id ...]
[exception_declaration_id ...]
[object_declaration_id ...]
[record_declaration_id ...]
[variable_declaration_id ...]
[cursor_spec_id ...]
[function_spec_id ...]
[procedure_spec_id ...]
[call_spec_id ...]
[PRAGMA RESTRICT_REFERENCES(assertions_id) ...]
END [package_name_id];
[CREATE [OR REPLACE] PACKAGE BODY package_name_id {IS | AS}
[PRAGMA SERIALLY_REUSABLE;]
[collection_type_definition_id ...]
[record_type_definition_id ...]
[subtype_definition_id ...]
[collection_declaration_id ...]
[constant_declaration_id ...]
[exception_declaration_id...]
[object_declaration_id ...]
[record_declaration_id ...]
[variable_declaration_id ...]
[cursor_body_id...]
[function_spec_id ...]
[procedure_spec_id ...]
[call_spec_id ...]
[BEGIN
sequence_of_statements_id]
END [package_name_id];]

The package specification contains public announcements that are visible to your application. You must declare subroutines at the end of the specification after all other elements (except for pragmas that call a particular function; such pragmas must follow the function specification).

The package body contains implementation details and private announcements that are hidden from your application. The declaration part of the package body is followed by the optional initialization part, which usually contains operators that initialize package variables.

Example of an Oracle PL/SQL package

In the example below, you define the type of entry, cursor and two employment procedures. Note that the hire_employee procedure uses the empno_seq database sequence and the SYSDATE function to insert the new employee number and recruitment date respectively.

CREATE OR REPLACE PACKAGE emp_actions AS -- specification
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL); -- record type
CURSOR desc_salary RETURN EmpRecTyp; -- Cursor
/*recruitment procedure*/
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
Deptno NUMBER);
/*firing procedure*/
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;
PROCEDURE hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr NUMBER,
sal NUMBER,
comm NUMBER,
deptno NUMBER) IS
BEGIN
INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,
mgr, SYSDATE, sal, comm, deptno);
END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;

Only declarations in the package specification are visible and available for applications. Implementation details in the package body are hidden and not available. Thus, you can change the body (implementation) without recompiling the calling programs.

Advantages of Oracle PL/SQL packages

Modularity

Packages allow you to encapsulate logically related types, elements and subroutines in a named PL/SQL module. Each package is simple to understand and the interfaces between packages are simple, clear and well defined. This helps in application development.

Simpler application design

When developing an application, all you initially need is the interface information in the package specification. You can encode and compile the specification without its body. Then, stored subroutines that reference the package can also be compiled.

Cover up information

Using packages you can specify which types, elements and subroutines are public (visible and available) or private (hidden and inaccessible).

For example, if a package contains four subroutines, three can be open and one can be closed. The package hides the implementation of a closed sub-program, so only the package (not your application) is affected when you change the implementation.

This simplifies maintenance and improvement. In addition, by hiding implementation details from users, you protect the package integrity.

Added functionality

Bundled public variables and cursors are saved during the session. Thus, they can be shared by all the subprograms that run in the environment. In addition, they allow you to maintain data between transactions without saving it in the database.

Better performance

When you call a batch subroutine for the first time, the entire package is loaded into memory. Therefore, subsequent calls to related subroutines in the packet do not require disk I/O.

In addition, the packets stop cascading dependencies and thus avoid unnecessary recompilation. For example, if you change a package function’s implementation, Oracle does not need to recompile calling subroutines because they are independent of the package body.

Understanding the package specification

The package specification contains public announcements of items. The scope of these ads is local for your database schema and global for the package. So, the declared items are available from your application and from anywhere in the package.

The package specification lists the items of the package available to applications. All the information your application needs to use the items is contained in the specification. For example, the following declaration shows that a function named fac takes one argument of type INTEGER and returns a value of type INTEGER:

FUNCTION fac (n INTEGER) RETURN INTEGER; -- n!

This is all the information needed to call the function. You do not need to consider its basic implementation (for example, iterative or recursive).

Only subroutines and cursors have a basic implementation. Thus, if a specification declares only types, constants, variables, exceptions and call specifications, the package body is not required. Let’s consider the following package without a body:

CREATE PACKAGE trans_data AS -- bodiless package
TYPE TimeRec IS RECORD (
minutes SMALLINT,
SMALLINT);
TYPE TransRec IS RECORD (
category VARCHAR2,
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;

The trans_data package does not need a body because types, constants, variables and exceptions do not have a basic implementation. These packages allow you to define global variables – used by subroutines and database triggers – that are stored during a session.

Link to package content

To refer to the types, elements, subroutines and call specifications declared in the package specification, use dot notation as follows:

package_name.type_name

package_name.item_name

package_name.subprogram_name

package_name.call_spec_name

You can reference the contents of the package using database triggers, stored subprograms, 3GL applications and various Oracle tools. For example, you can call the procedure of the hire_employee package from PL/SQL Developer as follows:

BEGIN
emp_actions.hire_employee('VLADIMIR', 'MEDIC', ...);
END;

LIMITATIONS

You cannot refer to remote package variables directly or indirectly. For example, you cannot call the following procedure remotely because it refers to a package variable when the parameter is initialized:

CREATE PACKAGE random AS
seed NUMBER;
PROCEDURE initialize (starter IN NUMBER := seed, ...);

Also, you cannot refer to host variables inside the package.

Understanding the package body

The package body implements the package specification. That is, the package body contains the implementation of each cursor and subroutine, declared in the package specification. Remember that the subroutines defined in the package body are only available outside the package if their specifications are also specified in the package specification.

To compare the specifications and sub-program bodies, PL/SQL compares their headers by definition. Thus, except for spaces, the headers must match word for word. Otherwise, PL/SQL calls an exception, as shown in the following example:

CREATE PACKAGE emp_actions AS
...
PROCEDURE calc_bonus (date_hired emp.hiredate%TYPE, ...);
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
...
PROCEDURE calc_bonus (date_hired DATE, ...) IS
/* a parameter declaration triggers an exception because 'DATE'
does not match 'emp.hiredate%TYPE' word to word
*/
BEGIN ... END;
END emp_actions;

The package body may also contain private announcements, which define the types and elements needed for the internal operation of the package. The scope of these ads is local for the package body. Therefore, the declared types and items are not available except inside the package body. Unlike the package specification, the declared part of the package body may contain subroutine bodies.

The declared part of the package body is followed by an additional initialization part, which usually contains operators that initialize some variables previously declared in the package.

The initialization part of a package plays a secondary role because, unlike subroutines, the package cannot be called or passed by parameters. As a result, the package initialization part is launched only once, the first time the package is accessed.

Remember, if the package specification announces only types, constants, variables, exceptions and call specifications, the package body is not required. However, the body can still be used to initialize items declared in the package specification.

Some examples of package functions

Consider the package named emp_actions below. The specification of the package declares the following types, elements and subroutines:

Types EmpRecTyp and DeptRecTyp
Cursor desc_salary
Exception invalid_salary
Functions hire_employee and nth_highest_salary
Procedures fire_employee and raise_salary

After writing a package you can develop applications that refer to its types, call its subroutines, use its cursor and call its exception. When you create a package, it is saved in the Oracle database for public use.

CREATE PACKAGE emp_actions AS
/* Announcement of external visible: types, cursor, exceptions. */
TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
TYPE DeptRecTyp IS RECORD (dept_id INT, location VARCHAR2);
CURSOR desc_salary RETURN EmpRecTyp;
invalid_salary EXCEPTION;

/* Declaration of external called subprograms. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
Deptno REAL) RETURN INT;
PROCEDURE fire_employee (emp_id INT);
PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL);
FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp;
END emp_actions;

CREATE PACKAGE BODY emp_actions AS
number_hired INT; -- visible only in this package

/* Fully defined cursor indicated in the package. */
CURSOR desc_salary RETURN EmpRecTyp IS
SELECT empno, sal FROM emp ORDER BY sal DESC;

/* Fully defined subroutine as specified in the package. */
FUNCTION hire_employee (
ename VARCHAR2,
job VARCHAR2,
mgr REAL,
sal REAL,
comm REAL,
Deptno REAL) RETURN INT IS
new_empno INT;
BEGIN
SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;
INSERT INTO emp VALUES (new_empno, ename, job,
mgr, SYSDATE, sal, comm, deptno);
number_hired := number_hired + 1;
RETURN new_empno;
END hire_employee;

PROCEDURE fire_employee (emp_id INT) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;

/* Certain local function, available only inside the package. */
FUNCTION sal_ok (rank INT, salary REAL) RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN .
SELECT losal, hisal INTO min_sal, max_sal FROM salgrade
WHERE grade = rank;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;

PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL) IS
salary REAL;
BEGIN
SELECT sal INTO salary FROM emp WHERE empno = emp_id;
IF sal_ok(grade, salary + amount) THEN
UPDATE emp SET sal = sal + amount WHERE empno = emp_id;
ELSE
RAISE invalid_salary;
END IF;
END raise_salary;

FUNCTION nth_highest_salary (n INT) 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 -- part of the initialization starts here.
INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS');
number_hired := 0;
END emp_actions;

Remember that the packet initialization portion of the package is only run once when you first refer to the package. So, in the last example, only one line is inserted into the emp_audit database table. Similarly, the number_hired variable is initialized only once.

Each time the hire_employee procedure is called, the number_hired variable is updated. However, the number stored in number_hired depends on a particular session. That is, the number represents the number of new employees processed by one user, not the number processed by all users.

An example of a bank transaction package

The next example is your package of some typical banking transactions. Suppose that debit and credit transactions are entered outside working hours through ATMs and then applied to accounts the next morning.

CREATE PACKAGE bank_transactions AS
/* Announcement of an externally visible constant. */
minimum_balance CONSTANT REAL := 100.00;
/* Declaring an external procedure to be called. */
PROCEDURE apply_transactions;
PROCEDURE enter_transaction (
acct INT,
kind CHAR,
REAL);
END bank_transactions;

CREATE PACKAGE BODY bank_transactions AS
/* Declare a global variable to store the transaction status. */
new_status VARCHAR2(70) := 'Unknown';

/* Use preliminary announcements because apply_transactions
calls credit_account and debit_account, which are not yet announced when calls are made. */
PROCEDURE credit_account (acct INT, credit REAL);
PROCEDURE debit_account (acct INT, debit REAL);

/* Fully define the procedures specified in the package. */
PROCEDURE apply_transactions IS

/* Apply pending transactions to the transactions table
to the accounts table. Use the cursor to select rows. */
CURSOR trans_cursor IS
SELECT acct_id, kind, amount FROM transactions
WHERE status = 'Pending'
ORDER BY time_tag
FOR UPDATE OF status; -- to lock the string
BEGIN
FOR trans IN trans_cursor LOOP
IF trans.kind = 'D' THEN
debit_account(trans.acct_id, trans.amount);
ELSIF trans.kind = 'C' THEN
credit_account(trans.acct_id, trans.amount);
ELSE .
new_status := 'Rejected';
END IF;
UPDATE transactions SET status = new_status
WHERE CURRENT OF trans_cursor;
END LOOP;
END apply_transactions;

PROCEDURE enter_transaction (
/* Add a transaction to the table of transactions. */
acct INT,
kind CHAR,
amount REAL) IS
BEGIN
INSERT INTO transactions
VALUES (acct, kind, amount, 'Pending', SYSDATE);
END enter_transaction;

/* Define local procedures that are only available in the package. */
PROCEDURE do_journal_entry (
/* Write a transaction in a log. */
acct INT,
kind CHAR,
new_bal REAL) IS
BEGIN
INSERT INTO journal
VALUES (acct, kind, new_bal, sysdate);
IF kind = 'D' THEN
new_status := 'Debit applied';
ELSE
new_status := 'Credit applied';
END IF;
END do_journal_entry;

PROCEDURE credit_account (acct INT, credit REAL) IS
/* Credit account, except for a bad account number (which does not exist). */
old_balance REAL;
new_balance REAL;
BEGIN .
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the string
new_balance := old_balance + credit;
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'C', new_balance);
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number';
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM,1,70);
END credit_account;

PROCEDURE debit_account (acct INT, debit REAL) IS
/* Debit account, if the account number is not bad or
there's not enough money in the account. */
old_balance REAL;
new_balance REAL;
insufficient_funds EXCEPTION;
BEGIN .
SELECT balance INTO old_balance FROM accounts
WHERE acct_id = acct
FOR UPDATE OF balance; -- to lock the string
new_balance := old_balance - debit;
IF new_balance >= minimum_balance THEN
UPDATE accounts SET balance = new_balance
WHERE acct_id = acct;
do_journal_entry(acct, 'D', new_balance);
ELSE .
RAISE insufficient_funds;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
new_status := 'Bad account number';
WHEN insufficient_funds THEN
new_status := 'Insufficient funds';
WHEN OTHERS THEN
new_status := SUBSTR(SQLERRM,1,70);
END debit_account;
END bank_transactions;

In this example of the package, part of the initialization is not used.

Private and public elements in packages

Look again at the emp_actions package. The body of the package declares a variable named number_hired, which is initialized with 0 – zero. Unlike items declared in the emp_actions specification, items declared in the body are limited for use in the package. Therefore, PL/SQL code outside the package cannot reference the number_hired variable. Such elements are called private.

However, the items declared in emp_actions specification, such as invalid_salary exception, are visible outside the package. Therefore, any PL/SQL code can refer to the invalid_salary exception. Such elements are called public.

When you must support items during a session or between transactions, place them on the declarative portion of the packet body. For example, the number_hired value is stored between hire_employee calls in the same session. The value is lost when the session ends.

If you must also make the items public, place them in the package specification. For example, the minimum_balance constant declared in the bank_transactions package specification is available for public use.

Overloading the package’s subroutines

PL/SQL allows two or more subprograms of a package to have the same name. This option is useful when you want a subprogram to accept similar sets of parameters that have different data types. For example, the following package defines two procedures with the name journalize:

CREATE PACKAGE journal_entries AS
...
PROCEDURE journalize (amount REAL, trans_date VARCHAR2) ;
PROCEDURE journalize (amount REAL, trans_date INT);
END journal_entries;

CREATE PACKAGE BODY journal_entries AS
...
PROCEDURE journalize (amount REAL, trans_date VARCHAR2) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYYYY'));
END journalize;

PROCEDURE journalize (amount REAL, trans_date INT) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'J'));
END journalize;
END journal_entries;

The first procedure takes trans_date as a character string, while the second procedure takes it as a number (Julian day). Each procedure handles the data accordingly.

How does the STANDARD package define the Oracle PL/SQL environment?

The package named STANDARD defines the PL/SQL environment. The specification of the package globally declares types, exceptions and subroutines that are automatically available for PL/SQL programs.

For example, the STANDARD package declares an ABS function that returns the absolute value of its argument as follows:

FUNCTION ABS (n NUMBER) RETURN NUMBER;

Contents of the STANDARD package are directly visible to applications. You do not need to specify links to its contents by adding a package name prefix. For example, you can call ABS from a database trigger, stored subroutine, Oracle tool or 3GL application as follows:

abs_diff := ABS(x - y);

If you re-declare ABS in the PL/SQL program, your local declaration will override the global declaration. However, you can still call the built-in function by specifying a reference to ABS as follows:

abs_diff := STANDARD.ABS(x - y);

Most built-in functions are overloaded. For example, the STANDARD package contains the following announcements:

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 allows calling TO_CHAR by comparing the number and data types of formal and actual parameters.

Package Writing Guide

When writing packages, keep them as general as possible so that they can be used in future applications. Avoid writing packages that duplicate certain features already provided by Oracle.

The package specifications reflect your application’s design. Identify them in front of the package body. Place only the types, elements and subroutines that should be visible to package users in the specification. This way, other developers cannot use the package incorrectly, based their code on irrelevant implementation details.

To reduce the need for recompilation when code changes, put as few elements as possible into the package specification. Changes to the package body do not require Oracle to recompile dependent procedures. However, changes to the package specification require Oracle to recompile every saved subroutine that refers to the package.

Introduction to PL/SQL Packages in Oracle Database