Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Using PL/SQL Packages

This chapter explains how to bundle related PL/SQL code and data into a package. A package is compiled and stored in the database, where many applications can share its contents.

Topics:

What is a PL/SQL Package?

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification ("spec") and a body; sometimes the body is unnecessary.

The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

You can think of the spec as an interface and of the body as a black box. You can debug, enhance, or replace a package body without changing the package spec.

To create a package spec, use the CREATE PACKAGE Statement. To create a package body, use the CREATE PACKAGE BODY Statement.

The spec holds public declarations, which are visible to stored subprograms and other code outside the package. You must declare subprograms at the end of the spec after all other items (except pragmas that name a specific function; such pragmas must follow the function spec).

The body holds implementation details and private declarations, which are hidden from code outside the package. Following the declarative part of the package body is the optional initialization part, which holds statements that initialize package variables and do any other one-time setup steps.

The AUTHID clause determines whether all the packaged subprograms execute 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 Using Invoker's Rights or Definer's Rights (AUTHID Clause).

A call specification lets you map a package subprogram to a Java method or external C function. The call specification maps the Java or C name, parameter types, and return type to their SQL counterparts.

See Also:

What Goes in a PL/SQL Package?

A PL/SQL package contains the following:

For more information, see CREATE PACKAGE Statement. For an examples of a PL/SQL packages, see Example 1-19 and Example 10-3. Only the declarations in the package spec are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible. You can change the body (implementation) without having to recompile invoking programs.

Advantages of PL/SQL Packages

Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.

Modularity

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

Easier Application Design

When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Information Hiding

With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

Added Functionality

Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

Better Performance

When you invoke a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.

Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, the database does not recompile other subprograms that invoke the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes.

Understanding the PL/SQL Package Specification

The package specification contains public declarations. The declared items are accessible from anywhere in the package and to any other subprograms in the same schema. Figure 10-1 illustrates the scoping.

Figure 10-1 Package Scope

Package Scope
Description of "Figure 10-1 Package Scope"

The spec lists the package resources available to applications. All the information your application must use the resources is in the spec. For example, the following declaration shows that the function named factorial takes one argument of type INTEGER and returns a value of type INTEGER:

FUNCTION factorial (n INTEGER) RETURN INTEGER; -- returns n!

That is all the information needed to invoke the function. You need not consider its underlying implementation (whether it is iterative or recursive for example).

If a spec declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. Only subprograms and cursors have an underlying implementation. In Example 10-1, the package needs no body because it declares types, exceptions, and variables, but no subprograms or cursors. Such packages let you define global variables, usable by stored subprograms and triggers, that persist throughout a session.

Example 10-1 A Simple Package Specification Without a Body

CREATE PACKAGE trans_data AS  -- bodiless package
   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;
/

Referencing PL/SQL Package Contents

To reference the types, items, subprograms, and call specifications declared within a package spec, use dot notation:

package_name.type_name
package_name.item_name
package_name.subprogram_name
package_name.call_spec_name

You can reference package contents from database triggers, stored subprograms, 3GL application programs, and various Oracle tools. For example, you can invoke package subprograms as shown in Example 1-20 or Example 10-3.

The following example invokes the hire_employee procedure from an anonymous block in a Pro*C program. The actual parameters emp_id, emp_lname, and emp_fname are host variables.

EXEC SQL EXECUTE
  BEGIN
    emp_actions.hire_employee(:emp_id,:emp_lname,:emp_fname, ...);

Restrictions

You cannot reference remote packaged variables, either directly or indirectly. For example, you cannot invoke the a subprogram through a database link if the subprogram refers to a packaged variable.

Inside a package, you cannot reference host variables.

Understanding the PL/SQL Package Body

The package body contains the implementation of every cursor and subprogram declared in the package spec. Subprograms defined in a package body are accessible outside the package only if their specs also appear in the package spec. If a subprogram spec is not included in the package spec, that subprogram can only be invoked by other subprograms in the same package. A package body must be in the same schema as the package spec.

To match subprogram specs and bodies, PL/SQL does a token-by-token comparison of their headers. Except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception, as Example 10-2 shows.

Example 10-2 Matching Package Specifications and Bodies

CREATE PACKAGE emp_bonus AS
   PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/
CREATE PACKAGE BODY emp_bonus AS
-- the following parameter declaration raises an exception 
-- because 'DATE' does not match employees.hire_date%TYPE
-- PROCEDURE calc_bonus (date_hired DATE) IS
-- the following is correct because there is an exact match
   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;
/

The package body can also contain private declarations, which define types and items necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and items are inaccessible except from within the package body. Unlike a package spec, the declarative part of a package body can contain subprogram bodies.

Following the declarative part of a package body is the optional initialization part, which typically holds statements that initialize some of the variables previously declared in the package.

The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be invoked or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package.

Remember, if a package specification declares only types, constants, variables, exceptions, and call specifications, the package body is unnecessary. However, the body can still be used to initialize items declared in the package spec.

Examples of PL/SQL Package Features

Consider the following package, named emp_admin. The package specification declares the following types, items, and subprograms:

After writing the package, you can develop applications that reference its types, invoke its subprograms, use its cursor, and raise its exception. When you create the package, it is stored in the database for use by any application that has execute privilege on the package.

Example 10-3 Creating the emp_admin Package

-- create the audit table to track changes
CREATE TABLE emp_audit(date_of_action DATE, user_id VARCHAR2(20), 
                       package_name VARCHAR2(30));

CREATE OR REPLACE PACKAGE emp_admin AS
-- Declare externally visible types, cursor, exception
   TYPE EmpRecTyp IS RECORD (emp_id NUMBER, sal NUMBER);
   CURSOR desc_salary RETURN EmpRecTyp;
   invalid_salary EXCEPTION;
-- Declare externally callable 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;
   PROCEDURE fire_employee
     (emp_id NUMBER); -- overloaded subprogram
   PROCEDURE fire_employee
     (emp_email VARCHAR2); -- overloaded subprogram
   PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER);
   FUNCTION nth_highest_salary (n NUMBER) RETURN EmpRecTyp;
END emp_admin;
/
CREATE OR REPLACE PACKAGE BODY emp_admin AS
   number_hired NUMBER;  -- visible only in this package
-- Fully define cursor specified in package
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT employee_id, salary
      FROM employees
      ORDER BY salary DESC;
-- Fully define subprograms specified in package
   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 VALUES (new_emp_id,
                                    last_name,
                                    first_name,
                                    email,
                                    phone_number,
                                    SYSDATE,
                                    job_id,
                                    salary,
                                    commission_pct,
                                    manager_id,
                                    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 local 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
         UPDATE employees SET salary =
           salary + amount WHERE employee_id = emp_id;
      ELSE
         RAISE invalid_salary;
      END IF;
   EXCEPTION  -- exception-handling part starts here
     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 starts here
   INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ADMIN');
   number_hired := 0;
END emp_admin;
/
-- invoking the package procedures
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 new 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;
/

Remember, the initialization part of a package is run just once, the first time you reference the package. In the last example, only one row is inserted into the database table emp_audit, and the variable number_hired is initialized only once.

Every time the procedure hire_employee is invoked, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.

PL/SQL allows two or more packaged subprograms 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 emp_admin package in Example 10-3 defines two procedures named fire_employee. The first procedure accepts a number, while the second procedure accepts string. Each procedure handles the data appropriately. For the rules that apply to overloaded subprograms, see Overloading PL/SQL Subprogram Names.

Private and Public Items in PL/SQL Packages

In the package emp_admin, the package body declares a variable named number_hired, which is initialized to zero. Items declared in the body are restricted to use within the package. PL/SQL code outside the package cannot reference the variable number_hired. Such items are called private.

Items declared in the spec of emp_admin, such as the exception invalid_salary, are visible outside the package. Any PL/SQL code can reference the exception invalid_salary. Such items are called public.

To maintain items throughout a session or across transactions, place them in the declarative part of the package body. For example, the value of number_hired is kept between calls to hire_employee within the same session. The value is lost when the session ends.

To make the items public, place them in the package specification. For example, emp_rec declared in the spec of the package is available for general use.

How STANDARD Package Defines the PL/SQL Environment

A package named STANDARD defines the PL/SQL environment. The package spec globally declares types, exceptions, and 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 global 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 the following 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 a call to TO_CHAR by matching the number and data types of the formal and actual parameters.

Overview of Product-Specific PL/SQL Packages

Various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) that you can invoke from PL/SQL, SQL, Java, and other programming environments. This section briefly describes the following widely used product-specific packages:

For more information about these and other product-specific packages, see Oracle Database PL/SQL Packages and Types Reference.

DBMS_ALERT Package

DBMS_ALERT package lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous (that is, they operate independently of any timing mechanism). For example, a company might use this package to update the value of its investment portfolio as new stock and bond quotes arrive.

DBMS_OUTPUT Package

DBMS_OUTPUT package enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information. The procedure PUT_LINE outputs information to a buffer that can be read by another trigger, subprogram, or package. You display the information by invoking the procedure GET_LINE or by setting SERVEROUTPUT ON in SQL*Plus. Example 10-4 shows how to display output from a PL/SQL block.

Example 10-4 Using PUT_LINE in the DBMS_OUTPUT Package

REM set server output to ON to display output from DBMS_OUTPUT
SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE
    ('These are the tables that ' || USER || ' owns:');
  FOR item IN (SELECT table_name FROM user_tables)
    LOOP
      DBMS_OUTPUT.PUT_LINE(item.table_name);
    END LOOP;
END;
/

DBMS_PIPE Package

DBMS_PIPE package allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a Linux or UNIX program.

At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored subprograms in the database.

DBMS_CONNECTION_POOL Package

DBMS_CONNECTION_POOL package is meant for managing the Database Resident Connection Pool, which is shared by multiple middle-tier processes. The database administrator uses procedures in DBMS_CONNECTION_POOL to start and stop the database resident connection pool and to configure pool parameters such as size and time limit.

See Also:

HTF and HTP Packages

HTF and HTP packages enable your PL/SQL programs to generate HTML tags.

UTL_FILE Package

UTL_FILE pagkage lets PL/SQL programs read and write operating system text files. It provides a restricted version of standard operating system stream file I/O, including open, put, get, and close operations.

When you want to read or write a text file, you invoke the function FOPEN, which returns a file handle for use in subsequent subprogram calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.

UTL_HTTP Package

UTL_HTTP package enables your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. It can retrieve data from the Internet or invoke Oracle Web Server cartridges. The package has multiple entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in hypertext markup language (HTML) format.

UTL_SMTP Package

UTL_SMTP package enables your PL/SQL programs to send electronic mails (eEmails) over Simple Mail Transfer Protocol (SMTP). The package provides interfaces to the SMTP commands for an eEmail client to dispatch eEmails to a SMTP server.

Guidelines for Writing PL/SQL Packages

When writing packages, keep them general so they can be reused in future applications. Become familiar with the packages that Oracle supplies, and avoid writing packages that duplicate features already provided by Oracle.

Design and define package specs before the package bodies. Place in a spec only those things that must be visible to invoking programs. That way, other developers cannot build unsafe dependencies on your implementation details.

To reduce the need for recompiling when code is changed, place as few items as possible in a package spec. Changes to a package body do not require recompiling invoking subprograms. Changes to a package spec require the database to recompile every stored subprogram that references the package.

Separating Cursor Specifications and Bodies with PL/SQL Packages

You can separate a cursor specification ("spec") from its body for placement in a package. That way, you can change the cursor body without having to change the cursor spec. For information about the cursor syntax, see Explicit Cursor.

In Example 10-5, you use the %ROWTYPE attribute to provide a record type that represents a row in the database table employees.

Example 10-5 Separating Cursor Specifications with Packages

CREATE PACKAGE emp_stuff AS
  -- Declare cursor spec
  CURSOR c1 RETURN employees%ROWTYPE;
END emp_stuff;
/
CREATE PACKAGE BODY emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE IS
    -- Define cursor body
    SELECT * FROM employees WHERE salary > 2500;
END emp_stuff;
/

The cursor spec has no SELECT statement because the RETURN clause specifies the data type of the return value. However, the cursor body must have a SELECT statement and the same RETURN clause as the cursor spec. Also, the number and data types of items in the SELECT list and the RETURN clause must match.

Packaged cursors increase flexibility. For example, you can change the cursor body in the last example, without having to change the cursor spec.

From a PL/SQL block or subprogram, you use dot notation to reference a packaged cursor, as the following example shows:

DECLARE   emp_rec employees%ROWTYPE;BEGIN
   OPEN emp_stuff.c1;
   LOOP
      FETCH emp_stuff.c1 INTO emp_rec;
-- do processing here ...
      EXIT WHEN emp_stuff.c1%NOTFOUND;
   END LOOP;
   CLOSE emp_stuff.c1;
END;
/

The scope of a packaged cursor is not limited to a PL/SQL block. When you open a packaged cursor, it remains open until you close it or you disconnect from the session.