11 Tuning PL/SQL Applications for Performance

Avoiding CPU Overhead in PL/SQL Code

PL/SQL programs look relatively simple because most of the work is done by SQL statements. Slow SQL statements are the main reason for slow execution.

If SQL statements are slowing down your program:

• Make sure you have appropriate indexes. There are different kinds of indexes for different situations. Your index strategy might be different depending on the sizes of various tables in a query, the distribution of data in each query, and the columns used in the WHERE clauses.

• Make sure you have up-to-date statistics on all the tables, using the subprograms in the DBMS_STATS package.

• Analyze the execution plans and performance of the SQL statements, using:

  • EXPLAIN PLAN statement

  • SQL Trace facility with TKPROF utility

  • Oracle Trace facility

• Rewrite the SQL statements if necessary. For example, query hints can avoid problems such as unnecessary full-table scans.

For more information about these methods, see Oracle Database Performance Tuning Guide.

Some PL/SQL features also help improve the performance of SQL statements:

• If you are running SQL statements inside a PL/SQL loop, look at the FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.

• If you are looping through the result set of a query, look at the BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.

Badly written subprograms (for example, a slow sort or search function) can harm performance. Avoid unnecessary calls to subprograms, and optimize their code:

• If a function is called within a SQL query, you can cache the function value for each row by creating a function-based index on the table in the query. The CREATE INDEX statement might take a while, but queries can be much faster.

• If a column is passed to a function within an SQL query, the query cannot use regular indexes on that column, and the function might be called for every row in a (potentially very large) table. Consider nesting the query so that the inner query filters the results to a small number of rows, and the outer query calls the function only a few times:

  BEGIN
  -- Inefficient, calls my_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;
  /
  

If you use OUT or IN OUT parameters, PL/SQL adds some performance overhead to ensure correct behavior in case of exceptions (assigning a value to the OUT parameter, then exiting the subprogram because of an unhandled exception, so that the OUT parameter keeps its original value).

If your program does not depend on OUT parameters keeping their values in such situations, you can add the NOCOPY keyword to the parameter declarations, so the parameters are declared OUT NOCOPY or IN OUT NOCOPY.

This technique can give significant speedup if you are passing back large amounts of data in OUT parameters, such as collections, big VARCHAR2 values, or LOBs.

This technique also applies to member subprograms of object types. If these subprograms modify attributes of the object type, all the attributes are copied when the subprogram ends. To avoid this overhead, you can explicitly declare the first parameter of the member subprogram as SELF IN OUT NOCOPY, instead of relying on PL/SQL's implicit declaration SELF IN OUT.

Because PL/SQL applications are often built around loops, it is important to optimize the loop itself and the code inside the loop:

• Move initializations or computations outside the loop if possible.

• To issue a series of DML statements, replace loop constructs with FORALL statements.

• To loop through a result set and store the values, use the BULK COLLECT clause on the query to bring the query results into memory in one operation.

• If you have to loop through a result set more than once, or issue other queries as you loop through a result set, you can probably enhance the original query to give you exactly the results you want. Some query operators to explore include UNION, INTERSECT, MINUS, and CONNECT BY.

• You can also nest one query inside another (known as a subselect) to do the filtering and sorting in multiple stages. For example, instead of calling a PL/SQL function in the inner WHERE clause (which might call the function once for each row of the table), you can filter the result set to a small set of rows in the inner query, and call the function in the outer query.

PL/SQL provides many highly optimized string functions such as REPLACE, TRANSLATE, SUBSTR, INSTR, RPAD, and LTRIM. The built-in functions use low-level code that is more efficient than regular PL/SQL.

If you use PL/SQL string functions to search for regular expressions, consider using the built-in regular expression functions, such as REGEXP_SUBSTR.

PL/SQL stops evaluating a logical expression as soon as the result can be determined (known as short-circuit evaluation).

When evaluating multiple conditions separated by AND or OR, put the least expensive ones first. For example, check the values of PL/SQL variables before testing function return values, because PL/SQL might be able to skip calling the functions.

At run time, PL/SQL converts between different datatypes automatically. For example, assigning a PLS_INTEGER variable to a NUMBER variable results in a conversion because their internal representations are different.

Avoiding implicit conversions can improve performance. Use literals of the appropriate types: character literals in character expressions, decimal numbers in number expressions, and so on.

In the example below, the integer literal 15 must be converted to an Oracle NUMBER before the addition. The floating-point literal 15.0 is represented as a NUMBER, avoiding the need for a conversion.

DECLARE
   n NUMBER;
   c CHAR(5);
BEGIN
   n := n + 15;      -- converted implicitly; slow
   n := n + 15.0;    -- not converted; fast
   c := 25;          -- converted implicitly; slow
   c := TO_CHAR(25); -- converted explicitly; still slow
   c := '25';        -- not converted; fast
END;
/

Minimizing conversions might mean changing the types of your variables, or even working backward and designing your tables with different datatypes. Or, you might convert data once (such as from an INTEGER column to a PLS_INTEGER variable) and use the PL/SQL type consistently after that.

When you need to declare a local integer variable, use the datatype PLS_INTEGER, which is the most efficient integer type. PLS_INTEGER values require less storage than INTEGER or NUMBER values, and PLS_INTEGER operations use machine arithmetic.

The BINARY_INTEGER datatype is just as efficient as PLS_INTEGER for any new code, but if you are running the same code on Oracle9i or Oracle8i databases, PLS_INTEGER is faster.

The datatype NUMBER and its subtypes are represented in a special internal format, designed for portability and arbitrary scale and precision, not performance. Even the subtype INTEGER is treated as a floating-point number with nothing after the decimal point. Operations on NUMBER or INTEGER variables require calls to library routines.

Avoid constrained subtypes such as INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, and SIGNTYPE in performance-critical code. Variables of these types require extra checking at run time, each time they are used in a calculation.

The datatype NUMBER and its subtypes are represented in a special internal format, designed for portability and arbitrary scale and precision, not performance. Operations on NUMBER or INTEGER variables require calls to library routines.

The BINARY_FLOAT and BINARY_DOUBLE types can use native machine arithmetic instructions, and are more efficient for number-crunching applications such as scientific processing. They also require less space in the database.

These types do not always represent fractional values precisely, and handle rounding differently than the NUMBER types. These types are less suitable for financial code where accuracy is critical.

Avoiding Memory Overhead in PL/SQL Code

You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can actually conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying a size such as 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of 2000 or more characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool. Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package is aged out of memory, it must be reloaded if you reference it again.

You can improve performance by sizing the shared memory pool correctly. Make sure it is large enough to hold all frequently used packages but not so large that memory is wasted.

You can "pin" frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it is not aged out by the least recently used (LRU) algorithm that Oracle normally uses. The package remains in memory no matter how full the pool gets or how frequently you access the package.

For more information on the DBMS_SHARED_POOL package, see PL/SQL Packages and Types Reference.

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 PL/SQL Packages and Types Reference.

The next step is to 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 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 PL/SQL Packages and Types Reference.

Tracing large applications can produce huge amounts of data that are difficult to manage. Before starting Trace, you can optionally limit the volume of data collected by selecting specific subprograms for trace data collection.

In addition, you can choose a tracing level. For example, you can choose to trace all subprograms and exceptions, or you can choose to trace selected subprograms and exceptions.

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.

CREATE TABLE employees2 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 employees2 WHERE department_id = depts(i);
   COMMIT;
END;
/
DROP TABLE employees2;

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;
/
DROP TABLE parts1;
DROP TABLE parts2;

CREATE TABLE employees2 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 employees2 WHERE department_id = depts(j);
   COMMIT;
END;
/
DROP TABLE employees2;

-- 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('Company 1','Company 2','Company 3','Company 4', 'Company 5');
      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;

-- Since the subscripts of our collections are not consecutive, we 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 let's process the order data differently. We'll 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.
         rejected_order_tab(rejected_order_tab.LAST) := i; -- And record the subscript from the original collection.
      END IF;
      IF amount_tab(i) > 2000 THEN
         big_order_tab.EXTEND; -- Add a new element to this collection.
         big_order_tab(big_order_tab.LAST) := i; -- And record the subscript from the original collection.
      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;

DROP TABLE valid_orders;
DROP TABLE big_orders;
DROP TABLE rejected_orders;

In a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as follows. Then, you change the job titles so that they are longer. The second UPDATE fails because the new value is too long for the column. Because we handle the exception, the first UPDATE is not rolled back and we can commit that change.

CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(18));
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30);
BEGIN
   INSERT INTO emp2 VALUES(10, 'Clerk');
   INSERT INTO emp2 VALUES(20, 'Bookkeeper');  -- Lengthening this job title causes an exception.
   INSERT INTO emp2 VALUES(30, 'Analyst');
   COMMIT;

   FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements.
      UPDATE emp2 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;
/
DROP TABLE emp2;

CREATE TABLE emp2 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 emp2 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;
/
DROP TABLE emp2;

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;
/
DROP TABLE emp_by_dept;

CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
   TYPE NumList IS TABLE OF NUMBER;
-- The zeros in this list will cause divide-by-zero errors.
   num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
   errors  NUMBER;
   dml_errors EXCEPTION;
   PRAGMA exception_init(dml_errors, -24381);
BEGIN
-- SAVE EXCEPTIONS means don't stop if some DELETEs fail.
   FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
      DELETE FROM emp2 WHERE salary > 500000/num_tab(i);
-- 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 DELETE 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 emp2;

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. The following example 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
      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;
BEGIN
   SELECT employee_id, last_name -- Retrieve data for 10 arbitrary employees.
      BULK COLLECT INTO enums, names
      FROM employees WHERE ROWNUM < 11;
-- The data has all been brought into memory by BULK COLLECT.
-- No need to FETCH each row from the result set.
   print_results;

   SELECT employee_id, last_name -- Retrieve approximately 20% of all rows
      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.

Since 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.

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

DECLARE
   TYPE SalList IS TABLE OF emp.sal%TYPE;
   sals SalList;
BEGIN
-- Limit the number of rows to 100.
   SELECT sal BULK COLLECT INTO sals FROM emp
      WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
   SELECT sal BULK COLLECT INTO sals FROM emp 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;

   PROCEDURE print_results IS
   BEGIN
      dbms_output.put_line('Results:');
      IF names IS NULL OR names.COUNT = 0 THEN
         RETURN; -- Don't print anything if collections are empty.
      END IF;
      FOR i IN names.FIRST .. names.LAST
      LOOP
         dbms_output.put_line('  Employee ' || names(i) || ': $' ||
            sals(i));
      END LOOP;
   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 7 rows at a time ---');
   OPEN c1;
   LOOP
      FETCH c1 BULK COLLECT INTO names, sals LIMIT 7;
      EXIT WHEN c1%NOTFOUND;
      print_results;
   END LOOP;
-- Loop exits when fewer than 7 rows are fetched. Have to
-- process the last few. Need extra checking inside PRINT_RESULTS
-- in case it is called when the collection is empty.
   print_results;
   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 dept%ROWTYPE;
   dept_recs DeptRecTab;
   CURSOR c1 IS
      SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO dept_recs;
END;
/

DECLARE
   TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   CURSOR c1 IS SELECT empno FROM emp;
   empnos NumTab;
   rows   NATURAL := 10;
BEGIN
   OPEN c1;
   LOOP
      /* The following statement fetches 10 rows (or less). */
      FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
      EXIT WHEN c1%NOTFOUND;
      ...
   END LOOP;
   CLOSE c1;
END;
/

CREATE TABLE emp2 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 emp2 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;
/
DROP TABLE emp2;

CREATE TABLE emp2 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 emp2 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;
/
DROP TABLE emp2;

DECLARE
   ...
BEGIN
   -- assume that values were assigned to the host array
   -- and host variables in the host environment
   FORALL i IN :lower..:upper
      DELETE FROM employees WHERE department_id = :depts(i);
   COMMIT;
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.

Overview of Table Functions

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 like the name of a database table, in the FROM clause of a query, or like 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.

CREATE TYPE Book_t AS OBJECT ( name VARCHAR2(100), author VARCHAR2(30), abstract VARCHAR2(1000));
/
CREATE TYPE BookSet_t AS TABLE OF Book_t;
/
-- The CLOBs are stored in a table Catalogs:
CREATE TABLE Catalogs ( name VARCHAR2(30), cat CLOB );

CREATE FUNCTION GetBooks(a CLOB) RETURN BookSet_t;
/

SELECT c.name, Book.name, Book.author, Book.abstract
  FROM Catalogs c, TABLE(GetBooks(c.cat)) Book;

CREATE TYPE numset_t AS TABLE OF NUMBER;
/

CREATE FUNCTION f1(x number) RETURN numset_t PIPELINED IS
BEGIN
  FOR i IN 1..x LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;
/

-- pipelined function in from clause
select * from table(f1(3));

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.

For example, the following code sketches the declarations that define a StockPivot function. This function converts a row of the type (Ticker, OpenPrice, ClosePrice) into two rows of the form (Ticker, PriceType, Price). Calling StockPivot for the row ("ORCL", 41, 42) generates two rows: ("ORCL", "O", 41) and ("ORCL", "C", 42).

Input data for the table function might come from a source such as table StockTable:

CREATE TABLE StockTable (
  ticker VARCHAR(4),
  open_price NUMBER,
  close_price NUMBER
);

Here are the declarations. See "Returning Results from Table Functions" for the function bodies.

-- Create the types for the table function's output collection
-- and collection elements
CREATE TYPE TickerType AS OBJECT
(
  ticker VARCHAR2(4),
  PriceType VARCHAR2(1),
  price NUMBER
);
/

CREATE TYPE TickerTypeSet AS TABLE OF TickerType;
/

-- Define the ref cursor type

CREATE PACKAGE refcur_pkg IS
  TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
END refcur_pkg;
/

-- Create the table function

CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED ... ;
/

Here is an example of a query that uses the StockPivot table function:

SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)));

In the query above, the pipelined table function StockPivot fetches rows from the CURSOR subquery SELECT * FROM StockTable, 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 the example above, the referenced cursor is already open when the function begins executing.

Writing a Pipelined Table Function

You declare a pipelined table function by specifying the PIPELINED keyword. This keyword indicates that the function returns rows iteratively. The return type of the pipelined table function must be a collection type, such as a nested table or a varray. You can declare this collection at the schema level or inside a package. Inside the function, you return individual elements of the collection type.

For example, here are declarations for two pipelined table functions. (The function bodies are shown in later examples.)

CREATE FUNCTION GetBooks(cat CLOB) RETURN BookSet_t
  PIPELINED IS ...;
/

CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
  PIPELINED IS...;
/

Returning Results from Table Functions

In PL/SQL, the PIPE ROW statement causes a 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.) 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;
BEGIN
  LOOP
    FETCH p INTO in_rec;
    EXIT WHEN p%NOTFOUND;
    -- first row
    out_rec.ticker := in_rec.Ticker;
    out_rec.PriceType := 'O';
    out_rec.price := in_rec.OpenPrice;
    PIPE ROW(out_rec);
    -- second row
    out_rec.PriceType := 'C';
    out_rec.Price := in_rec.ClosePrice;
    PIPE ROW(out_rec);
  END LOOP;
  CLOSE p;
  RETURN;
END;
/

In the example, 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 must 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 areproduced, 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).

Querying Table Functions

Pipelined table functions are used in the FROM clause of SELECT statements. The result rows are retrieved by Oracle iteratively from the table function implementation. For example:

SELECT x.Ticker, x.Price
FROM TABLE(StockPivot(  CURSOR(SELECT * FROM StockTable))) x
WHERE x.PriceType='C';

Optimizing Multiple Calls to Table Functions

Multiple invocations of a 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,

SELECT * FROM TABLE(f(...)) t1, TABLE(f(...)) t2
  WHERE t1.id = t2.id;

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

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 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:

DECLARE
    r SYS_REFCURSOR;
BEGIN
    OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));
    SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
END;
/

does not execute as well as:

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

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 empno FROM tab)));

In the example above, 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.

CREATE FUNCTION g(p1 pkg.refcur_t1, p2 pkg.refcur_t2) RETURN...
  PIPELINED ... ;
/

SELECT * FROM TABLE(g(CURSOR(SELECT employee_id FROM tab),
  CURSOR(SELECT * FROM employees));

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

DECLARE
  r SYS_REFCURSOR;
  rec ...;
BEGIN
  OPEN r FOR SELECT * FROM TABLE(f(...));
  -- Must return a single row result set.
  SELECT * INTO rec FROM TABLE(g(r));
END;
/

DROP TABLE gradereport;
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 OR replace TYPE gpa AS TABLE OF NUMBER;
/

CREATE OR replace 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);
-- The function returns a single result.
  RETURN;
END;
/
show errors;

-- The 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 weighted_result.column_value from
   table( weighted_average( cursor(select weight,grade from gradereport) ) ) weighted_result;

Performing DML Operations Inside Table Functions

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

CREATE FUNCTION f(p SYS_REFCURSOR) return CollType PIPELINED IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN NULL; END;
/

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

Performing DML Operations on Table Functions

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

UPDATE F(CURSOR(SELECT * FROM tab)) SET col = value;
INSERT INTO f(...) VALUES ('any', 'thing');

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

CREATE VIEW BookTable AS
  SELECT x.Name, x.Author
  FROM TABLE(GetBooks('data.txt')) x;

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

CREATE TRIGGER BookTable_insert
INSTEAD OF INSERT ON BookTable
REFERENCING NEW AS n
FOR EACH ROW
BEGIN
  ...
END;
/
INSERT INTO BookTable VALUES (...);

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

Handling Exceptions in Table Functions

Exception handling in 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.