Skip Headers
Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1)

Part Number B28424-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

7 Coding PL/SQL Subprograms and Packages

This chapter describes some of the procedural capabilities of Oracle Database for application development, including:

See Also:

Overview of PL/SQL Program Units

PL/SQL is a modern, block-structured programming language. It provides several features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that are not available in standard SQL.

You can directly enter SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use subprograms supplied by Oracle to perform data definition language (DDL) statements.

PL/SQL code runs on the server, so using PL/SQL lets you centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.


Some Oracle tools, such as Oracle Forms, contain a PL/SQL engine that lets you run PL/SQL locally.

You can even use PL/SQL for some database applications in place of 3GL programs that use embedded SQL or Oracle Call Interface (OCI).

PL/SQL program units include:

Anonymous Blocks

An anonymous block is a PL/SQL program unit that has no name. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is invoked when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.

The following example of a PL/SQL anonymous block prints the names of all employees in department 20 in the hr.employees table by using the DBMS_OUTPUT package:

   Last_name    VARCHAR2(10);
   Cursor       c1 IS SELECT last_name 
                       FROM employees
                       WHERE department_id = 20;
   OPEN c1;
      FETCH c1 INTO Last_name;


If you test this block using SQL*Plus, then enter the statement SET SERVEROUTPUT ON so that output using the DBMS_OUTPUT procedures (for example, PUT_LINE) is activated. Also, end the example with a slash (/) to activate it.

Exceptions let you handle Oracle Database error conditions with PL/SQL program logic. This enables your application to prevent the server from issuing an error that can cause the client application to end. The following anonymous block handles the predefined Oracle Database exception NO_DATA_FOUND (which results in an ORA-01403 error if not handled):

   Emp_number   INTEGER := 9999;
   Emp_name     VARCHAR2(10);
   SELECT Ename INTO Emp_name FROM Emp_tab
      WHERE Empno = Emp_number;   -- no such number
   DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
      DBMS_OUTPUT.PUT_LINE('No such employee: ' || Emp_number);

You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

   Emp_name           VARCHAR2(10);
   Emp_number         INTEGER;
   Empno_out_of_range EXCEPTION;
   Emp_number := 10001;
   IF Emp_number > 9999 OR Emp_number < 1000 THEN
      RAISE Empno_out_of_range;
      SELECT Ename INTO Emp_name FROM Emp_tab
         WHERE Empno = Emp_number;
      DBMS_OUTPUT.PUT_LINE('Employee name is ' || Emp_name);
   WHEN Empno_out_of_range THEN
      DBMS_OUTPUT.PUT_LINE('Employee number ' || Emp_number ||
       ' is out of range.');

Anonymous blocks are usually used interactively from a tool, such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are usually used to invoke stored subprograms or to open cursor variables.

Stored PL/SQL Program Units

A stored PL/SQL program unit is a subprogram (procedure or function) or package that:

  • Has a name.

  • Can take parameters, and can return values.

  • Is stored in the data dictionary.

  • Can be invoked by many users.

If a subprogram belongs to a package, it is called a package subprogram; if not, it is called a standalone subprogram.


Naming Subprograms

Because a subprogram is stored in the database, it must be named. This distinguishes it from other stored subprograms and makes it possible for applications to invoke it. Each publicly-visible subprogram in a schema must have a unique name, and the name must be a legal PL/SQL identifier.


If you plan to invoke a stored subprogram using a stub generated by SQL*Module, then the stored subprogram name must also be a legal identifier in the invoking host 3GL language, such as Ada or C.

Subprogram Parameters

Stored subprograms can take parameters. The following example shows a stored subprogram that is similar to the anonymous block in "Anonymous Blocks".


To execute the following, use CREATE OR REPLACE PROCEDURE.
PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS
   Emp_name       VARCHAR2(10);
   CURSOR         c1 (Depno NUMBER) IS
                     SELECT Ename FROM Emp_tab
                        WHERE deptno = Depno;
   OPEN c1(Dept_num);
      FETCH c1 INTO Emp_name;
   CLOSE c1;

In the procedure Get_emp_names, the department number is an input parameter that is used when the parameterized cursor c1 is opened.

The formal parameters of a subprogram have three major attributes, described in Table 7-1.

Table 7-1 Attributes of Subprogram Parameters

Parameter Attribute Description


This must be a legal PL/SQL identifier.


This indicates whether the parameter is an input-only parameter (IN), an output-only parameter (OUT), or is both an input and an output parameter (IN OUT). If the mode is not specified, then IN is assumed.


This is a standard PL/SQL datatype.


Parameter Modes

Parameter modes define the action of formal parameters. You can use the three parameter modes, IN (the default), OUT, and IN OUT, with any subprogram. Avoid using the OUT and IN OUT modes with functions. Good programming practice dictates that a function returns a single value and does not change the values of variables that are not local to the subprogram.

Table 7-2 summarizes the information about parameter modes.

Table 7-2 Parameter Modes


The default.

Must be specified.

Must be specified.

Passes values to a subprogram.

Returns values to the caller.

Passes initial values to a subprogram; returns updated values to the caller.

Formal parameter acts like a constant.

Formal parameter acts like an uninitialized variable.

Formal parameter acts like an initialized variable.

Formal parameter cannot be assigned a value.

Formal parameter cannot be used in an expression; must be assigned a value.

Formal parameter must be assigned a value.

Actual parameter can be a constant, initialized variable, literal, or expression.

Actual parameter must be a variable.

Actual parameter must be a variable.

See Also:

Oracle Database PL/SQL Language Reference for details about parameter modes
Parameter Datatypes

The datatype of a formal parameter consists of one of the following:

  • An unconstrained type name, such as NUMBER or VARCHAR2.

  • A type that is constrained using the %TYPE or %ROWTYPE attributes.


    Numerically constrained types such as NUMBER(2) or VARCHAR2(20) are not allowed in a parameter list.
%TYPE and %ROWTYPE Attributes

Use the type attributes %TYPE and %ROWTYPE to constrain the parameter. For example, the Get_emp_names procedure specification in "Subprogram Parameters" can be written as the following:

PROCEDURE Get_emp_names(Dept_num IN Emp_tab.Deptno%TYPE)

This has the Dept_num parameter take the same datatype as the Deptno column in the Emp_tab table. The column and table must be available when a declaration using %TYPE (or %ROWTYPE) is elaborated.

Using %TYPE is recommended, because if the type of the column in the table changes, then it is not necessary to change the application code.

If the Get_emp_names procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

Dept_number    number(2);
PROCEDURE Get_emp_names(Dept_num IN Dept_number%TYPE);

Use the %ROWTYPE attribute to create a record that contains all the columns of the specified table. The following example defines the Get_emp_rec procedure, which returns all the columns of the Emp_tab table in a PL/SQL record for the given empno:


To execute the following, use the statement CREATE OR REPLACE PROCEDURE.
PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;

You can invoke this procedure from a PL/SQL block as follows:

   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
   Get_emp_rec(7499, Emp_row);   -- invoke for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);

Stored functions can also return values that are declared using %ROWTYPE. For example:

FUNCTION Get_emp_rec (Dept_num IN Emp_tab.Deptno%TYPE)
   RETURN Emp_tab%ROWTYPE IS ...
Tables and Records

You can pass PL/SQL tables as parameters to stored subprograms. You can also pass tables of records as parameters.


When passing a user defined type, such as a PL/SQL table or record to a remote subprogram, to make PL/SQL use the same definition so that the type checker can verify the source, you must create a redundant loop back DBLINK so that when the PL/SQL compiles, both sources pull from the same location.
Default Parameter Values

Parameters can take default values. Use the DEFAULT keyword or the assignment operator to give a parameter a default value. For example, the specification for the Get_emp_names procedure can be written as the following:

PROCEDURE Get_emp_names (Dept_num IN NUMBER DEFAULT 20) IS ...


PROCEDURE Get_emp_names (Dept_num IN NUMBER := 20) IS ...

When a parameter takes a default value, it can be omitted from the actual parameter list when you invoke the subprogram. When you do specify the parameter value on the invocation, it overrides the default value.


Unlike in an anonymous PL/SQL block, you do not use the keyword DECLARE before the declarations of variables, cursors, and exceptions in a stored subprogram. In fact, it is an error to use it.

Creating Subprograms

Use a text editor to write the subprogram. At the beginning of the subprogram, place the following statement:

CREATE PROCEDURE Procedure_name AS   ...

For example, to use the example in "%TYPE and %ROWTYPE Attributes", create a text (source) file called get_emp.sql containing the following code:

CREATE PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                              Emp_ret     OUT Emp_tab%ROWTYPE) AS
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;

Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering the following statement:

SQL> @get_emp

This loads the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). The slash (/) at the end of the code is not part of the code, it only activates the loading of the procedure.


When developing a new subprogram, it is usually preferable to use the statement CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION. This statement replaces any previous version of that subprogram in the same schema with the newer version, but without warning.

You can use either the keyword IS or AS after the subprogram parameter list.

See Also:

Privileges Needed

To create a subprogram, a package specification, or a package body, you must meet the following prerequisites:

  • You must have the CREATE PROCEDURE system privilege to create a subprogram or package in your schema, or the CREATE ANY PROCEDURE system privilege to create a subprogram or package in another user's schema. In either case, the package body must be created in the same schema as the package.


    To create without errors (to compile the subprogram or package successfully) requires the following additional privileges:
    • The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.

    • The owner cannot obtain required privileges through roles.

If the privileges of the owner of a subprogram or package change, then the subprogram must be reauthenticated before it is run. If a necessary privilege to a referenced object is revoked from the owner of the subprogram or package, then the subprogram cannot be run.

The EXECUTE privilege on a subprogram gives a user the right to run a subprogram owned by another user. Privileged users run the subprogram under the security domain of the owner of the subprogram. Therefore, users never need to be granted the privileges to the objects referenced by a subprogram. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all subprograms and packages are stored in the data dictionary (in the SYSTEM tablespace). No quota controls the amount of space available to a user who creates subprograms and packages.


Package creation requires a sort. The user creating the package must be able to create a sort segment in the temporary tablespace with which the user is associated.

Altering Subprograms

To alter a subprogram, you must first drop it using the DROP PROCEDURE or DROP FUNCTION statement, then re-create it using the CREATE PROCEDURE or CREATE FUNCTION statement. Alternatively, use the CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION statement, which first drops the subprogram if it exists, then re-creates it as specified.


The subprogram is dropped without warning.

Dropping Subprograms and Packages

A standalone subprogram, a standalone function, a package body, or an entire package can be dropped using the SQL statements DROP PROCEDURE, DROP FUNCTION, DROP PACKAGE BODY, and DROP PACKAGE, respectively. A DROP PACKAGE statement drops both the specification and body of a package.

The following statement drops the Old_sal_raise procedure in your schema:

DROP PROCEDURE Old_sal_raise;

Privileges Needed

To drop a subprogram or package, the subprogram or package must be in your schema, or you must have the DROP ANY PROCEDURE privilege. An individual subprogram within a package cannot be dropped; the containing package specification and body must be re-created without the subprograms to be dropped.

External Subprograms

A PL/SQL subprogram executing on an Oracle Database instance can invoke an external subprogram written in a third-generation language (3GL). The 3GL subprogram runs in a separate address space from that of the database.

See Also:

Chapter 14, "Developing Applications Using Multiple Programming Languages" for information about external subprograms

Cross-Session PL/SQL Function Result Cache

Using the PL/SQL cross-session function result cache can save significant space and time. Each time a result-cached PL/SQL function is invoked with different parameter values, those parameters and their result are stored in the cache. Subsequently, when the same function is invoked with the same parameter values, the result is retrieved from the cache, instead of being recomputed. Because the cache is stored in a shared global area (SGA), it is available to any session that runs your application.

If a database object that was used to compute a cached result is updated, the cached result becomes invalid and must be recomputed.

The best candidates for result-caching are functions that are invoked frequently but depend on information that changes infrequently or never.

For more information about the PL/SQL cross-session function result cache, see Oracle Database PL/SQL Language Reference.

PL/SQL Packages

A package is a collection of related program objects (for example, subprogram, variables, constants, cursors, and exceptions) stored together in the database.

Using packages is an alternative to creating subprograms as standalone schema objects. Packages have many advantages over standalone subprograms. For example, they:

  • Let you organize your application development more efficiently.

  • Let you grant privileges more efficiently.

  • Let you modify package objects without recompiling dependent schema objects.

  • Enable Oracle Database to read multiple package objects into memory at once.

  • Can contain global variables and cursors that are available to all subprograms in the package.

  • Let you overload subprograms. Overloading a subprogram means creating multiple subprograms with the same name in the same package, each taking arguments of different number or datatype.

    See Also:

    Oracle Database PL/SQL Language Reference for more information about subprogram name overloading

The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

Example of a PL/SQL Package Specification and Body

The following example shows a package specification for a package named Employee_management. The package contains one stored function and two stored procedures. The body for this package defines the function and the procedures:

CREATE PACKAGE BODY Employee_management AS
   FUNCTION Hire_emp (ename VARCHAR2, Job VARCHAR2,
      Mgr NUMBER, Hiredate DATE, Sal NUMBER, Comm NUMBER,
       New_empno    NUMBER(10);

-- This function accepts all arguments for the fields in
-- the employee table except for the employee number.
-- A value for this field is supplied by a sequence.
-- The function returns the sequence number generated
-- by the invocation of this function.

New_empno := Emp_sequence.NEXTVAL;
INSERT INTO emp VALUES (New_empno, ename, Job, Mgr,
         Hiredate, Sal, Comm, Deptno);
      RETURN (New_empno);
   END Hire_emp;

   PROCEDURE fire_emp(emp_id IN NUMBER) AS

-- This procedure deletes the employee with an employee
-- number that corresponds to the argument Emp_id. If
-- no employee is found, then an exception is raised.

      DELETE FROM emp WHERE Empno = Emp_id;
      Raise_application_error(-20011, 'Invalid Employee
         Number: ' || TO_CHAR(Emp_id));
   END IF;
END fire_emp;

PROCEDURE Sal_raise (Emp_id IN NUMBER, Sal_incr IN NUMBER) AS

-- This procedure accepts two arguments. Emp_id is a
-- number that corresponds to an employee number.
-- SAL_INCR is the amount by which to increase the
-- employee's salary. If employee exists, then update 
-- salary with increase.

      UPDATE emp
         SET Sal = Sal + Sal_incr
         WHERE Empno = Emp_id;
         Raise_application_error(-20011, 'Invalid Employee
            Number: ' || TO_CHAR(Emp_id));
      END IF;
   END Sal_raise;
END Employee_management;


If you want to try this example, then first create the sequence number Emp_sequence. Do this with the following SQL*Plus statement:

PL/SQL Object Size Limits

The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.

The most closely related number that a user can access is the PARSED_SIZE in the static data dictionary view *_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.

Creating Packages

Each part of a package is created with a different statement. Create the package specification using the CREATE PACKAGE statement. The CREATE PACKAGE statement declares public package objects.

To create a package body, use the CREATE PACKAGE BODY statement. The CREATE PACKAGE BODY statement defines the procedural code of the public subprograms declared in the package specification.

You can also define private, or local, package subprograms, and variables in a package body. These objects can only be accessed by other subprograms in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

It is often more convenient to add the OR REPLACE clause in the CREATE PACKAGE or CREATE PACKAGE BODY statements when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The CREATE statements are:



Creating Packaged Objects

The body of a package can contain:

  • Subprograms declared in the package specification.

  • Definitions of cursors declared in the package specification.

  • Local subprograms, not declared in the package specification.

  • Local variables.

Subprograms, cursors, and variables that are declared in the package specification are global. They can be invoked, or used, by external users that have EXECUTE permission for the package or that have EXECUTE ANY PROCEDURE privileges.

When you create the package body, ensure that each subprogram that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters and the return type must agree in name and type.

Privileges to Needed to Create or Drop Packages

The privileges required to create or drop a package specification or package body are the same as those required to create or drop a standalone subprogram. See "Creating Subprograms" and "Dropping Subprograms and Packages".

Naming Packages and Package Objects

The names of a package and all public objects in the package must be unique within a given schema. The package specification and its body must have the same name. All package constructs must have unique names within the scope of the package, unless overloading of subprogram names is desired.

Package Invalidations and Session State

Each session that references a package object has its own instance of the corresponding package, including persistent state for any public and private variables, cursors, and constants. If any of the session's instantiated packages (specification or body) are invalidated, then all package instances in the session are invalidated and recompiled. As a result, the session state is lost for all package instances in the session.

When a package in a given session is invalidated, the session receives the following error the first time it attempts to use any object of the invalid package instance:

ORA-04068: existing state of packages has been discarded

The second time a session makes such a package call, the package is reinstantiated for the session without error.


For optimal performance, Oracle Database returns this error message only once—each time the package state is discarded.

If you handle this error in your application, ensure that your error handling strategy can accurately handle this error. For example, when a subprogram in one package invokes a subprogram in another package, your application must be aware that the session state is lost for both packages.

In most production environments, DDL operations that can cause invalidations are usually performed during inactive working hours; therefore, this situation might not be a problem for end-user applications. However, if package invalidations are common in your system during working hours, then you might want to code your applications to handle this error when package calls are made.

Packages Supplied with Oracle Database

There are many packages provided with Oracle Database, either to extend the functionality of the database or to give PL/SQL access to SQL features. You can invoke these packages from your application.

See Also:

Oracle Database PL/SQL Packages and Types Reference for an overview of these Oracle Database packages

Overview of Bulk Binding

Oracle Database uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead.

Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include the following:

  • Varrays

  • Nested tables

  • Index-by tables

  • Host arrays

Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection back and forth between the two engines in a single operation.

Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds.


This section provides an overview of bulk binds to help you decide whether to use them in your PL/SQL applications. For detailed information about using bulk binds, including ways to handle exceptions that occur in the middle of a bulk bind operation, see Oracle Database PL/SQL Language Reference.

Parallel DML is disabled with bulk binds.

When to Use Bulk Binds

Consider using bulk binds to improve the performance of the following:

DML Statements that Reference Collections

The FORALL keyword can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements.

For example, the following PL/SQL block increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, both with and without using bulk binds:

   TYPE Numlist IS VARRAY (100) OF NUMBER;
   Id NUMLIST := NUMLIST(7902, 7698, 7839);

-- Efficient method, using a bulk bind
   FORALL i IN Id.FIRST..Id.LAST   -- bulk-bind the VARRAY
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);

-- Slower method, running the UPDATE statements within a regular loop
      UPDATE Emp_tab SET Sal = 1.1 * Sal
      WHERE Mgr = Id(i);

Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.

If you have a set of rows prepared in a PL/SQL table, you can bulk-insert or bulk-update the data using a loop like:

FORALL i in Emp_Data.FIRST..Emp_Data.LAST
    INSERT INTO Emp_tab VALUES(Emp_Data(i));
SELECT Statements that Reference Collections

The BULK COLLECT INTO clause can improve the performance of queries that reference collections.

For example, the following PL/SQL block queries multiple values into PL/SQL tables, both with and without bulk binds:

-- Find all employees whose manager's ID number is 7698.
   Empno VAR_TAB;
   Ename VAR_TAB;
   Counter NUMBER;
      SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;

-- Efficient method, using a bulk bind
    SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
        FROM Emp_Tab WHERE Mgr = 7698;

-- Slower method, assigning each collection element within a loop.

   counter := 1;  
   FOR rec IN C LOOP  
      Empno(Counter) := rec.Empno;
      Ename(Counter) := rec.Ename;
      Counter := Counter + 1;

You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values.

Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is selected, leading to context switches that hurt performance.

FOR Loops that Reference Collections and Return DML

You can use the FORALL keyword along with the BULK COLLECT INTO keywords to improve the performance of FOR loops that reference collections and return DML.

For example, the following PL/SQL block updates the Emp_tab table by computing bonuses for a collection of employees; then it returns the bonuses in a column called Bonlist. The actions are performed both with and without using bulk binds:

   Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698);
   TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE;
   Bonlist_inst BONLIST;
   Bonlist_inst := BONLIST(1,2,3,4,5);

   FORALL i IN Empids.FIRST..empIDs.LAST
      UPDATE Emp_tab SET Bonus = 0.1 * Sal
      WHERE Empno = Empids(i)

   FOR i IN Empids.FIRST..Empids.LAST LOOP
      UPDATE Emp_tab Set Bonus = 0.1 * sal        
         WHERE Empno = Empids(i)

Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.


A trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. You can also define INSTEAD OF triggers or system triggers (triggers on DATABASE and SCHEMA).

See Also:

Oracle Database PL/SQL Language Referencefor more information about triggers

Compiling PL/SQL Subprograms for Native Execution

You can speed up PL/SQL subprograms by compiling them into native code residing in shared libraries.

You can use native compilation with both the supplied Oracle packages and subprograms you write yourself. Subprograms compiled this way work in all server environments, such as the shared server configuration (formerly known as multithreaded server) and Oracle Real Application Clusters (Oracle RAC).

This technique is most effective for computation-intensive subprograms that do not spend much time executing SQL, because it can do little to speed up SQL statements invoked from these subprograms.

With Java, you can use the ncomp tool to compile your own packages and classes.

See Also:

Cursor Variables

A cursor is a static object; a cursor variable is a pointer to a cursor. Because cursor variables are pointers, they can be passed and returned as parameters to subprograms. A cursor variable can also refer to different cursors in its lifetime.

Additional advantages of cursor variables include the following:

See Also:

Oracle Database PL/SQL Language Reference for more information about cursor variables


Declaring and Opening Cursor Variables

Memory is usually allocated for a cursor variable in the client application using the appropriate ALLOCATE statement. In Pro*C, use the EXEC SQL ALLOCATE cursor_name statement. In OCI, use the Cursor Data Area.

You can also use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.

Examples of Cursor Variables

This section includes several examples of cursor variable usage in PL/SQL. For additional cursor variable examples that use the programmatic interfaces, see the following:

Example 7-1 creates a package, Emp_data, that defines a PL/SQL cursor variable type, Emp_val_cv_type, and two procedures. The first procedure, Open_emp_cv, opens the cursor variable using a bind variable in the WHERE clause. The second procedure, Fetch_emp_data, fetches rows from the Emp_tab table using the cursor variable. Example 7-2 invokes the Emp_data package procedures from a PL/SQL block.

Example 7-1 Package for Fetching Data with Cursor Variable

  TYPE Emp_val_cv_type IS REF CURSOR RETURN Emp_tab%ROWTYPE;
  PROCEDURE Open_emp_cv (Emp_cv          IN OUT Emp_val_cv_type,
                         Dept_number     IN     INTEGER); 
  PROCEDURE Fetch_emp_data (emp_cv       IN     Emp_val_cv_type,
                            emp_row      OUT    Emp_tab%ROWTYPE);
END Emp_data;

  PROCEDURE Open_emp_cv (Emp_cv      IN OUT Emp_val_cv_type,
                         Dept_number IN     INTEGER) IS
    OPEN emp_cv FOR SELECT * FROM Emp_tab WHERE deptno = dept_number;
  END open_emp_cv;
  PROCEDURE Fetch_emp_data (Emp_cv      IN  Emp_val_cv_type,
                            Emp_row     OUT Emp_tab%ROWTYPE) IS
    FETCH Emp_cv INTO Emp_row;
  END Fetch_emp_data;
END Emp_data;

Example 7-2 Invoking Package Procedures from a PL/SQL Block

-- declare a cursor variable
   Emp_curs Emp_data.Emp_val_cv_type;
   Dept_number Dept_tab.Deptno%TYPE;
   Emp_row Emp_tab%ROWTYPE;

   Dept_number := 20;
-- open the cursor using a variable
   Emp_data.Open_emp_cv(Emp_curs, Dept_number);
-- fetch the data and display it
     Emp_data.Fetch_emp_data(Emp_curs, Emp_row);
     DBMS_OUTPUT.PUT(Emp_row.Ename || '  ');

The power of cursor variables comes from their ability to point to different cursors. Example 7-3 uses a discriminant to open a cursor variable to point to one of two different cursors.

Example 7-3 Cursor Variable with Discriminator

  PROCEDURE Open_cv (Cv          IN OUT cv_type,
                     Discrim     IN     POSITIVE); 
END Emp_dept_data;

  PROCEDURE Open_cv (Cv      IN OUT cv_type,
                     Discrim IN     POSITIVE) IS
    IF Discrim = 1 THEN
      OPEN Cv FOR SELECT * FROM Emp_tab WHERE Sal > 2000;
    ELSIF Discrim = 2 THEN
      OPEN Cv FOR SELECT * FROM Dept_tab;
    END IF;
  END Open_cv;
END Emp_dept_data;

You can invoke the Open_cv procedure in Example 7-3 to open the cursor variable and point it to a query on either the Emp_tab table or the Dept_tab table. Example 7-4 uses the cursor variable to fetch data and then uses the ROWTYPE_MISMATCH predefined exception to handle either fetch.

Example 7-4 ROWTYPE_MISMATCH Predefined Exception

  Emp_rec  Emp_tab%ROWTYPE;
  Dept_rec Dept_tab%ROWTYPE;
  Cv       Emp_dept_data.CV_TYPE;

  Emp_dept_data.open_cv(Cv, 1); -- Open Cv For Emp_tab Fetch
  Fetch cv INTO Dept_rec;       -- but fetch into Dept_tab record
                                -- which raises ROWTYPE_MISMATCH
  DBMS_OUTPUT.PUT_LINE('  ' || Dept_rec.Loc);

           ('Row type mismatch, fetching Emp_tab data...');
      FETCH Cv INTO Emp_rec;
      DBMS_OUTPUT.PUT_LINE('  ' || Emp_rec.Ename);

Handling PL/SQL Compile-Time Errors

To list compile-time errors, query the static data dictionary view *_ERRORS. From these views, you can retrieve original source code. The error text associated with the compilation of a subprogram is updated when the subprogram is replaced, and it is deleted when the subprogram is dropped.

When you use SQL*Plus to submit PL/SQL code, and when the code contains errors, you receive notification that compilation errors have occurred, but there is no immediate indication of what the errors are. For example, if you submit a standalone (or stored) procedure PROC1 in the file proc1.sql as follows:

SQL> @proc1

If there are one or more errors in the code, then you receive a notice such as the following:

MGR-00072: Warning: Procedure proc1 created with compilation errors

In this case, use the SHOW ERRORS statement in SQL*Plus to get a list of the errors that were found. SHOW ERRORS with no argument lists the errors from the most recent compilation. You can qualify SHOW ERRORS using the name of a subprogram, package, or package body:



Before issuing the SHOW ERRORS statement, use the SET LINESIZE statement to get long lines on output. The value 132 is usually a good choice. For example:

Assume that you want to create a simple procedure that deletes records from the employee table using SQL*Plus:

      DELETE FROM Emp_tab WHER Empno = Emp_id;

Notice that the CREATE PROCEDURE statement has two errors: the DELETE statement has an error (the E is absent from WHERE), and the semicolon is missing after END.

After the CREATE PROCEDURE statement is entered and an error is returned, a SHOW ERRORS statement returns the following lines:


-------------- --------------------------------------------
3/27           PL/SQL-00103: Encountered the symbol "EMPNO" wh. . .
5/0            PL/SQL-00103: Encountered the symbol "END" when . . .
2 rows selected.

Notice that each line and column number where errors were found is listed by the SHOW ERRORS statement.

See Also:

Handling Run-Time PL/SQL Errors

Oracle Database allows user-defined errors in PL/SQL code to be handled so that user-specified error numbers and messages are returned to the client application. After received, the client application can handle the error based on the user-specified error number and message returned by Oracle Database.

User-specified error messages are returned using the RAISE_APPLICATION_ERROR procedure. For example:

RAISE_APPLICATION_ERROR(Error_number, 'text', Keep_error_stack)

This procedure stops subprogram execution, rolls back any effects of the subprogram, and returns a user-specified error number and message (unless the error is trapped by an exception handler). ERROR_NUMBER must be in the range of -20000 to -20999.

Use error number -20000 as a generic number for messages where it is important to relay information to the user, but having a unique error number is not required. Text must be a character expression, 2 Kbytes or less (longer messages are ignored). Keep_error_stack can be TRUE if you want to add the error to any already on the stack, or FALSE if you want to replace the existing errors. By default, this option is FALSE.


Some of the Oracle Database packages, such as DBMS_OUTPUT, DBMS_DESCRIBE, and DBMS_ALERT, use application error numbers in the range -20000 to -20005. See the descriptions of these packages for more information.

The RAISE_APPLICATION_ERROR procedure is often used in exception handlers or in the logic of PL/SQL code. For example, the following exception handler selects the string for the associated user-defined error message and invokes the RAISE_APPLICATION_ERROR procedure:

   SELECT Error_string INTO Message
   FROM Error_table,
   WHERE Error_number = -20101 AND Lang = v.value AND
      v.parameter = "NLS_LANGUAGE";
   Raise_application_error(-20101, Message);

See Also:

"Handling Errors in Remote Subprograms" for information on exception handling when invoking remote subprograms


Declaring Exceptions and Exception Handling Routines

User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application. When an exception is raised (signaled), the usual execution of the PL/SQL block stops, and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.

Application code can check for a condition that requires special attention using an IF statement. If there is an error condition, then two options are available:

  • Enter a RAISE statement that names the appropriate exception. A RAISE statement stops the execution of the subprogram, and control passes to an exception handler (if any).

  • Invoke the RAISE_APPLICATION_ERROR procedure to return a user-specified error number and message.

You can also define an exception handler to handle user-specified error messages. For example, Figure 7-1 shows the following:

  • An exception and associated exception handler in a subprogram

  • A conditional statement that checks for an error (such as transferring funds not available) and enters a user-specified error number and message within a trigger

  • How user-specified error numbers are returned to the invoking environment (in this case, a subprogram), and how that application can define an exception that corresponds to the user-specified error number

Declare a user-defined exception in a subprogram or package body (private exceptions), or in the specification of a package (public exceptions). Define an exception handler in the body of a subprogram (standalone or package).

Figure 7-1 Exceptions and User-Defined Errors

Description of Figure 7-1 follows
Description of "Figure 7-1 Exceptions and User-Defined Errors"

Unhandled Exceptions

In database PL/SQL program units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a COMMIT statement before the point at which the unhandled exception is observed, then the implicit rollback of the program unit can only be completed back to the previous COMMIT.

Additionally, unhandled exceptions in database-stored PL/SQL program units propagate back to client-side applications that invoke the containing program unit. In such an application, only the application program unit invocation is rolled back (not the entire application program unit), because it is submitted to the database as a SQL statement.

If unhandled exceptions in database PL/SQL program units are propagated back to database applications, modify the database PL/SQL code to handle the exceptions. Your application can also trap for unhandled exceptions when invoking database program units and handle such errors appropriately.

Handling Errors in Distributed Queries

You can use a trigger or a stored subprogram to create a distributed query. This distributed query is decomposed by the local Oracle Database instance into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes run the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

If a portion of a distributed statement fails, possibly due to a constraint violation, then Oracle Database returns error number ORA-02055. Subsequent statements, or subprogram invocations, return error number ORA-02067 until a rollback or a rollback to savepoint is entered.

Design your application to check for any returned error messages that indicates that a portion of the distributed update has failed. If you detect a failure, rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.

Handling Errors in Remote Subprograms

When a subprogram is run locally or at a remote location, four types of exceptions can occur:

  • PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION.

  • PL/SQL predefined exceptions, such as NO_DATA_FOUND.

  • SQL errors, such as ORA-00900 and ORA-02015.

  • Application exceptions, which are generated using the RAISE_APPLICATION_ERROR procedure.

When using local subprograms, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

    /* Handle the exception */

Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, then one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

    Null_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(Null_salary, -20101);
    RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
    WHEN Null_salary THEN

When invoking a remote subprogram, exceptions are also handled by creating a local exception handler. The remote subprogram must return an error number to the local invoking subprogram, which then handles the exception, as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local subprogram, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.

Debugging Stored Subprograms

Compiling a stored subprogram involves fixing any syntax errors in the code. You might need to do additional debugging to ensure that the subprogram works correctly, performs well, and recovers from errors. Such debugging might involve:



PL/Scope is a compiler-driven tool that collects and organizes data about user-defined identifiers from PL/SQL source code. Because PL/Scope is a compiler-driven tool, you use it through interactive development environments (such as SQL Developer and JDeveloper), rather than directly.

PL/Scope enables the development of powerful and effective PL/Scope source code browsers that increase PL/SQL developer productivity by minimizing time spent browsing and understanding source code.

For more information about PL/Scope, see Chapter 8, "Using PL/Scope".

PL/SQL Hierarchical Profiler

The PL/SQL hierarchical profiler reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls. It accounts for SQL and PL/SQL execution times separately. Each subprogram-level summary in the dynamic execution profile includes information such as number of calls to the subprogram, time spent in the subprogram itself, time spent in the subprogram's subtree (that is, in its descendent subprograms), and detailed parent-children information.

You can browse the generated HTML reports in any browser. The browser's navigational capabilities, combined with well chosen links, provide a powerful way to analyze performance of large applications, improve application performance, and lower development costs.

For a detailed description of PL/SQL hierarchical profiler, see Chapter 9, "Using the PL/SQL Hierarchical Profiler".

Oracle JDeveloper

Recent releases of Oracle JDeveloper have extensive features for debugging PL/SQL, Java, and multi-language programs. You can get Oracle JDeveloper as part of various Oracle product suites. Often, a more recent release is available as a download at


You can also debug stored subprograms and triggers using the Oracle Database package DBMS_OUTPUT. Put PUT and PUT_LINE statements in your code to output the value of variables and expressions to your terminal.

Privileges for Debugging PL/SQL and Java Stored Subprograms

Starting with Oracle Database 10g, a new privilege model applies to debugging PL/SQL and Java code running within the database. This model applies whether you are using Oracle JDeveloper, Oracle Developer, or any of the various third-party PL/SQL or Java development environments, and it affects both the DBMS_DEBUG and DBMS_DEBUG_JDWP APIs.

For a session to connect to a debugger, the effective user at the time of the connect operation must have the DEBUG CONNECT SESSION system privilege. This effective user might be the owner of a DR routine involved in making the connect call.

When a debugger becomes connected to a session, the session login user and the currently enabled session-level roles are fixed as the privilege environment for that debugging connection. Any DEBUG or EXECUTE privileges needed for debugging must be granted to that combination of user and roles.

  • To be able to display and change Java public variables or variables declared in a PL/SQL package specification, the debugging connection must be granted either EXECUTE or DEBUG privilege on the relevant code.

  • To be able to either display and change private variables or breakpoint and execute code lines step by step, the debugging connection must be granted DEBUG privilege on the relevant code


    The DEBUG privilege allows a debugging session to do anything that the subprogram being debugged could have done if that action had been included in its code.

In addition to these privilege requirements, the ability to stop on individual code lines and debugger access to variables are allowed only in code compiled with debug information generated. Use the PLSQL_DEBUG parameter and the DEBUG keyword on statements such as ALTER PACKAGE to control whether the PL/SQL compiler includes debug information in its results. If not, variables are not accessible, and neither stepping nor breakpoints stop on code lines. The PL/SQL compiler never generates debug information for code hidden with the PL/SQL wrap utility.

See Also:

Oracle Database PL/SQL Language Reference, for information about the wrap utility

The DEBUG ANY PROCEDURE system privilege is equivalent to the DEBUG privilege granted on all objects in the database. Objects owned by SYS are included if the value of the O7_DICTIONARY_ACCESSIBILITY parameter is TRUE.

A debug role mechanism is available to carry privileges needed for debugging that are not normally enabled in the session. See the documentation on the DBMS_DEBUG and DBMS_DEBUG_JDWP packages for details on how to specify a debug role and any necessary related password.

The JAVADEBUGPRIV role carries the DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE privileges. Grant it only with the care those privileges warrant.


Granting DEBUG ANY PROCEDURE privilege, or granting DEBUG privilege on any object owned by SYS, means granting complete rights to the database.

Writing Low-Level Debugging Code

If you are writing code for part of a debugger, you might need to use packages such as DBMS_DEBUG_JDWP or DBMS_DEBUG.


The DBMS_DEBUG_JDWP package, provided starting with Oracle9i Release 2, provides a framework for multi-language debugging that is expected to supersede the DBMS_DEBUG package over time. It is especially useful for programs that combine PL/SQL and Java.


The DBMS_DEBUG package, provided starting with Oracle8i, implements server-side debuggers and provides a way to debug server-side PL/SQL program units. Several of the debuggers available, such as Oracle Procedure Builder and various third-party vendor solutions, use this API.

See Also:

Invoking Stored Subprograms


You might need to set up data structures, similar to the following, for certain examples to work:
   Empno    NUMBER(4) NOT NULL,
   Ename    VARCHAR2(10),
   Job      VARCHAR2(9),
   Mgr      NUMBER(4),
   Hiredate DATE,
   Sal      NUMBER(7,2),
   Comm     NUMBER(7,2),
   Deptno   NUMBER(2));

      DELETE FROM Emp_tab WHERE Empno = Emp_id;

PL/SQL subprograms can be invoked from many different environments. For example:

The following topics include common examples of invoking subprograms from within these environments (except from an SQL statement, which is covered in "Invoking Stored PL/SQL Functions from SQL Statements"). For more information about invoking PL/SQL subprograms, including passing parameters, see Oracle Database PL/SQL Language Reference.


Privileges Required to Execute a Subprogram

If you are the owner of a standalone subprogram or package, then you can run the standalone subprogram or packaged subprogram, or any public subprogram or packaged subprogram at any time, as described in the previous sections. If you want to run a standalone or packaged subprogram owned by another user, then the following conditions apply:

  • You must have the EXECUTE privilege for the standalone subprogram or package containing the subprogram, or you must have the EXECUTE ANY PROCEDURE system privilege. If you are executing a remote subprogram, then you must be granted the EXECUTE privilege or EXECUTE ANY PROCEDURE system privilege directly, not through a role.

  • You must include the name of the owner in the invocation. For example:

    EXECUTE Jward.Fire_emp (1043);
    EXECUTE Jward.Hire_fire.Fire_emp (1043); 
  • If the subprogram is a definer's-rights (DR) subprogram, then it runs with the privileges of the subprogram owner. The owner must have all the necessary object privileges for any referenced objects.

  • If the subprogram is an invoker's-rights (IR) subprogram, then it runs with your privileges (as the invoker). In this case, you also need privileges on all referenced objects; that is, all objects accessed by the subprogram through external references that are resolved in your schema. You might hold these privileges directly or through a role. Roles are enabled unless an IR subprogram is invoked directly or indirectly by a DR subprogram.


You might need to set up the following data structures for certain examples to work:

Invoking a Subprogram from a Trigger or Another Subprogram

A subprogram or trigger can invoke another stored subprogram. For example, included in the body of one subprogram might be the following line:

. . .
Sal_raise(Emp_id, 200);
. . .

This line invokes the Sal_raise procedure. Emp_id is a variable within the context of the procedure. Recursive subprogram invocations are allowed within PL/SQL: A subprogram can invoke itself.

Invoking a Subprogram Interactively from Oracle Database Tools

A subprogram can be invoked interactively from an Oracle Database tool, such as SQL*Plus. For example, to invoke a procedure named SAL_RAISE, owned by you, you can use an anonymous PL/SQL block, as follows:

   Sal_raise(7369, 200);


Interactive tools, such as SQL*Plus, require you to follow these lines with a slash (/) to run the PL/SQL block.

An easier way to run a block is to use the SQL*Plus statement EXECUTE, which wraps BEGIN and END statements around the code you enter. For example:

EXECUTE Sal_raise(7369, 200);

Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:

VARIABLE Assigned_empno NUMBER

After defined, any session variable can be used for the duration of the session. For example, you might run a function and capture the return value using a session variable:

EXECUTE :Assigned_empno := Hire_emp('JSMITH', 'President', 
   1032, SYSDATE, 5000, NULL, 10);
PRINT Assigned_empno;

See Also:

  • SQL*Plus User's Guide and Reference for information about the EXECUTE command

  • Your tools documentation for information about performing similar operations using your development tool

Invoking a Subprogram from a 3GL Application

A 3GL database application, such as a precompiler or an OCI application, can include an invocation to a subprogram within the code of the application.

To run a subprogram within a PL/SQL block in an application, simply invoke the subprogram. The following line within a PL/SQL block invokes the Fire_emp procedure:


In this case, :Empno is a host (bind) variable within the context of the application.

To run a subprogram within the code of a precompiler application, you must use the EXEC call interface. For example, the following statement invokes the Fire_emp procedure in the code of a precompiler application:


See Also:

For information about invoking PL/SQL subprograms from within 3GL applications:

Invoking Remote Subprograms

Invoke remote subprograms using an appropriate database link and the subprogram name. The following SQL*Plus statement runs the procedure Fire_emp located in the database and pointed to by the local database link named BOSTON_SERVER:

EXECUTE fire_emp1@boston_server(1043);

See Also:

"Handling Errors in Remote Subprograms" for information on exception handling when invoking remote subprograms


Remote Subprogram Invocations and Parameter Values

You must explicitly pass values to all remote subprogram parameters, even if there are defaults. You cannot access remote package variables and constants.

Referencing Remote Objects

Remote objects can be referenced within the body of a locally defined subprogram. The following procedure deletes a row from the remote employee table:

    DELETE FROM emp@boston_server WHERE empno = emp_id;

The following list explains how to properly invoke remote subprograms, depending on the invoking environment.

  • Remote subprograms (standalone and packaged) can be invoked from within a subprogram, an OCI application, or a precompiler application by specifying the remote subprogram name, a database link, and the arguments for the remote subprogram.

  • In the previous example, you can create a synonym for FIRE_EMP1@BOSTON_SERVER. This enables you to invoke the remote subprogram from an Oracle Database tool application, such as a SQL*Forms application, as well from within a subprogram, OCI application, or precompiler application.

    CREATE SYNONYM synonym1 for  fire_emp1@boston_server;
  • If you do not want to use a synonym, then you can write a local cover subprogram to invoke the remote subprogram.

       arg NUMBER;

    Here, local_procedure is defined as in the first item of this list.


    Unlike stored subprograms, which use compile-time binding, run-time binding is used when referencing remote subprograms. The user account to which you connect depends on the database link.

All invocations to remotely stored subprograms are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote subprogram is read-only). Furthermore, if a transaction that includes a remote subprogram invocation is rolled back, then the work done by the remote subprogram is also rolled back.

A subprogram invoked remotely can usually execute a COMMIT, ROLLBACK, or SAVEPOINT statement, the same as a local subprogram. However, there are some differences in action:

  • If the transaction was originated by a database that is not an Oracle database, as might be the case in XA applications, these operations are not allowed in the remote subprogram.

  • After doing one of these operations, the remote subprogram cannot start any distributed transactions of its own.

  • If the remote subprogram does not commit or roll back its work, the commit is done implicitly when the database link is closed. In the meantime, further invocations to the remote subprogram are not allowed because it is still considered to be performing a transaction.

A distributed update modifies data on two or more databases. A distributed update is possible using a subprogram that includes two or more remote updates that access data on different databases. Statements in the construct are sent to the remote databases, and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, then a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating subprograms that perform distributed updates.

Pay special attention when using a local subprogram that invokes a remote subprogram. If a timestamp mismatch is found during execution of the local subprogram, then the remote subprogram is not run, and the local subprogram is invalidated.

Synonyms for Subprograms and Packages

Synonyms can be created for standalone subprograms and packages to do the following:

  • Hide the identity of the name and owner of a subprogram or package.

  • Provide location transparency for remotely stored subprograms (standalone or within a package).

When a privileged user needs to invoke a subprogram, an associated synonym can be used. Because the subprograms defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual subprograms within a package.

For more information about synonyms, see Oracle Database Concepts.

Invoking Stored PL/SQL Functions from SQL Statements

To be invoked from a SQL statement, a stored PL/SQL function must be declared either at schema level or in a package specification.

The following SQL statements can invoke stored PL/SQL functions:

To invoke a PL/SQL subprogram from SQL, you must either own or have EXECUTE privileges on the subprogram. To select from a view defined with a PL/SQL function, you must have SELECT privileges on the view. No separate EXECUTE privileges are necessary to select from the view.

For general information about invoking subprograms, including passing parameters, see Oracle Database PL/SQL Language Reference.


Why Invoke Stored PL/SQL Subprograms from SQL Statements?

Invoking PL/SQL subprograms in SQL statements enables you to do the following:

  • Increase user productivity by extending SQL.

    Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.

  • Increase query efficiency.

    Functions used in the WHERE clause of a query can filter data using criteria that must otherwise be evaluated by the application.

  • Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).

  • Provide parallel query execution.

    If the query is parallelized, then SQL statements in your PL/SQL subprogram might also be run in parallel (using the parallel query option).

Where PL/SQL Functions Can Appear in SQL Statements

A PL/SQL function can appear in a SQL statement wherever a built-in SQL function or an expression can appear in a SQL statement. For example, a PL/SQL function can appear in the following:

  • Select list of the SELECT statement

  • Condition of the WHERE or HAVING clause


  • VALUES clause of the INSERT statement

  • SET clause of the UPDATE statement

A PL/SQL table function (which returns a collection of rows) can appear in a SELECT statement in place of the following:

  • Column name in the SELECT list

  • Table name in the FROM clause

A PL/SQL function cannot appear in the following contexts, which require unchanging definitions:

  • CHECK constraint clause of a CREATE or ALTER TABLE statement

  • Default value specification for a column

When PL/SQL Functions Can Appear in SQL Expressions

To be invoked from a SQL expression, a PL/SQL function must satisfy the following additional requirements:

  • It must be a row function, not a column (group) function; that is, its argument cannot be an entire column.

  • Its formal parameters must be IN parameters, not OUT or IN OUT parameters.

  • Its formal parameters and its result value (if any) must have Oracle built-in datatypes (such as CHAR, DATE, or NUMBER), not PL/SQL datatypes (such as BOOLEAN, RECORD, or TABLE).

    There is an exception to this rule: A formal parameter can have a PL/SQL datatype if the corresponding actual parameter is implicitly converted to the datatype of the formal parameter (as in Example 7-6).

The function in Example 7-5 satisfies the preceding requirements. It uses the table payroll:

CREATE TABLE payroll (srate  NUMBER,
                      orate  NUMBER,
                      acctno NUMBER);

Example 7-5 PL/SQL Function that Can Appear in a SQL Expression

CREATE FUNCTION gross_pay (emp_id IN NUMBER,
                           st_hrs IN NUMBER DEFAULT 40,
                           ot_hrs IN NUMBER DEFAULT 0)
  st_rate  NUMBER;
  ot_rate  NUMBER;

   SELECT srate, orate INTO st_rate, ot_rate FROM payroll
      WHERE acctno = emp_id;
   RETURN st_hrs * st_rate + ot_hrs * ot_rate;
END gross_pay;

In the SQL*Plus script in Example 7-6, the SQL statement CALL invokes the PL/SQL function f1, whose formal parameter has PL/SQL datatype PLS_INTEGER. The CALL statement succeeds because the actual parameter, 2, is implicitly converted to the datatype PLS_INTEGER. If the actual parameter had a value outside the range of PLS_INTEGER, the CALL statement would fail.

Example 7-6 PL/SQL Function with Formal Parameter of PL/SQL Datatype, Invoked from a SQL Expression

      WHEN b > 0 THEN 1
      WHEN b <= 0 THEN -1
END f1;
CALL f1(b=>2) INTO :x

Controlling Side Effects

The purity of a stored subprogram refers to the side effects of that subprogram on database tables or package variables. Side effects can prevent the parallelization of a query, yield order-dependent (and therefore, indeterminate) results, or require that package state be maintained across user sessions. Various side effects are not allowed when a function is invoked from a SQL query or DML statement.

In releases prior to Oracle8i, Oracle Database leveraged the PL/SQL compiler to enforce restrictions during the compilation of a stored subprogram or a SQL statement. Starting with Oracle8i, the compile-time restrictions were relaxed, and a smaller set of restrictions are enforced during execution.

This change provides uniform support for stored subprograms written in PL/SQL, Java, and C, and it allows programmers the most flexibility possible.



When a SQL statement is run, checks are made to see if it is logically embedded within the execution of an already running SQL statement. This occurs if the statement is run from a trigger or from a subprogram that was in turn invoked from the already running SQL statement. In these cases, further checks occur to determine if the new SQL statement is safe in the specific context.

The following restrictions are enforced on subprograms:

  • A subprogram invoked from a query or DML statement might not end the current transaction, create or rollback to a savepoint, or ALTER the system or session.

  • A subprogram invoked from a query (SELECT) statement or from a parallelized DML statement might not execute a DML statement or otherwise modify the database.

  • A subprogram invoked from a DML statement might not read or modify the particular table being modified by that DML statement.

These restrictions apply regardless of what mechanism is used to run the SQL statement inside the subprogram or trigger. For example:

  • They apply to a SQL statement invoked from PL/SQL, whether embedded directly in a subprogram or trigger body, run using the native dynamic mechanism (EXECUTE IMMEDIATE), or run using the DBMS_SQL package.

  • They apply to statements embedded in Java with SQLJ syntax or run using JDBC.

  • They apply to statements run with OCI using the callback context from within an "external" C function.

You can avoid these restrictions if the execution of the new SQL statement is not logically embedded in the context of the already running statement. PL/SQL's autonomous transactions provide one escape (see "Autonomous Transactions" ). Another escape is available using Oracle Call Interface (OCI) from an external C function, if you create a new connection rather than using the handle available from the OCIExtProcContext argument.

Declaring a Function

You can use the keywords DETERMINISTIC and PARALLEL_ENABLE in the syntax for declaring a function. These are optimization hints that inform the query optimizer and other software components about the following:

  • Functions that need not be invoked redundantly

  • Functions permitted within a parallelized query or parallelized DML statement

Only functions that are DETERMINISTIC are allowed in function-based indexes and in certain snapshots and materialized views.

A deterministic function depends solely on the values passed into it as arguments and does not reference or modify the contents of package variables or the database or have other side-effects. Such a function produces the same result value for any combination of argument values passed into it.

You place the DETERMINISTIC keyword after the return value type in a declaration of the function. For example:

  RETURN P1 * 2; 

You might place this keyword in the following places:

  • On a function defined in a CREATE FUNCTION statement

  • In a function declaration in a CREATE PACKAGE statement

  • On a method declaration in a CREATE TYPE statement

Do not repeat the keyword on the function or method body in a CREATE PACKAGE BODY or CREATE TYPE BODY statement.

Certain performance optimizations occur on invocations of functions that are marked DETERMINISTIC without any other action being required. The following features require that any function used with them be declared DETERMINISTIC:

  • Any user-defined function used in a function-based index.

  • Any function used in a materialized view, if that view is to qualify for Fast Refresh or is marked ENABLE QUERY REWRITE.

The preceding functions features attempt to use previously calculated results rather than invoking the function when it is possible to do so.

It is good programming practice to make functions that fall in the following categories DETERMINISTIC:

  • Functions used in a WHERE, ORDER BY, or GROUP BY clause

  • Functions that MAP or ORDER methods of a SQL type

  • Functions that help determine whether or where a row appears in a result set

Keep the following points in mind when you create DETERMINISTIC functions:

  • The database cannot recognize if the action of the function is indeed deterministic. If the DETERMINISTIC keyword is applied to a function whose action is not truly deterministic, then the result of queries involving that function is unpredictable.

  • If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.

See Also:

Oracle Database SQL Language Reference for an account of CREATE FUNCTION restrictions

Parallel Query and Parallel DML

Oracle Database's parallel execution feature divides the work of executing a SQL statement across multiple processes. Functions invoked from a SQL statement that is run in parallel might have a separate copy run in each of these processes, with each copy invoked for only the subset of rows that are handled by that process.

Each process has its own copy of package variables. When parallel execution begins, these are initialized based on the information in the package specification and body as if a new user is logging into the system; the values in package variables are not copied from the original login session. And changes made to package variables are not automatically propagated between the various sessions or back to the original session. Java STATIC class attributes are similarly initialized and modified independently in each process. Because a function can use package (or Java STATIC) variables to accumulate some value across the various rows it encounters, Oracle Database cannot assume that it is safe to parallelize the execution of all user-defined functions.

For SELECT statements in Oracle Database versions prior to 8.1.5, the parallel query optimization allowed functions noted as both RNPS and WNPS in a PRAGMA RESTRICT_REFERENCES declaration to run in parallel. Functions defined with CREATE FUNCTION statements had their code implicitly examined to determine if they were pure enough; parallelized execution might occur even though a pragma cannot be specified on these functions.

For DML statements in Oracle Database versions prior to 8.1.5, the parallelization optimization looked to see if a function was noted as having all four of RNDS, WNDS, RNPS and WNPS specified in a PRAGMA RESTRICT_REFERENCES declaration; those functions that were marked as neither reading nor writing to either the database or package variables could run in parallel. Again, those functions defined with a CREATE FUNCTION statement had their code implicitly examined to determine if they were actually pure enough; parallelized execution might occur even though a pragma cannot be specified on these functions.

Oracle Database versions 8.1.5 and later continue to parallelize those functions that earlier versions recognize as parallelizable. The PARALLEL_ENABLE keyword is the preferred way to mark your code as safe for parallel execution. This keyword is syntactically similar to DETERMINISTIC as described in "Declaring a Function"; it is placed after the return value type in a declaration of the function, as in:

  RETURN P1 * 2; 

A PL/SQL function defined with CREATE FUNCTION might still be run in parallel without any explicit declaration that it is safe to do so, if the system can determine that it neither reads nor writes package variables nor invokes any function that might do so. A Java method or C function is never seen by the system as safe to run in parallel, unless the programmer explicitly indicates PARALLEL_ENABLE on the call specification, or provides a PRAGMA RESTRICT_REFERENCES indicating that the function is sufficiently pure.

An additional run-time restriction is imposed on functions run in parallel as part of a parallelized DML statement. Such a function is not permitted to in turn execute a DML statement; it is subject to the same restrictions that are enforced on functions that are run inside a query (SELECT) statement.

See Also:


PRAGMA RESTRICT_REFERENCES for Backward Compatibility

In Oracle Database versions prior to 8.1.5 (Oracle8i), programmers used the pragma RESTRICT_REFERENCES to assert the purity level of a subprogram. In subsequent versions, use the hints PARALLEL-ENABLE and DETERMINISTIC, instead, to communicate subprogram purity to Oracle Database.

You can remove RESTRICT_REFERENCES from your code. However, this pragma remains available for backward compatibility in situations where one of the following is true:

  • It is impossible or impractical to edit existing code to remove RESTRICT_REFERENCES completely. If you do not remove it from a subprogram S1 that depends on another subprogram S2, then RESTRICT_REFERENCES might also be needed in S2, so that S1 will compile.

  • Replacing RESTRICT_REFERENCES in existing code with hints parallel-enable and deterministic would negatively affect the action of new, dependent code. Use RESTRICT_REFERENCES to preserve the action of the existing code.

An existing PL/SQL application can thus continue using the pragma even on new functionality, to ease integration with the existing code. Do not use the pragma in a wholly new application.

If you use the pragma RESTRICT_REFERENCES, place it in a package specification, not in a package body. It must follow the declaration of a subprogram, but it need not follow immediately. Only one pragma can reference a given subprogram declaration.

To code the pragma RESTRICT_REFERENCES, use the following syntax:

    Function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] ); 


Option Description
WNDS The subprogram writes no database state (does not modify database tables).
RNDS The subprogram reads no database state (does not query database tables).
WNPS The subprogram writes no package state (does not change the values of packaged variables).
RNPS The subprogram reads no package state (does not reference the values of packaged variables).
TRUST The other restrictions listed in the pragma are not enforced; they are simply assumed to be true. This allows easy invocation from functions that have RESTRICT_REFERENCES declarations to those that do not.

You can pass the arguments in any order. If any SQL statement inside the subprogram body violates a rule, then you get an error when the statement is parsed.

In the following example, the function compound neither reads nor writes database or package state; therefore, you can assert the maximum purity level. Always assert the highest purity level that a subprogram allows. That way, the PL/SQL compiler never rejects the subprogram unnecessarily.


You might need to set up the following data structures for certain examples here to work:
 Yrs      NUMBER,
 Amt      NUMBER,
 Acctno   NUMBER,
 Rte      NUMBER);
CREATE PACKAGE Finance AS  -- package specification 
   FUNCTION Compound 
         (Years  IN NUMBER, 
          Amount IN NUMBER, 
          Rate   IN NUMBER) RETURN NUMBER; 
END Finance; 
CREATE PACKAGE BODY Finance AS  --package body 
   FUNCTION Compound 
         (Years  IN NUMBER, 
          Amount IN NUMBER, 
      RETURN Amount * POWER((Rate / 100) + 1, Years); 
   END Compound; 
                   -- no pragma in package body 
END Finance; 

Later, you might invoke compound from a PL/SQL block, as follows:

   Interest NUMBER;
   Acct_id NUMBER;
   SELECT Finance.Compound(Yrs, Amt, Rte)  -- function invocation
   INTO   Interest       
   FROM   Accounts       
   WHERE  Acctno = Acct_id; 


Using the Keyword TRUST

The keyword TRUST in the RESTRICT_REFERENCES syntax allows easy invocation from functions that have RESTRICT_REFERENCES declarations to those that do not. When TRUST is present, the restrictions listed in the pragma are not actually enforced, but rather are simply assumed to be true.

When invoking a function from a section of code that is using pragmas to one that is not, there are two likely usage styles. One is to place a pragma on the routine to be invoked, for example on a call specification for a Java method. Then, invocations from PL/SQL to this method complain if the method is less restricted than the invoking subprogram. For example:

      LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; 

      RETURN F1(P1); 

Here, F2 can invoke F1, as F1 was declared to be WNDS.

The other approach is to mark only the invoker, which might then invoke any subprogram without complaint. For example:

      LANGUAGE JAVA NAME 'CLASS1.METHODNAME(int) return int'; 
      RETURN F1(P1); 

Here, F2 can invoke F1 because while F2 is promised to be WNDS (because TRUST is specified), the body of F2 is not actually examined to determine if it truly satisfies the WNDS restriction. Because F2 is not examined, its invocation of F1 is allowed, even though there is no PRAGMA RESTRICT_REFERENCES for F1.

Differences between Static and Dynamic SQL Statements

Static INSERT, UPDATE, and DELETE statements do not violate RNDS if these statements do not explicitly read any database states, such as columns of a table. However, dynamic INSERT, UPDATE, and DELETE statements always violate RNDS, regardless of whether or not the statements explicitly read database states.

The following INSERT violates RNDS if it is executed dynamically, but it does not violate RNDS if it is executed statically.

INSERT INTO my_table values(3, 'SCOTT'); 

The following UPDATE always violates RNDS statically and dynamically, because it explicitly reads the column name of my_table.

UPDATE my_table SET id=777 WHERE name='SCOTT';
Overloading Packaged PL/SQL Functions

PL/SQL lets you overload packaged (but not standalone) functions: You can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. Therefore, a pragma that references the name of overloaded functions always applies to the nearest preceding function declaration.

In this example, the pragma applies to the second declaration of valid:


Serially Reusable PL/SQL 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 (using pragma syntax).

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. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore 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.


Package States

The state of a nonreusable package (one not marked SERIALLY_REUSABLE) persists for the lifetime of a session. A package state includes global variables, cursors, and so on.

The state of a serially reusable package persists only for the lifetime of a call to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, then Oracle Database creates a new instantiation of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous calls to the server are not visible.


Creating a new instantiation of a serially reusable package on a call to the server does not necessarily imply that Oracle Database allocates memory or configures the instantiation object. Oracle Database looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in the SGA.

At the end of the call to the server, this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.

Why Serially Reusable Packages?

Because the state of a nonreusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In some applications, such as Oracle Office, a log-on session typically exists for days. Applications often need to use certain packages only for short periods of the session. Ideally, such applications could de-instantiate the package state in after they finish using the package (the middle of the session).

SERIALLY_REUSABLE packages enable you to design applications that manage memory better for scalability. Package states that matter only for the duration of a call to the server can be captured in SERIALLY_REUSABLE packages.

Syntax of Serially Reusable Packages

A package can be marked serially reusable by a pragma. The syntax of the pragma is:


A package specification can be marked serially reusable, whether or not it has a corresponding package body. If the package has a body, then the body must have the serially reusable pragma, if its corresponding specification has the pragma; it cannot have the serially reusable pragma unless the specification also has the pragma.

Semantics of Serially Reusable Packages

A package that is marked SERIALLY_REUSABLE has the following properties:

  • Its package variables are meant for use only within the work boundaries, which correspond to calls to the server (either OCI call boundaries or PL/SQL RPC calls to the server).


    If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.
  • A pool of package instantiations is kept, and whenever a "unit of work" needs this package, one of the instantiations is "reused", as follows:

    • The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).

    • The initialization code in the package body is run again.

  • At the "end work" boundary, cleanup is done.

    • If any cursors were left open, then they are silently closed.

    • Some nonreusable secondary memory is freed (such as memory for collection variables or long VARCHAR2s).

    • This package instantiation is returned back to the pool of reusable instantiations kept for this package.

  • Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are invoked from SQL statements. If you try, then Oracle Database generates an error.

Examples of Serially Reusable Packages

Example 1: How Package Variables Act Across Call Boundaries

This example has a serially reusable package specification (there is no body).


  N NUMBER := 5;                -- default initialization 
END Sr_pkg; 

Suppose your Enterprise Manager (or SQL*Plus) application issues the following:


# first CALL to server 
   Sr_pkg.N := 10; 

# second CALL to server 

This program prints:



If the package had not had the pragma SERIALLY_REUSABLE, the program would have printed '10'.
Example 2: How Package Variables Act Across Call Boundaries

This example has both a package specification and package body, which are serially reusable.


   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;
   -- the body is required to have the pragma because the
  -- specification of this package has the pragma
   PROCEDURE Print_pkg IS
      DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);
      DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);
      DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);
      FOR i IN 1..Sr_pkg.Str_tab.Count LOOP
      END LOOP;
   PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
   -- init the package globals
      Sr_pkg.Num := N;
      Sr_pkg.Str := V;
      FOR i IN 1..n LOOP
         Sr_pkg.Str_tab(i) := V || ' ' || i;
   -- print the package
 END Sr_pkg;


Rem SR package access in a CALL:

   -- initialize and print the package
   DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');
   Sr_pkg.Init_and_print_pkg(4, 'abracadabra');
   -- print it in the same call to the server.
   -- we should see the initialized values.
   DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');

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

REM SR package access in subsequent CALL:
   -- print the package in the next call to the server.
   -- The package state should be reset to the initial (default) values.
   DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0
Example 3: Open Cursors in Serially Reusable Packages at Call Boundaries

This example demonstrates that any open cursors in serially reusable packages get closed automatically at the end of a work boundary (which is a call). Also, in a new call, these cursors need to be opened again.

REM  For serially reusable pkg: At the end work boundaries
REM  (which is currently the OCI call boundary) all open
REM  cursors will be closed.
REM  Because the cursor is closed - every time we fetch we 
REM  will start at the first row again.

CREATE TABLE People (Name VARCHAR2(20));
END Sr_pkg;
Name VARCHAR2(200);
   IF (Sr_pkg.C%ISOPEN) THEN
      DBMS_OUTPUT.PUT_LINE('cursor is already open.');
      DBMS_OUTPUT.PUT_LINE('cursor is closed; opening now.');
      OPEN Sr_pkg.C;
   END IF;
   -- fetching from cursor.
   FETCH sr_pkg.C INTO name;
   DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
   FETCH Sr_pkg.C INTO name;
   DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
   -- Oops forgot to close the cursor (Sr_pkg.C).
   -- But, because it is a Serially Reusable pkg's cursor, 
   -- it will be closed at the end of this CALL to the server.
EXECUTE fetch_from_cursor;
cursor is closed; opening now.
fetched: ET
fetched: RAMBO

Returning Large Amounts of Data from a Function

In a data warehousing environment, you might use a PL/SQL function to transform large amounts of data. Perhaps the data is passed through a series of transformations, each performed by a different function. PL/SQL table functions let you perform such transformations without significant memory overhead or the need to store the data in tables between each transformation stage. These functions can accept and return multiple rows, can return rows as they are ready rather than all at once, and can be parallelized.

In this technique:

For example:

CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED IS
  out_rec TickerType := TickerType(NULL,NULL,NULL);
  in_rec p%ROWTYPE;
-- Function accepts multiple rows through a REF CURSOR argument.
    FETCH p INTO in_rec;
-- Return value is a record type that matches the table definition.
    out_rec.ticker := in_rec.Ticker;
    out_rec.PriceType := 'O';
    out_rec.price := in_rec.OpenPrice;
-- Once a result row is ready, we send it back to the calling program,
-- and continue processing.
    PIPE ROW(out_rec);
-- This function outputs twice as many rows as it receives as input.
    out_rec.PriceType := 'C';
    out_rec.Price := in_rec.ClosePrice;
    PIPE ROW(out_rec);
  CLOSE p;
-- The function ends with a RETURN statement that does not specify any value.

-- Here we use the result of this function in a SQL query.

-- Here we use the result of this function in a PL/SQL block.
  total NUMBER := 0;
  price_type VARCHAR2(1);
  FOR item IN (SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable))))
-- Access the values of each output row.
-- We know the column names based on the declaration of the output type.
-- This computation is just for illustration.
    total := total + item.price;
    price_type := item.price_type;

Coding Your Own Aggregate Functions

To analyze a set of rows and compute a result value, you can code your own aggregate function that works the same as a built-in aggregate like SUM:

See Also:

Oracle Database Data Cartridge Developer's Guide for more information about user-defined aggregate functions