11 Tuning PL/SQL Applications for Performance

When to Tune PL/SQL Code

The information in this chapter is especially valuable if you are responsible for:

• Programs that do a lot of mathematical calculations. You will want to investigate the datatypes PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE.

• Functions that are called from PL/SQL queries, where the functions might be executed millions of times. You will want to look at all performance features to make the function as efficient as possible, and perhaps a function-based index to precompute the results for each row and save on query time.

• Programs that spend a lot of time processing INSERT, UPDATE, or DELETE statements, or looping through query results. You will want to investigate the FORALL statement for issuing DML, and the BULK COLLECT INTO and RETURNING BULK COLLECT INTO clauses for queries.

• Older code that does not take advantage of recent PL/SQL language features. With the many performance improvements in Oracle Database 10g, any code from earlier releases is a candidate for tuning.

• Any program that spends a lot of time doing PL/SQL processing, as opposed to issuing DDL statements like CREATE TABLE that are just passed directly to SQL. You will want to investigate native compilation. Because many built-in database features use PL/SQL, you can apply this tuning feature to an entire database to improve performance in many areas, not just your own code.

Before starting any tuning effort, benchmark the current system and measure how long particular subprograms take. PL/SQL in Oracle Database 10g includes many automatic optimizations, so you might see performance improvements without doing any tuning.

Avoiding CPU Overhead in PL/SQL Code

This sections discusses how you can avoid excessive CPU overhead in the PL/SQL code.

BEGIN
-- Inefficient, calls function for every row
   FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees)
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
-- Efficient, only calls function once for each distinct value.
   FOR item IN
   ( SELECT SQRT(department_id) col_alias FROM
     ( SELECT DISTINCT department_id FROM employees)
   )
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
END;
/

Avoiding Memory Overhead in PL/SQL Code

This sections discusses how you can avoid excessive memory overhead in the PL/SQL code.

Using The Profiler API: Package DBMS_PROFILER

The Profiler API is implemented as PL/SQL package DBMS_PROFILER, which provides services for gathering and saving run-time statistics. The information is stored in database tables, which you can query later. For example, you can learn how much time was spent executing each PL/SQL line and subprogram.

To use the Profiler, you start the profiling session, run your application long enough to get adequate code coverage, flush the collected data to the database, then stop the profiling session.

The Profiler traces the execution of your program, computing the time spent at each line and in each subprogram. You can use the collected data to improve performance. For instance, you might focus on subprograms that run slowly. For information about the DBMS_PROFILER subprograms, see Oracle Database PL/SQL Packages and Types Reference.

After you have collected data with the Profiler, you can do the following:

• Analyze the Collected Performance Data

  Determine why more time was spent executing certain code segments or accessing certain data structures. Find the problem areas by querying the performance data. Focus on the subprograms and packages that use up the most execution time, inspecting possible performance bottlenecks such as SQL statements, loops, and recursive functions.

• Use Trace Data to Improve Performance

  Use the results of your analysis to rework slow algorithms. For example, due to an exponential growth in data, you might need to replace a linear search with a binary search. Also, look for inefficiencies caused by inappropriate data structures, and, if necessary, replace those data structures.

Using The Trace API: Package DBMS_TRACE

With large, complex applications, it becomes difficult to keep track of calls between subprograms. By tracing your code with the Trace API, you can see the order in which subprograms execute. The Trace API is implemented as PL/SQL package DBMS_TRACE, which provides services for tracing execution by subprogram or exception.

To use Trace, you start the tracing session, run your application, then stop the tracing session. As the program executes, trace data is collected and stored in database tables.

For information about the DBMS_TRACE subprograms, see Oracle Database PL/SQL Packages and Types Reference.

Using the FORALL Statement

The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop. For full syntax and restrictions, see "FORALL Statement".

The SQL statement can reference more than one collection, but FORALL only improves performance where the index value is used as a subscript.

Usually, the bounds specify a range of consecutive index numbers. If the index numbers are not consecutive, such as after you delete collection elements, you can use the INDICES OF or VALUES OF clause to iterate over just those index values that really exist.

The INDICES OF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound.

The VALUES OF clause refers to a collection that is indexed by BINARY_INTEGER or PLS_INTEGER and whose elements are of type BINARY_INTEGER or PLS_INTEGER. The FORALL statement iterates over the index values specified by the elements of this collection.

The FORALL statement in Example 11-2 sends all three DELETE statements to the SQL engine at once.

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS VARRAY(20) OF NUMBER;
   depts NumList := NumList(10, 30, 70);  -- department numbers
BEGIN
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM employees_temp WHERE department_id = depts(i);
   COMMIT;
END;
/

Example 11-3 loads some data into PL/SQL collections. Then it inserts the collection elements into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
  pnums  NumTab;
  pnames NameTab;
  iterations CONSTANT PLS_INTEGER := 500;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load index-by tables
     pnums(j) := j;
     pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;
  t1 := DBMS_UTILITY.get_time;
  FOR i IN 1..iterations LOOP  -- use FOR loop
     INSERT INTO parts1 VALUES (pnums(i), pnames(i));
  END LOOP;
  t2 := DBMS_UTILITY.get_time;
  FORALL i IN 1..iterations  -- use FORALL statement
     INSERT INTO parts2 VALUES (pnums(i), pnames(i));
  t3 := DBMS_UTILITY.get_time;
  DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
  DBMS_OUTPUT.PUT_LINE('---------------------');
  DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100));
  DBMS_OUTPUT.PUT_LINE('FORALL:   ' || TO_CHAR((t3 - t2)/100));
  COMMIT;
END;
/

The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements, as shown in Example 11-4.

CREATE TABLE employees_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS VARRAY(10) OF NUMBER;
   depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN
   FORALL j IN 4..7  -- use only part of varray
      DELETE FROM employees_temp WHERE department_id = depts(j);
   COMMIT;
END;
/

You might need to delete some elements from a collection before using the collection in a FORALL statement. The INDICES OF clause processes sparse collections by iterating through only the remaining elements.

You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the VALUES OF clause lets you set up simple collections whose elements serve as pointers to elements in the original collection.

Example 11-5 creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that would not work in a default FORALL statement. The program uses a FORALL statement with the INDICES OF clause to insert the data into a table. It then sets up two more collections, pointing to certain elements from the original collection. The program stores each set of names in a different database table using FORALL statements with the VALUES OF clause.

-- Create empty tables to hold order details
CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
DECLARE
-- Make collections to hold a set of customer names and order amounts.
   SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
   TYPE cust_typ IS TABLe OF cust_name;
   cust_tab cust_typ;
   SUBTYPE order_amount IS valid_orders.amount%TYPE;
   TYPE amount_typ IS TABLE OF NUMBER;
   amount_tab amount_typ;
-- Make other collections to point into the CUST_TAB collection.
   TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
   big_order_tab index_pointer_t := index_pointer_t();
   rejected_order_tab index_pointer_t := index_pointer_t();
   PROCEDURE setup_data IS BEGIN
 -- Set up sample order data, including some invalid orders and some 'big' orders.
     cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
     amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
   END;
BEGIN
   setup_data();
   DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
   FOR i IN 1..cust_tab.LAST LOOP
     DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                           amount_tab(i));
   END LOOP;
-- Delete invalid orders (where amount is null or 0).
   FOR i IN 1..cust_tab.LAST LOOP
     IF amount_tab(i) is null or amount_tab(i) = 0 THEN
        cust_tab.delete(i);
        amount_tab.delete(i);
     END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
   FOR i IN 1..cust_tab.LAST LOOP
     IF cust_tab.EXISTS(i) THEN
       DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||
                             amount_tab(i));
      END IF;
   END LOOP;
-- Because the subscripts of the collections are not consecutive, use
-- FORALL...INDICES OF to iterate through the actual subscripts, 
-- rather than 1..COUNT
   FORALL i IN INDICES OF cust_tab
     INSERT INTO valid_orders(cust_name, amount) 
        VALUES(cust_tab(i), amount_tab(i));
-- Now process the order data differently
-- Extract 2 subsets and store each subset in a different table
   setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again.
   FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
     IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
       rejected_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
       rejected_order_tab(rejected_order_tab.LAST) := i; 
     END IF;
     IF amount_tab(i) > 2000 THEN
        big_order_tab.EXTEND; -- Add a new element to this collection
-- Record the subscript from the original collection
        big_order_tab(big_order_tab.LAST) := i;
     END IF;
   END LOOP;
-- Now it's easy to run one DML statement on one subset of elements, 
-- and another DML statement on a different subset.
   FORALL i IN VALUES OF rejected_order_tab
     INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
   FORALL i IN VALUES OF big_order_tab
     INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
   COMMIT;
END;
/
-- Verify that the correct order details were stored
SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders;
SELECT cust_name "Customer", amount "Big order amount" FROM big_orders;
SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;

CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18));
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30);
BEGIN
  INSERT INTO emp_temp VALUES(10, 'Clerk');
-- Lengthening this job title causes an exception
  INSERT INTO emp_temp VALUES(20, 'Bookkeeper');
  INSERT INTO emp_temp VALUES(30, 'Analyst');
  COMMIT;
  FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements.
    UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Problem in the FORALL statement.');
    COMMIT; -- Commit results of successful updates.
END;
/

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(30, 50, 60);
BEGIN
   FORALL j IN depts.FIRST..depts.LAST
      DELETE FROM emp_temp WHERE department_id = depts(j);
-- How many rows were affected by each DELETE statement?
   FOR i IN depts.FIRST..depts.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Iteration #' || i || ' deleted ' ||
         SQL%BULK_ROWCOUNT(i) || ' rows.');
   END LOOP;
END;
/

CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
   FROM employees WHERE 1 = 0;
DECLARE
  TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
  deptnums dept_tab;
BEGIN
  SELECT department_id BULK COLLECT INTO deptnums FROM departments;
  FORALL i IN 1..deptnums.COUNT
     INSERT INTO emp_by_dept
        SELECT employee_id, department_id FROM employees
           WHERE department_id = deptnums(i);
  FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
   DBMS_OUTPUT.PUT_LINE('Dept '||deptnums(i)||': inserted '||
                          SQL%BULK_ROWCOUNT(i)||' records');
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT);
END;
/

-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
   TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
   emp_sr empid_tab;
-- create an exception handler for ORA-24381
   errors NUMBER;
   dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
   SELECT employee_id BULK COLLECT INTO emp_sr FROM emp_temp 
         WHERE hire_date < '30-DEC-94';
-- add '_SR' to the job_id of the most senior employees
     FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
       UPDATE emp_temp SET job_id = job_id || '_SR' 
          WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.

EXCEPTION
  WHEN dml_errors THEN -- Now we figure out what failed and why.
   errors := SQL%BULK_EXCEPTIONS.COUNT;
   DBMS_OUTPUT.PUT_LINE('Number of statements that failed: ' || errors);
   FOR i IN 1..errors LOOP
      DBMS_OUTPUT.PUT_LINE('Error #' || i || ' occurred during '|| 
         'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); 
          DBMS_OUTPUT.PUT_LINE('Error message is ' ||
          SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
   END LOOP;
END;
/
DROP TABLE emp_temp;

Retrieving Query Results into Collections with the BULK COLLECT Clause

Using the keywords BULK COLLECT with a query is a very efficient way to retrieve the result set. Instead of looping through each row, you store the results in one or more collections, in a single operation. You can use these keywords in the SELECT INTO and FETCH INTO statements, and the RETURNING INTO clause.

With the BULK COLLECT clause, all the variables in the INTO list must be collections. The table columns can hold scalar or composite values, including object types. Example 11-10 loads two entire database columns into nested tables:

DECLARE
   TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
   TYPE NameTab IS TABLE OF employees.last_name%TYPE;
   enums NumTab;   -- No need to initialize the collections.
   names NameTab;  -- Values will be filled in by the SELECT INTO.
   PROCEDURE print_results IS
   BEGIN
     IF enums.COUNT = 0 THEN 
       DBMS_OUTPUT.PUT_LINE('No results!');
     ELSE
       DBMS_OUTPUT.PUT_LINE('Results:');
       FOR i IN enums.FIRST .. enums.LAST
       LOOP
         DBMS_OUTPUT.PUT_LINE('  Employee #' || enums(i) || ': ' || names(i));
       END LOOP;
     END IF;
   END;
BEGIN
 -- Retrieve data for employees with Ids greater than 1000
   SELECT employee_id, last_name
     BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000;
-- The data has all been brought into memory by BULK COLLECT
-- No need to FETCH each row from the result set
   print_results();
 -- Retrieve approximately 20% of all rows
   SELECT employee_id, last_name
      BULK COLLECT INTO enums, names FROM employees SAMPLE (20);
   print_results();
END;
/

The collections are initialized automatically. Nested tables and associative arrays are extended to hold as many elements as needed. If you use varrays, all the return values must fit in the varray's declared size. Elements are inserted starting at index 1, overwriting any existing elements.

Because the processing of the BULK COLLECT INTO clause is similar to a FETCH loop, it does not raise a NO_DATA_FOUND exception if no rows match the query. You must check whether the resulting nested table or varray is null, or if the resulting associative array has no elements, as shown in Example 11-10.

To prevent the resulting collections from expanding without limit, you can use the LIMIT clause to or pseudocolumn ROWNUM to limit the number of rows processed. You can also use the SAMPLE clause to retrieve a random sample of rows.

DECLARE
   TYPE SalList IS TABLE OF employees.salary%TYPE;
   sals SalList;
BEGIN
-- Limit the number of rows to 50
   SELECT salary BULK COLLECT INTO sals FROM employees
      WHERE ROWNUM <= 50;
-- Retrieve 10% (approximately) of the rows in the table
   SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);
END;
/

You can process very large result sets by fetching a specified number of rows at a time from a cursor, as shown in the following sections.

DECLARE
  TYPE NameList IS TABLE OF employees.last_name%TYPE;
  TYPE SalList IS TABLE OF employees.salary%TYPE;
  CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
  names NameList;
  sals  SalList;
  TYPE RecList IS TABLE OF c1%ROWTYPE;
  recs RecList;
  v_limit PLS_INTEGER := 10;
  PROCEDURE print_results IS
  BEGIN
    IF names IS NULL OR names.COUNT = 0 THEN  -- check if collections are empty
       DBMS_OUTPUT.PUT_LINE('No results!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Results: ');
      FOR i IN names.FIRST .. names.LAST
      LOOP
        DBMS_OUTPUT.PUT_LINE('  Employee ' || names(i) || ': $' || sals(i));
      END LOOP;
    END IF;
  END;
BEGIN
  DBMS_OUTPUT.PUT_LINE('--- Processing all results at once ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO names, sals;
  CLOSE c1;
  print_results();
  DBMS_OUTPUT.PUT_LINE('--- Processing ' || v_limit || ' rows at a time ---');
  OPEN c1;
  LOOP
    FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
    EXIT WHEN names.COUNT = 0;
    print_results();
  END LOOP;
  CLOSE c1;
  DBMS_OUTPUT.PUT_LINE('--- Fetching records rather than columns ---');
  OPEN c1;
  FETCH c1 BULK COLLECT INTO recs;
  FOR i IN recs.FIRST .. recs.LAST
  LOOP
-- Now all the columns from the result set come from a single record
    DBMS_OUTPUT.PUT_LINE('  Employee ' || recs(i).last_name || ': $'
         || recs(i).salary);
  END LOOP;
END;
/

DECLARE
  TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
  dept_recs DeptRecTab;
  CURSOR c1 IS
    SELECT department_id, department_name, manager_id, location_id 
      FROM departments WHERE department_id > 70;
BEGIN
  OPEN c1;
  FETCH c1 BULK COLLECT INTO dept_recs;
END;
/

DECLARE
   TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80;
   empids    numtab;
   rows      PLS_INTEGER := 10;
BEGIN
  OPEN c1;
  LOOP -- the following statement fetches 10 rows or less in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT rows;
    EXIT WHEN empids.COUNT = 0;
--  EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data
    DBMS_OUTPUT.PUT_LINE('------- Results from Each Bulk Fetch --------');
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i));
    END LOOP;
  END LOOP;
  CLOSE c1;
END;
/

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF employees.employee_id%TYPE;
   enums NumList;
   TYPE NameList IS TABLE OF employees.last_name%TYPE;
   names NameList;
BEGIN
   DELETE FROM emp_temp WHERE department_id = 30
      RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
   FOR i IN enums.FIRST .. enums.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
   END LOOP;
END;
/

CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10,20,30);
   TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
   TYPE dept_t IS TABLE OF employees.department_id%TYPE;
   e_ids enum_t;
   d_ids dept_t;
BEGIN
  FORALL j IN depts.FIRST..depts.LAST
    DELETE FROM emp_temp WHERE department_id = depts(j)
       RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids;
  DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
  FOR i IN e_ids.FIRST .. e_ids.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i));
  END LOOP;
END;
/

Restrictions on NOCOPY

The use of NOCOPY increases the likelihood of parameter aliasing. For more information, see "Understanding Subprogram Parameter Aliasing".

Remember, NOCOPY is a hint, not a directive. In the following cases, the PL/SQL compiler ignores the NOCOPY hint and uses the by-value parameter-passing method; no error is generated:

• The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.

• The actual parameter is constrained, such as by scale or NOT NULL. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types.

• The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.

• The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.

• Passing the actual parameter requires an implicit datatype conversion.

• The subprogram is called through a database link or as an external procedure.

Before You Begin

If you are a first-time user of native PL/SQL compilation, try it first with a test database, before proceeding to a production environment.

Always back up your database before configuring the database for PL/SQL native compilation. If you find that the performance benefit is outweighed by extra compilation time, it might be faster to restore from a backup than to recompile everything in interpreted mode.

Some of the setup steps require DBA authority. You must change the values of some initialization parameters, and create a new directory on the database server, preferably near the data files for the instance. The database server also needs a C compiler; on a cluster, the compiler is needed on each node. Even if you can test out these steps yourself on a development machine, you will generally need to consult with a DBA and enlist their help to use native compilation on a production server.

Determining Whether to Use PL/SQL Native Compilation

PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations. Examples of such operations are data warehouse applications, and applications with extensive server-side transformations of data for display. In such cases, expect speed increases of up to 30%.

Because this technique cannot do much to speed up SQL statements called from PL/SQL, it is most effective for compute-intensive PL/SQL procedures that do not spend most of their time executing SQL. You can test to see how much performance gain you can get by enabling PL/SQL native compilation.

It takes longer to compile program units with native compilation than to use the default interpreted mode. You might turn off native compilation during the busiest parts of the development cycle, where code is being frequently recompiled.

If you have determined that there will be significant performance gains in database operations using PL/SQL native compilation, Oracle Corporation recommends that you compile the entire database using the NATIVE setting. Compiling all the PL/SQL code in the database means you see the speedup in your own code, and in calls to all the built-in PL/SQL packages.

If interpreted compilation is required for your environment, you can compile all the PL/SQL units to INTERPRETED. For example, if you import an entire database that includes NATIVE PL/SQL units into an environment where a C compiler is unavailable.

To convert an entire database to NATIVE or INTERPRETED compilation, see "Modifying the Entire Database for PL/SQL Native or Interpreted Compilation".

How PL/SQL Native Compilation Works

If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (m-code). The m-code is stored in the database dictionary and interpreted at run time. With PL/SQL native compilation, the PL/SQL statements are turned into C code that bypasses all the runtime interpretation, giving faster runtime performance.

PLSQL_ initialization parameters set up the environment for PL/SQL native compilation, as described in "Setting up Initialization Parameters for PL/SQL Native Compilation".

PL/SQL uses the command file $ORACLE_HOME/plsql/spnc_commands, and the supported operating system C compiler and linker, to compile and link the resulting C code into shared libraries. See "The spnc_commands File".

The shared libraries are stored inside the data dictionary, so that they can be backed up automatically and are protected from being deleted. These shared library files are copied to the file system and are loaded and run when the PL/SQL subprogram is invoked. If the files are deleted from the file system while the database is shut down, or if you change the directory that holds the libraries, they are extracted again automatically.

Although PL/SQL program units that just call SQL statements might see little or no speedup, natively compiled PL/SQL is always at least as fast as the corresponding interpreted code. The compiled code makes the same library calls as the interpreted code would, so its behavior is exactly the same.

The spnc_commands File

The spnc_commands file, in the $ORACLE_HOME/plsql directory, contains the templates for the commands to compile and link each program. Some special names such as %(src) are predefined, and are replaced by the corresponding filename. The variable $(ORACLE_HOME) is replaced by the location of the Oracle home directory. Comment lines start with a # character. The file contains comments that explain all the special notation.

The spnc_commands file contains a predefined path for the default C compiler, depending on the particular operating system. A specific compiler is supported on each operating system. Only one compiler can be used to compile the PL/SQL modules; do not compile PL/SQL modules in a database with different compilers.

You can view spnc_commands file to confirm that the command templates are correct. You should not need to change this file unless the system administrator has installed the C compiler in another location or you want to use a different supported C compiler. Additional information can be found in the Oracle database installation guide for your platform and by searching for spnc_commands on Oracle Metalink at http://metalink.oracle.com.

Setting up Initialization Parameters for PL/SQL Native Compilation

This section describes the initialization parameters that are used to set PL/SQL native compilation.

• PLSQL_NATIVE_LIBRARY_DIR

• PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

• PLSQL_CODE_TYPE

To check the settings of these parameters, enter the following in SQL*Plus:

SHOW PARAMETERS PLSQL

See also "Initialization Parameters for PL/SQL Compilation".

Setting Up PL/SQL Native Library Subdirectories

By default, PL/SQL program units are kept in one directory. However, if the number of program units is very large, then the operating system might have difficulty handling a large of files in one directory. To avoid this problem, Oracle Corporation recommends that you spread the PL/SQL program units in subdirectories under the directory specified by the PLSQL_NATIVE_LIBRARY_DIR initialization parameter.

If you have an existing database that you will migrate to the new installation, or if you have set up a test database, use the following SQL query to determine how many PL/SQL program units you are using:

SELECT COUNT (*) from DBA_PLSQL_OBJECT_SETTINGS

If you are going to exclude any units, such as packages, use the previous query with:

    WHERE TYPE NOT IN ('TYPE', 'PACKAGE')

If you need to set up PL/SQL native library subdirectories, first create subdirectories sequentially in the form of d0, d1, d2, d3...dx, where x is the total number of directories. Oracle Corporation recommends that you use a script for this task. For example, you might run a PL/SQL block like the following, save its output to a file, then run that file as a shell script:

Next, set the PLSQL_NATIVE_LIBARY_SUBDIR_COUNT initialization parameter to the number of subdirectories you have created. For example, if you created 1000 subdirectories, you can use SQL*Plus to enter the following SQL statement:

ALTER SYSTEM SET PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT=1000;

See "PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT Initialization Parameter".

Setting Up and Testing PL/SQL Native Compilation

To set up and test one or more subprograms through native compilation:

1. Set up the necessary initialization parameter. See "Setting up Initialization Parameters for PL/SQL Native Compilation".

2. Compile one or more subprograms, using one of these methods:

   • Use CREATE OR REPLACE to create or recompile the subprogram.

   • Use the ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE command with the COMPILE option to recompile a specific subprogram or entire package, as shown in Example 11-18.

   • Drop the subprogram and create it again.

   • Run one of the SQL*Plus scripts that creates a set of Oracle-supplied packages.

   • Create a database using a preconfigured initialization file with PLSQL_CODE_TYPE=NATIVE. During database creation, the utlirp script is run to compile all the Oracle-supplied packages.

   Example 11-18 illustrates the process of altering and testing a procedure using native compilation. The procedure is immediately available to call, and runs as a shared library directly within the Oracle process. If any errors occur during compilation, you can see them using the USER_ERRORS view or the SHOW ERRORS command in SQL*Plus.

   Example 11-18 Compiling a PL/SQL Procedure for Native Execution

   -- PLSQL_NATIVE_LIBRARY_DIR must be set to an existing, accessible directory
   SET SERVEROUTPUT ON FORMAT WRAPPED
   
   CREATE OR REPLACE PROCEDURE hello_native AS
   BEGIN
     DBMS_OUTPUT.PUT_LINE('Hello world. Today is ' || TO_CHAR(SYSDATE) || '.');
   END hello_native;
   /
   
   ALTER PROCEDURE hello_native COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
   SHOW ERRORS
   -- check for a file HELLO_NATIVE... in the PLSQL_NATIVE_LIBRARY_DIR directory
   CALL hello_native();
   
   

3. To be sure that the process worked, you can query the data dictionary to see that a procedure is compiled for native execution. To check whether an existing procedure is compiled for native execution or not, you can query the ALL_PLSQL_OBJECT_SETTINGS view. The PLSQL_CODE_TYPE column has a value of NATIVE for procedures that are compiled for native execution, and INTERPRETED otherwise. For information, see Oracle Database Reference.

   For example, to check the status of the procedure hello_native, use the query in Example 11-19.

SELECT PLSQL_CODE_TYPE FROM USER_PLSQL_OBJECT_SETTINGS
   WHERE NAME = 'HELLO_NATIVE';

Setting Up a New Database for PL/SQL Native Compilation

Use the procedures in this section to set up an new database for PL/SQL native compilation. The performance benefits apply to all the built-in PL/SQL packages, which are used for many database operations. If using a Real Applications Cluster environment, see "Real Application Clusters and PL/SQL Native Compilation".

1. Check that spnc_commands file has the correct command templates. Contact your system administrator to ensure that you have the required C compiler on your operating system. See "The spnc_commands File".

2. Ensure that the PL/SQL native library directory is created for each Oracle database. See "PLSQL_NATIVE_LIBRARY_DIR Initialization Parameter".

   • You must set up PL/SQL libraries for each Oracle database. Shared libraries (.so and .dll files) are logically connected to the database. They cannot be shared between databases. If you set up PL/SQL libraries to be shared, the databases will be corrupted.

   • Create a directory in a secure place, in accordance with OFA rules, to prevent .so and .dll files from unauthorized access.

   • Ensure that the compiler executables used for PL/SQL native compilation are writable only by a properly secured user.

   • Ensure that the original copies of the shared libraries are stored inside the database, so they are backed up automatically with the database.

3. Set up the necessary initialization parameters. See "Setting up Initialization Parameters for PL/SQL Native Compilation".

   If you use Database Configuration Assistant, use it to set the initialization parameters required for PL/SQL native compilation. Make sure that you set PLSQL_NATIVE_LIBRARY_DIR to an accessible directory and PLSQL_CODE_TYPE to NATIVE.

Modifying the Entire Database for PL/SQL Native or Interpreted Compilation

You can recompile all PL/SQL modules in an existing database to NATIVE or INTERPRETED, using the dbmsupgnv.sql and dbmsupgin.sql scripts respectively during the process described in this section. Before making the conversion, review "Determining Whether to Use PL/SQL Native Compilation".

During the conversion to native compilation, TYPE specifications are not recompiled by dbmsupgnv.sql to NATIVE because these specifications do not contain executable code.

Package specifications seldom contain executable code so the runtime benefits of compiling to NATIVE are not measurable. You can use the TRUE command line parameter with the dbmsupgnv.sql script to exclude package specs from recompilation to NATIVE, saving time in the conversion process.

When converting to interpreted compilation, the dbmsupgin.sql script does not accept any parameters and does not exclude any PL/SQL units.

1. Ensure that following are properly configured to support native compilation:

   • A supported C compiler is installed in the database environment and that the spnc_commands file has the correct command templates for this compiler.

   • The PLSQL_NATIVE_LIBRARY_DIR is set. See "PLSQL_NATIVE_LIBRARY_DIR Initialization Parameter".

   • The PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT is set correctly for the number of natively compiled units after conversion. See "PLSQL_NATIVE_LIBRARY_DIR Initialization Parameter" and "Setting Up PL/SQL Native Library Subdirectories".

   • A test PL/SQL unit can be compiled as in Example 11-18. For example:

     
     ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE
    REUSE SETTINGS;
     

2. Shut down application services, the listener, and the database.

   • Shut down all of the Application services including the Forms Processes, Web Servers, Reports Servers, and Concurrent Manager Servers. After shutting down all of the Application services, ensure that all of the connections to the database have been terminated.

   • Shut down the TNS listener of the database to ensure that no new connections are made.

   • Shut down the database in normal or immediate mode as the user SYS. See "Starting Up and Shutting Down" in the Oracle Database Administrator's Guide.

3. Set PLSQL_CODE_TYPE to NATIVE in the initialization parameter file. If the database is using a server parameter file, then set this after the database has started. See "PLSQL_CODE_TYPE Initialization Parameter".

   The value of PLSQL_CODE_TYPE does not affect the conversion of the PL/SQL units in these steps. However, it does affect all subsequently compiled units and it should be explicitly set to the compilation type that you want.

4. Start up the database in upgrade mode, using the UPGRADE option. For information on SQL*Plus STARTUP, see the SQL*Plus User's Guide and Reference.

5. Execute the following code to list the invalid PL/SQL units. You can save the output of the query for future reference with the SQL SPOOL command.

   Example 11-20 Checking for Invalid PL/SQL Units

   REM To save the output of the query to a file: SPOOL pre_update_invalid.log
   SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE 
     FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s 
     WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID';
   REM To stop spooling the output: SPOOL OFF
   
   

   If any Oracle supplied units are invalid, try to validate them. For example:

   ALTER PACKAGE OLAPSYS.DBMS_AWM COMPILE BODY REUSE SETTINGS;

   See ALTER FUNCTION, ALTER PACKAGE, and ALTER PROCEDURE, in the Oracle Database SQL Reference.If the units cannot be validated, save the spooled log for future resolution and continue.

6. Execute the following query to determine how many objects are compiled NATIVE and INTERPRETED. Use the SQL SPOOL command if you want to save the output.

   Example 11-21 Checking for PLSQL Compilation Type

   SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*) FROM DBA_PLSQL_OBJECT_SETTINGS
     WHERE PLSQL_CODE_TYPE IS NOT NULL
     GROUP BY TYPE, PLSQL_CODE_TYPE
     ORDER BY TYPE, PLSQL_CODE_TYPE;
   
   

   Any objects with a NULL plsql_code_type are special internal objects and can be ignored.

7. Run the $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as the user SYS to update the plsql_code_type setting to NATIVE in the dictionary tables for all PL/SQL units. This process also invalidates the units. Use TRUE with the script to exclude package specifications; FALSE to include the package specifications.

   This update must be done when the database is in UPGRADE mode. The script is guaranteed to complete successfully or rollback all the changes.

8. Shut down the database and restart in NORMAL mode.

9. Before you run the utlrp.sql script, Oracle recommends that no other sessions are connected to avoid possible problems. You can ensure this with:

   ALTER SYSTEM ENABLE RESTRICTED SESSION;

10. Run the $ORACLE_HOME/rdbms/admin/utlrp.sql script as the user SYS. This script recompiles all the PL/SQL modules using a default degree of parellelism. See the comments in the script for information on setting the degree explicitly.

    If for any reason the script is abnormally terminated, rerun the utlrp.sql script to recompile any remaining invalid PL/SQL modules.

11. After the compilation completes successfully, verify that there are no new invalid PL/SQL units using the query in Example 11-20. You can spool the output of the query to the post_upgrade_invalid.log file and compare the contents with the pre_upgrade_invalid.log file, if it was created previously.

12. Re-execute the query in Example 11-21. If recompiling with dbmsupgnv.sql, confirm that all PL/SQL units, except TYPE specifications and package specifications if excluded, are NATIVE. If recompiling with dbmsupgin.sql, confirm that all PL/SQL units are INTERPRETED.

13. Disable the restricted session mode for the database, then start the services that you previously shut down. To disable restricted session mode:

    ALTER SYSTEM DISABLE RESTRICTED SESSION;

Overview of Pipelined Table Functions

Pipelined table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function in place of the name of a database table in the FROM clause of a query or in place of a column name in the SELECT list of a query.

A table function can take a collection of rows as input. An input collection parameter can be either a collection type (such as a VARRAY or a PL/SQL table) or a REF CURSOR.

Execution of a table function can be parallelized, and returned rows can be streamed directly to the next process without intermediate staging. Rows from a collection returned by a table function can also be pipelined, that is, iteratively returned as they are produced instead of in a batch after all processing of the table function's input is completed.

Streaming, pipelining, and parallel execution of table functions can improve performance:

• By enabling multi-threaded, concurrent execution of table functions

• By eliminating intermediate staging between processes

• By improving query response time: With non-pipelined table functions, the entire collection returned by a table function must be constructed and returned to the server before the query can return a single result row. Pipelining enables rows to be returned iteratively, as they are produced. This also reduces the memory that a table function requires, as the object cache does not need to materialize the entire collection.

• By iteratively providing result rows from the collection returned by a table function as the rows are produced instead of waiting until the entire collection is staged in tables or memory and then returning the entire collection.

Writing a Pipelined Table Function

You declare a pipelined table function by specifying the PIPELINED keyword. Pipelined functions can be defined at the schema level with CREATE FUNCTION or in a package. The PIPELINED keyword indicates that the function returns rows iteratively. The return type of the pipelined table function must be a supported collection type, such as a nested table or a varray. This collection type can be declared at the schema level or inside a package. Inside the function, you return individual elements of the collection type. The elements of the collection type must be supported SQL datatypes, such as NUMBER and VARCHAR2. PL/SQL datatypes, such as PLS_INTEGER and BOOLEAN, are not supported as collection elements in a pipelined function.

Example 11-22 shows how to assign the result of a pipelined table function to a PL/SQL collection variable and use the function in a SELECT statement.

CREATE PACKAGE pkg1 AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));

Using Pipelined Table Functions for Transformations

A pipelined table function can accept any argument that regular functions accept. A table function that accepts a REF CURSOR as an argument can serve as a transformation function. That is, it can use the REF CURSOR to fetch the input rows, perform some transformation on them, and then pipeline the results out.

In Example 11-23, the f_trans function converts a row of the employees table into two rows.

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg IS
  TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE outrec_typ IS RECORD ( 
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30));
  TYPE outrecset IS TABLE OF outrec_typ;
 FUNCTION f_trans(p refcur_t) 
      RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
  FUNCTION f_trans(p refcur_t) 
   RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    in_rec  p%ROWTYPE;
  BEGIN
  LOOP
    FETCH p INTO in_rec;
    EXIT WHEN p%NOTFOUND;
    -- first row
    out_rec.var_num := in_rec.employee_id;
    out_rec.var_char1 := in_rec.first_name;
    out_rec.var_char2 := in_rec.last_name;
    PIPE ROW(out_rec);
    -- second row
    out_rec.var_char1 := in_rec.email;
    out_rec.var_char2 := in_rec.phone_number;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE p;
  RETURN;
  END;
END refcur_pkg;
/
-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
   refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));

In the preceding query, the pipelined table function f_trans fetches rows from the CURSOR subquery SELECT * FROM employees ..., performs the transformation, and pipelines the results back to the user as a table. The function produces two output rows (collection elements) for each input row.

Note that when a CURSOR subquery is passed from SQL to a REF CURSOR function argument as in Example 11-23, the referenced cursor is already open when the function begins executing.

Returning Results from Pipelined Table Functions

In PL/SQL, the PIPE ROW statement causes a pipelined table function to pipe a row and continue processing. The statement enables a PL/SQL table function to return rows as soon as they are produced. For performance, the PL/SQL runtime system provides the rows to the consumer in batches.

In Example 11-23, the PIPE ROW(out_rec) statement pipelines data out of the PL/SQL table function. out_rec is a record, and its type matches the type of an element of the output collection.

The PIPE ROW statement may be used only in the body of pipelined table functions; an error is raised if it is used anywhere else. The PIPE ROW statement can be omitted for a pipelined table function that returns no rows.

A pipelined table function may have a RETURN statement that does not return a value. The RETURN statement transfers the control back to the consumer and ensures that the next fetch gets a NO_DATA_FOUND exception.

Because table functions pass control back and forth to a calling routine as rows are produced, there is a restriction on combining table functions and PRAGMA AUTONOMOUS_TRANSACTION. If a table function is part of an autonomous transaction, it must COMMIT or ROLLBACK before each PIPE ROW statement, to avoid an error in the calling subprogram.

Oracle has three special SQL datatypes that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three special types to create anonymous (that is, unnamed) types, including anonymous collection types. The types are SYS.ANYTYPE, SYS.ANYDATA, and SYS.ANYDATASET. The SYS.ANYDATA type can be useful in some situations as a return value from table functions.

Pipelining Data Between PL/SQL Table Functions

With serial execution, results are pipelined from one PL/SQL table function to another using an approach similar to co-routine execution. For example, the following statement pipelines results from function g to function f:

SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));

Parallel execution works similarly except that each function executes in a different process (or set of processes).

Optimizing Multiple Calls to Pipelined Table Functions

Multiple invocations of a pipelined table function, either within the same query or in separate queries result in multiple executions of the underlying implementation. By default, there is no buffering or reuse of rows. For example:

If the function always produces the same result value for each combination of values passed in, you can declare the function DETERMINISTIC, and Oracle automatically buffers rows for it. If the function is not really deterministic, results are unpredictable.

Fetching from the Results of Pipelined Table Functions

PL/SQL cursors and ref cursors can be defined for queries over table functions. For example:

OPEN c FOR SELECT * FROM TABLE(f(...));

Cursors over table functions have the same fetch semantics as ordinary cursors. REF CURSOR assignments based on table functions do not have any special semantics.

However, the SQL optimizer will not optimize across PL/SQL statements. For example:

does not execute as well as:

This is so even ignoring the overhead associated with executing two SQL statements and assuming that the results can be pipelined between the two statements.

Passing Data with Cursor Variables

You can pass a set of rows to a PL/SQL function in a REF CURSOR parameter. For example, this function is declared to accept an argument of the predefined weakly typed REF CURSOR type SYS_REFCURSOR:

FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;

Results of a subquery can be passed to a function directly:

SELECT * FROM TABLE(f(CURSOR(SELECT empid FROM tab)));

In the preceding example, the CURSOR keyword is required to indicate that the results of a subquery should be passed as a REF CURSOR parameter.

A predefined weak REF CURSOR type SYS_REFCURSOR is also supported. With SYS_REFCURSOR, you do not need to first create a REF CURSOR type in a package before you can use it.

To use a strong REF CURSOR type, you still must create a PL/SQL package and declare a strong REF CURSOR type in it. Also, if you are using a strong REF CURSOR type as an argument to a table function, then the actual type of the REF CURSOR argument must match the column type, or an error is generated. Weak REF CURSOR arguments to table functions can only be partitioned using the PARTITION BY ANY clause. You cannot use range or hash partitioning for weak REF CURSOR arguments.

PL/SQL functions can accept multiple REF CURSOR input variables as shown in Example 11-24.

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg IS
  TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
  TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;  
  TYPE outrec_typ IS RECORD ( 
    var_num    NUMBER(6),
    var_char1  VARCHAR2(30),
    var_char2  VARCHAR2(30));
  TYPE outrecset IS TABLE OF outrec_typ;
  FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) 
    RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) 
    RETURN outrecset PIPELINED IS
    out_rec outrec_typ;
    in_rec1 p1%ROWTYPE;
    in_rec2 p2%ROWTYPE;
BEGIN
  LOOP
    FETCH p2 INTO in_rec2;
    EXIT WHEN p2%NOTFOUND;
  END LOOP;
  CLOSE p2;
  LOOP
    FETCH p1 INTO in_rec1;
    EXIT WHEN p1%NOTFOUND;
    -- first row
    out_rec.var_num := in_rec1.employee_id;
    out_rec.var_char1 := in_rec1.first_name;
    out_rec.var_char2 := in_rec1.last_name;
    PIPE ROW(out_rec);
    -- second row
    out_rec.var_num := in_rec2.department_id;
    out_rec.var_char1 := in_rec2.department_name;
    out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE p1;
  RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
  CURSOR(SELECT * FROM employees WHERE department_id = 60),
  CURSOR(SELECT * FROM departments WHERE department_id = 60)));

You can explicitly open a REF CURSOR for a query and pass it as a parameter to a table function:

In this case, the table function closes the cursor when it completes, so your program should not explicitly try to close the cursor.

A table function can compute aggregate results using the input ref cursor. Example 11-25 computes a weighted average by iterating over a set of input rows.

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
                          weight NUMBER, grade NUMBER);
INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

CREATE PACKAGE pkg_gpa IS
  TYPE gpa IS TABLE OF NUMBER;
  FUNCTION weighted_average(input_values SYS_REFCURSOR)
    RETURN gpa PIPELINED;
END pkg_gpa;
/
CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average(input_values SYS_REFCURSOR)
  RETURN gpa PIPELINED IS
  grade NUMBER;
  total NUMBER := 0;
  total_weight NUMBER := 0;
  weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows
  LOOP
     FETCH input_values INTO weight, grade;
     EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
     total_weight := total_weight + weight;
     total := total + grade*weight;
  END LOOP;
  PIPE ROW (total / total_weight);
  RETURN; -- the function returns a single result
END;
END pkg_gpa;
/
-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
       pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;

Performing DML Operations Inside Pipelined Table Functions

To execute DML statements, declare a pipelined table function with the AUTONOMOUS_TRANSACTION pragma, which causes the function to execute in a new transaction not shared by other processes:

During parallel execution, each instance of the table function creates an independent transaction.

Performing DML Operations on Pipelined Table Functions

Pipelined table functions cannot be the target table in UPDATE, INSERT, or DELETE statements. For example, the following statements will raise an error:

However, you can create a view over a table function and use INSTEAD OF triggers to update it. For example:

The following INSTEAD OF trigger is fired when the user inserts a row into the BookTable view:

INSTEAD OF triggers can be defined for all DML operations on a view built on a table function.

Handling Exceptions in Pipelined Table Functions

Exception handling in pipelined table functions works just as it does with regular functions.

Some languages, such as C and Java, provide a mechanism for user-supplied exception handling. If an exception raised within a table function is handled, the table function executes the exception handler and continues processing. Exiting the exception handler takes control to the enclosing scope. If the exception is cleared, execution proceeds normally.

An unhandled exception in a table function causes the parent transaction to roll back.