10 PL/SQL Packages

What is a Package?

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. 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 items 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 specification as an interface and of the body as a black box. You can debug, enhance, or replace a package body without changing the package specification.

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

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

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 package initialization part, which holds statements that initialize package variables and do any other one-time setup steps.

The AUTHID clause 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)".

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.

What Goes in a Package?

A PL/SQL package contains:

• Get and Set methods for the package variables, to avoid letting other subprograms read and write them directly.

• Cursor declarations with the text of SQL queries. Reusing the same query text in multiple locations is faster than retyping the same query each time with slight differences. It is also easier to maintain if you must change a query that is used in many places.

• Declarations for exceptions. Typically, you must be able to reference these from different subprograms, so that you can handle exceptions in invoked subprograms.

• Declarations for subprograms that invoke each other. You need not worry about compilation order for packaged subprograms, making them more convenient than standalone stored subprograms when they invoke back and forth to each other.

• Declarations for overloaded subprograms. You can create multiple variations of a subprogram, using the same names but different sets of parameters.

• Variables to remain available between subprogram calls in the same session. You can treat variables in a package like global variables.

• Type declarations for PL/SQL collection types. To pass a collection as a parameter between stored subprograms, you must declare the type in a package so that both the invoking and invoked subprogram can refer to it.

For more information, see "CREATE PACKAGE Statement". Only the declarations in the package specification 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 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 specification 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 run 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 specification, so they are only recompiled if the specification changes.

Serially Reusable Packages

PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE.

For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. Therefore, the global memory for such a package is only used in a unit of work. After that unit of work, the memory can be released to the pool to be reused by another user (after running the initialization code for all the global variables).

The unit of work for serially reusable packages is implicitly a call to the server; for example, an OCI call to the server, or a PL/SQL RPC call from a client to a server, or an RPC call from a server to another server.

Topics:

• Package States

• Why Serially Reusable Packages?

• Syntax of Serially Reusable Packages

• Semantics of Serially Reusable Packages

• Examples of Serially Reusable Packages

PRAGMA SERIALLY_REUSABLE;
 

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

CREATE OR REPLACE PACKAGE sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  TYPE str_table_type IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
  num      NUMBER        := 10;
  str      VARCHAR2(200) := 'default-init-str';
  str_tab  STR_TABLE_TYPE;
  
  PROCEDURE print_pkg;
  
    PROCEDURE init_and_print_pkg (
      n NUMBER,
      v VARCHAR2
    );
END sr_pkg;
/
CREATE OR REPLACE PACKAGE BODY sr_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  
  PROCEDURE print_pkg IS
    num      NUMBER;
    str      VARCHAR2(200);
    str_tab  STR_TABLE_TYPE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('num: ' || sr_pkg.num);
    DBMS_OUTPUT.PUT_LINE('str: ' || sr_pkg.str);
    DBMS_OUTPUT.PUT_LINE
      ('number of table elements: ' || sr_pkg.str_tab.count);
    FOR i IN 1..sr_pkg.str_tab.count LOOP
      DBMS_OUTPUT.PUT_LINE(sr_pkg.str_tab(i));
    END LOOP;
  END print_pkg;
 
  PROCEDURE init_and_print_pkg (
    n NUMBER,
    v VARCHAR2
  ) IS
  BEGIN
    sr_pkg.num := n;
    sr_pkg.str := v;
 
    FOR i IN 1..n LOOP
      sr_pkg.str_tab(i) := v || ' ' || i;
    END LOOP;
 
    Print_pkg;
  END  init_and_print_pkg;
END sr_pkg;
/
BEGIN
  DBMS_OUTPUT.PUT_LINE('Initializing and printing package state ...');
  sr_pkg.init_and_print_pkg(4, 'abracadabra');
  DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL ...');
  sr_pkg.print_pkg;
END;
/

Initializing and printing package state ...
num: 4
str: abracadabra
number of table elements: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL ...
num: 4
str: abracadabra
number of table elements: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4

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;
/
 

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
 

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

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

Description of "Figure 10-1 Package Scope"

The specification lists the package resources available to applications. All the information your application must use the resources is in the specification. For example, this 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 specification 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-4, 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.

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;
/

Referencing Package Contents

To reference the types, items, subprograms, and call specifications declared in a package specification, 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.

Restrictions

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

Inside a package, you cannot reference host variables.

Package Body

The package body contains the implementation of every cursor and subprogram declared in the package specification. Subprograms defined in a package body are accessible outside the package only if their specs also appear in the package specification. If a subprogram specification is not included in the package specification, 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 specification.

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-5 shows.

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

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 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 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 in the package body. Unlike a package specification, 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 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 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 specification.

Examples of Package Features

Consider the package emp_admin in Example 10-6. The package specification declares these types, items, and subprograms:

• Type EmpRecTyp

• Cursor desc_salary

• Exception invalid_salary

• Functions hire_employee and nth_highest_salary

• Procedures fire_employee and raise_salary

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 run privilege on the package.

-- Log to track changes:

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 externally visible type, cursor, and 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;

  -- Overloaded 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;  -- 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 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
    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;
/
-- Invoking 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 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;
/

The number of employees hired is 1
The employee id is 207
The 10th highest salary is 11730, belonging to employee: 168
There are now 107 employees.

Remember, the initialization part of a package is run once, the first time you reference the package. In the last example, only one row is inserted into the database table log, and the variable number_hired is initialized 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 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-6 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 "Overloaded Subprograms".

Private and Public Items in 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 in the package. PL/SQL code outside the package cannot reference the variable number_hired. Such items are called private.

Items declared in the specification 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 in 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 specification 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 specification 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 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 a call to TO_CHAR by matching the number and data types of the formal and actual parameters.

Overview of Product-Specific 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 these widely used product-specific packages:

• DBMS_ALERT Package

• DBMS_OUTPUT Package

• DBMS_PIPE Package

• DBMS_CONNECTION_POOL Package

• HTF and HTP Packages

• UTL_FILE Package

• UTL_HTTP Package

• UTL_SMTP Package

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

-- 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;
/

These are the tables that HR owns:
LOCATIONS
PERFORMANCE_PARTS
EVALUATIONS
SCORES
EVALUATIONS_LOG
COUNTRIES
JOB_HISTORY
REGIONS
JOBS
DEPARTMENTS
EMPLOYEES

Packing Writing Guidelines

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

Design and define package specs before the package bodies. Place in a specification 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 specification. Changes to a package body do not require recompiling invoking subprograms. Changes to a package specification require the database to recompile every stored subprogram that references the package.

Separating Cursor Specifications and Bodies with 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 specification. For information about the cursor syntax, see "Explicit Cursor".

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

CREATE PACKAGE emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE;
END emp_stuff;
/
CREATE PACKAGE BODY emp_stuff AS
  CURSOR c1 RETURN employees%ROWTYPE IS
    SELECT * FROM employees WHERE salary > 2500;
END emp_stuff;
/

The cursor specification 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 specification. 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 specification.

From a PL/SQL block or subprogram, you use dot notation to reference a packaged cursor, as in Example 10-9.

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.