Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 2 (11.2)

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

Go to previous page
Previous
Go to next page
Next
View PDF

12 PL/SQL Optimization and Tuning

This chapter explains how the PL/SQL compiler optimizes your code and how to write efficient PL/SQL code and improve existing PL/SQL code.

Topics:

PL/SQL Optimizer

Prior to Oracle Database 10g Release 1 (10.1), the PL/SQL compiler translated your source code to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance.

The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.

See Also:

Subprogram Inlining

One optimization that the compiler can perform is subprogram inlining. Subprogram inlining replaces a subprogram invocation (to a subprogram in the same program unit) with a copy of the invoked subprogram.

To allow subprogram inlining, either accept the default value of the PLSQL_OPTIMIZE_LEVEL compilation parameter (which is 2) or set it to 3. With PLSQL_OPTIMIZE_LEVEL=2, you must specify each subprogram to be inlined. With PLSQL_OPTIMIZE_LEVEL=3, the PL/SQL compiler seeks opportunities to inline subprograms beyond those that you specify.

If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations.

If subprogram inlining slows the performance of a particular PL/SQL program, use the PL/SQL hierarchical profiler to identify subprograms for which you want to turn off inlining. To turn off inlining for a subprogram, use the INLINE pragma, explained in "INLINE Pragma".

See Also:

In Example 12-1 and Example 12-2, assume that PLSQL_OPTIMIZE_LEVEL=2.

In Example 12-1, the INLINE pragma affects the procedure invocations p1(1) and p1(2), but not the procedure invocations p1(3) and p1(4).

Example 12-1 Specifying that a Subprogram Is To Be Inlined

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are inlined
...
x:= p1(3) + p1(4) + 17;    -- These 2 invocations to p1 are not inlined
...

In Example 12-2 the INLINE pragma affects both functions named p2.

Example 12-2 Specifying that an Overloaded Subprogram Is To Be Inlined

FUNCTION p2 (p boolean) return PLS_INTEGER IS ...
FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS ...
...
PRAGMA INLINE(p2, 'YES');
x := p2(true) + p2(3);
...

In Example 12-3, assume that PLSQL_OPTIMIZE_LEVEL=3. The INLINE pragma affects the procedure invocations p1(1) and p1(2), but not the procedure invocations p1(3) and p1(4).

Example 12-3 Specifying that a Subprogram Is Not To Be Inlined

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'NO');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are inlined
...
x:= p1(3) + p1(4) + 17;    -- These 2 invocations to p1 might be inlined
...

PRAGMA INLINE ... 'NO' overrides PRAGMA INLINE ... 'YES' for the same subprogram, regardless of their order in the code. In Example 12-4, the second INLINE pragma overrides both the first and third INLINE pragmas.

Example 12-4 Applying Two INLINE Pragmas to the Same Subprogram

PROCEDURE p1 (x PLS_INTEGER) IS ...
...
PRAGMA INLINE (p1, 'YES');
PRAGMA INLINE (p1, 'NO');
PRAGMA INLINE (p1, 'YES');
x:= p1(1) + p1(2) + 17;    -- These 2 invocations to p1 are not inlined
...

See Also:

"INLINE Pragma" for more information about subprogram inlining

Candidates for Tuning

The following kinds of PL/SQL code are very likely to benefit from tuning:

Minimizing CPU Overhead in PL/SQL Code

Topics:

Tune SQL Statements

The most common cause of slowness in PL/SQL programs is slow SQL statements. To make the SQL statements in a PL/SQL program as efficient as possible:

Tune Function Invocations in Queries

Functions invoked in queries might run millions of times. Do not invoke a function in a query unnecessarily, and make the invocation as efficient as possible.

Create a function-based index on the table in the query. The CREATE INDEX statement (described in Oracle Database SQL Language Reference) might take a while, but because the function value for each row is cached, the query can run much faster.

See Also:

"PL/SQL Function Result Cache" for information about caching the results of PL/SQL functions

If the query passes a column to a function, then the query cannot use user-created indexes on that column, so the query might invoke the function for every row of the table (which might be very large). To minimize the number of function invocations, use a nested query. Have the inner query filter the result set to a small number of rows, and have the outer query invoke the function for only those rows.

In Example 12-5, the two queries produce the same result set, but the second query is more efficient than the first. (In the example, the times and time difference are very small, because the EMPLOYEES table is very small. For a very large table, they would be significant.)

Example 12-5 Nested Query Improves Performance

DECLARE
  starting_time  TIMESTAMP WITH TIME ZONE;
  ending_time    TIMESTAMP WITH TIME ZONE;
BEGIN
  -- Invokes SQRT for every row of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT DISTINCT(SQRT(department_id)) col_alias
    FROM employees
    ORDER BY col_alias
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
 
  -- Invokes SQRT for every distinct department_id of employees table:
 
  SELECT SYSTIMESTAMP INTO starting_time FROM DUAL;
 
  FOR item IN (
    SELECT SQRT(department_id) col_alias
    FROM (SELECT DISTINCT department_id FROM employees)
    ORDER BY col_alias
  )
  LOOP
    IF item.col_alias IS NOT NULL THEN
      DBMS_OUTPUT.PUT_LINE('Square root of dept. ID = ' || item.col_alias);
    END IF;
  END LOOP;
 
  SELECT SYSTIMESTAMP INTO ending_time FROM DUAL;
 
  DBMS_OUTPUT.PUT_LINE('Time = ' || TO_CHAR(ending_time - starting_time));
END;
/

Result:

Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.046000000
Square root of dept. ID = 3.16227766016837933199889354443271853372
Square root of dept. ID = 4.47213595499957939281834733746255247088
Square root of dept. ID = 5.47722557505166113456969782800802133953
Square root of dept. ID = 6.32455532033675866399778708886543706744
Square root of dept. ID = 7.07106781186547524400844362104849039285
Square root of dept. ID = 7.74596669241483377035853079956479922167
Square root of dept. ID = 8.36660026534075547978172025785187489393
Square root of dept. ID = 8.94427190999915878563669467492510494176
Square root of dept. ID = 9.48683298050513799599668063329815560116
Square root of dept. ID = 10
Square root of dept. ID = 10.48808848170151546991453513679937598475
Time = +000000000 00:00:00.000000000

Tune Subprogram Invocations

If a subprogram has OUT or IN OUT parameters, you can sometimes decrease its invocation overhead by declaring those parameters with the NOCOPY hint (described in "NOCOPY").

By default, PL/SQL passes OUT and IN OUT subprogram parameters by value. Before running the subprogram, PL/SQL copies each OUT and IN OUT parameter to a temporary variable, which holds the value of the parameter during subprogram execution. If the subprogram is exited normally, then PL/SQL copies the value of the temporary variable to the corresponding actual parameter. If the subprogram is exited with an unhandled exception, then PL/SQL does not change the value of the actual parameter.

When OUT or IN OUT parameters represent large data structures such as collections, records, and instances of ADTs, copying them slows execution and increases memory use—especially for an instance of an ADT.

For each invocation of an ADT method, PL/SQL copies every attribute of the ADT. If the method is exited normally, then PL/SQL applies any changes that the method made to the attributes. If the method is exited with an unhandled exception, then PL/SQL does not change the attributes.

If your program does not require that an OUT or IN OUT parameter retain its pre-invocation value if the subprogram ends with an unhandled exception, then include the NOCOPY hint in the parameter declaration. The NOCOPY hint requests (but does not ensure) that the compiler pass the corresponding actual parameter by reference instead of value. For more information about NOCOPY, see "NOCOPY". For information about using NOCOPY with member methods of ADTs, see Oracle Database Object-Relational Developer's Guide.

Caution:

Do not rely on NOCOPY (which the compiler might or might not obey for a particular invocation) to ensure that an actual parameter or ADT attribute retains its pre-invocation value if the subprogram is exited with an unhandled exception. Instead, ensure that the subprogram handle all exceptions.

In Example 12-6, if the compiler obeys the NOCOPY hint for the invocation of do_nothing2, then the invocation of do_nothing2 is faster than the invocation of do_nothing1.

Example 12-6 NOCOPY with Parameters

DECLARE
  TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
  emp_tab EmpTabTyp := EmpTabTyp(NULL);  -- initialize
  t1 NUMBER;
  t2 NUMBER;
  t3 NUMBER;

  PROCEDURE get_time (t OUT NUMBER) IS
  BEGIN
    t := DBMS_UTILITY.get_time;
  END;

  PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
  BEGIN
    NULL;
  END;

  PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
  BEGIN
    NULL;
  END;

BEGIN
  SELECT * INTO emp_tab(1)
  FROM employees
  WHERE employee_id = 100;

  emp_tab.EXTEND(49999, 1);  -- Copy element 1 into 2..50000
  get_time(t1);
  do_nothing1(emp_tab);  -- Pass IN OUT parameter
  get_time(t2);
  do_nothing2(emp_tab);  -- Pass IN OUT NOCOPY parameter
  get_time(t3);
  DBMS_OUTPUT.PUT_LINE ('Call Duration (secs)');
  DBMS_OUTPUT.PUT_LINE ('--------------------');
  DBMS_OUTPUT.PUT_LINE ('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
  DBMS_OUTPUT.PUT_LINE ('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/

Tune Loops

Because PL/SQL applications are often built around loops, it is important to optimize both the loops themselves and the code inside them.

If you must loop through a result set more than once, or issue other queries as you loop through a result set, you might be able to change the original query to give you exactly the results you want. Explore the SQL set operators that let you combine multiple queries, described in Oracle Database SQL Language Reference.

You can also use subqueries to do the filtering and sorting in multiple stages—see "Query Result Set Processing with Subqueries".

Use SQL Character Functions

SQL provides many highly optimized character functions, which use low-level code that is more efficient than PL/SQL code. Use these functions instead of writing PL/SQL code to do the same things.

See:

Put Least Expensive Conditional Tests First

PL/SQL stops evaluating a logical expression as soon as it can determine the result. Take advantage of this short-circuit evaluation by putting the conditions that are least expensive to evaluate first in logical expressions whenever possible. For example, test the values of PL/SQL variables before testing function return values, so that if the variable tests fail, PL/SQL need not invoke the functions:

IF boolean_variable OR (number > 10) OR boolean_function(parameter) THEN ...

Minimize Implicit Data Type Conversion

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

Whenever possible, choose data types carefully to minimize implicit conversions. Use literals of the appropriate types, such as character literals in character expressions and decimal numbers in number expressions.

To minimize conversions, you can change the types of your variables, design your tables with different data types, or convert the data to PL/SQL data types and then use the converted data. Converting data from a SQL data type (such as INTEGER) to a PL/SQL data type (such as PLS_INTEGER) might improve performance, because of the more efficient hardware arithmetic.

Avoid NUMBER Data Type and Constrained Subtypes

The data type NUMBER and its subtypes are represented in a special internal format, designed for portability and arbitrary scale and precision, not for 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 invocations of library subprograms.

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.

Topics:

Recommended Data Types for Integer Arithmetic

When declaring a local integer variable:

Recommended Data Types for Floating-Point Arithmetic

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

For variables that you know will never have the value NULL, use the subtypes SIMPLE_FLOAT and SIMPLE_DOUBLE, explained in "Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE".

Note:

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

Bulk SQL and Bulk Binding

Bulk SQL minimizes the performance overhead of the communication between PL/SQL and SQL.

PL/SQL and SQL communicate as follows: To run a SELECT INTO or DML statement, PL/SQL sends the query or DML statement to the SQL engine. The SQL engine runs the query or DML statement and returns the result to PL/SQL.

The PL/SQL features that comprise bulk SQL are the FORALL statement and the BULK COLLECT clause of the SELECT INTO statement, FETCH statement, and RETURNING INTO clause. TheRETURNING INTO clause can appear in the DELETE, INSERT, UPDATE, or EXECUTE IMMEDIATE statement.

The FORALL statement sends queries or DML statements from PL/SQL to SQL in batches rather than one at a time. The BULK COLLECT clause returns results from SQL to PL/SQL in batches rather than one at a time. Bulk SQL passes the batches in either PL/SQL collections or host arrays, a process called bulk binding. (Binding is assigning values to PL/SQL or host variables referenced in SQL statements.) For a collection or array of n elements, bulk binding uses a single operation to perform the equivalent of n SELECT INTO or DML statements. A query that uses bulk binding can return any number of rows, without using a FETCH statement for each one. If a query or DML statement affects four or more database rows, then bulk binding can significantly improve performance.

Note:

Parallel DML is disabled with bulk binding.

PL/SQL binding operations fall into these categories:

Binding Category When This Binding Occurs
In-bind When an INSERT or UPDATE statement stores a PL/SQL or host variable in the database
Out-bind When the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement assigns a database value to a PL/SQL or host variable
DEFINE When a SELECT or FETCH statement assigns a database value to a PL/SQL or host variable

Topics:

FORALL Statement

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

The DML statement can reference multiple collections, 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 PLS_INTEGER and whose elements are of type PLS_INTEGER. The FORALL statement iterates over the index values specified by the elements of this collection.

The FORALL statement in Example 12-7 sends three DELETE statements to the SQL engine simultaneously.

Example 12-7 Issuing DELETE Statements in a Loop

DROP TABLE employees_temp;
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 12-8 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 faster. (Elapsed times for the FOR loop and FORALL statement vary from run to run.)

Example 12-8 Issuing INSERT Statements in a Loop

DROP TABLE parts1;
CREATE TABLE parts1 (
  pnum INTEGER,
  pname VARCHAR2(15)
);
 
DROP TABLE parts2;
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 := 50000;
  t1  INTEGER;
  t2  INTEGER;
  t3  INTEGER;
BEGIN
  FOR j IN 1..iterations LOOP  -- load associative arrays
    pnums(j) := j;
    pnames(j) := 'Part No. ' || TO_CHAR(j);
  END LOOP;

  t1 := DBMS_UTILITY.get_time;

  FOR i IN 1..iterations LOOP
    INSERT INTO parts1 (pnum, pname)
    VALUES (pnums(i), pnames(i));
  END LOOP;

  t2 := DBMS_UTILITY.get_time;

  FORALL i IN 1..iterations
    INSERT INTO parts2 (pnum, pname)
    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;
/

Result is similar to:

Execution Time (secs)
---------------------
FOR LOOP: 2.16
FORALL:   .11
 
PL/SQL procedure successfully completed.

The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements, as Example 12-9 shows.

Example 12-9 FORALL Statement for Part of Collection

DROP TABLE employees_temp;
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 multiple times. Instead of copying the entire elements into collections, which might consume 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 12-10 creates a collection holding some arbitrary data, a set of table names. Deleting some elements makes it a sparse collection that does 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.

Example 12-10 FORALL Statement for Nonconsecutive Index Values

-- Create empty tables to hold order details:

DROP TABLE valid_orders;
CREATE TABLE valid_orders (
  cust_name  VARCHAR2(32),
  amount     NUMBER(10,2)
);

DROP TABLE big_orders;
CREATE TABLE big_orders AS
  SELECT * FROM valid_orders
  WHERE 1 = 0;

DROP TABLE rejected_orders;
CREATE TABLE rejected_orders AS
  SELECT * FROM valid_orders
  WHERE 1 = 0;

DECLARE
  -- Collections for 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;

  -- Collections to point into 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 setup_data;

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;

  /* Subscripts of collections are not consecutive,
     so use FORALL...INDICES OF to iterate through actual subscripts,
     rather than using 1..COUNT. */

  FORALL i IN INDICES OF cust_tab
    INSERT INTO valid_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));

  /* Process the order data differently:
     Extract 2 subsets and store each subset in a different table.
     Reinitialize the CUST_TAB and AMOUNT_TAB collections. */

  setup_data();

  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;
      rejected_order_tab(rejected_order_tab.LAST) := i; 
    END IF;
    IF amount_tab(i) > 2000 THEN
      big_order_tab.EXTEND;
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
  END LOOP;

  /* Run one DML statement n one subset of elements
     and another DML statement on another subset. */

  FORALL i IN VALUES OF rejected_order_tab
    INSERT INTO rejected_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));
  FORALL i IN VALUES OF big_order_tab
    INSERT INTO big_orders (cust_name, amount)
    VALUES (cust_tab(i), amount_tab(i));
  COMMIT;
END;
/

Result:

--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

Verify that correct order details were stored:

SELECT cust_name "Customer", amount "Valid order amount"
FROM valid_orders
ORDER BY cust_name;

Result:

Customer                         Valid order amount
-------------------------------- ------------------
Company1                                    5000.01
Company3                                     150.25
Company4                                       4000
 
3 rows selected.

Query:

SELECT cust_name "Customer", amount "Big order amount"
FROM big_orders
ORDER BY cust_name;

Result:

Customer                         Big order amount
-------------------------------- ----------------
Company1                                  5000.01
Company4                                     4000
 
2 rows selected.

Query:

SELECT cust_name "Customer", amount "Rejected order amount"
FROM rejected_orders
ORDER BY cust_name;

Result:

Customer                         Rejected order amount
-------------------------------- ---------------------
Company2                                             0
Company5
 
2 rows selected.

Topics:

Effect of FORALL Exceptions on Rollbacks

In a FORALL statement, if any execution of the DML 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 shown in Example 12-11. 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 you handle the exception, the first UPDATE is not rolled back and you can commit that change.

Example 12-11 Rollbacks with FORALL Statement

DROP TABLE emp_temp;
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 (deptno, job)
  VALUES(10, 'Clerk');
 
  -- Lengthening this job title raises an exception.
 
  INSERT INTO emp_temp (deptno, job)
  VALUES(20, 'Bookkeeper');
 
  INSERT INTO emp_temp  (deptno, job)
  VALUES(30, 'Analyst');
 
  COMMIT;
 
  FORALL j IN depts.FIRST..depts.LAST
    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;
/

Result:

Problem in the FORALL statement.

Exception Handling in FORALL Statements

PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.

To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement. Provide an exception handler to track the exceptions that occurred during the bulk operation.

Example 12-12 shows how you can perform several DML operations without stopping if some operations encounter errors. The example uses PRAGMA EXCEPTION_INIT to associate the name DML_ERRORS with ORA-24381. PL/SQL raises ORA-24381 if any exceptions are caught and saved after a bulk operation.

All exceptions raised during the execution are saved in the cursor attribute SQL%BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:

  • SQL%BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration of the FORALL statement during which the exception was raised.

  • SQL%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle Database error code.

The values stored by SQL%BULK_EXCEPTIONS always refer to the most recently run FORALL statement. The number of exceptions is saved in SQL%BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.

The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM as shown in Example 12-12.

You might need to work backward to determine which collection element was used in the iteration that caused an exception. For example, if you use the INDICES OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to SQL%BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the VALUES OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches SQL%BULK_EXCEPTIONS(i).ERROR_INDEX, and then use that element's value as the subscript to find the erroneous element in the original collection.

If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when PL/SQL raises an exception. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If PL/SQL raises no exception during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.

In Example 12-12, the bulk operation continues despite exceptions.

Example 12-12 FORALL Statement and SQL%BULK_EXCEPTIONS

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
  TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
  emp_sr empid_tab;

  -- 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 job_id of 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 errors occurred during FORALL SAVE EXCEPTIONS,
       PL/SQL raises a single exception when the statement completes. */
EXCEPTION
  -- Figure out what failed and why
  WHEN dml_errors THEN
    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;
/

In Example 12-12, PL/SQL raises predefined exceptions because updated values were too large to insert into the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returned 2, and the contents of SQL%BULK_EXCEPTIONS were (7,12899) and (13,12899).

To get the Oracle Database error message (which includes the code), the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE was negated and then passed to the error-reporting function SQLERRM, which expects a negative number.

Counting Rows Affected by FORALL

The composite attribute SQL%BULK_ROWCOUNT, used with the FORALL statement, works like an associative array. SQL%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an INSERT, UPDATE or DELETE statement, as in Example 12-13.

Example 12-13 FORALL Statement and SQL%BULK_ROWCOUNT

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

Result:

Iteration #1 deleted 6 rows.
Iteration #2 deleted 45 rows.
Iteration #3 deleted 5 rows.

The FORALL statement and SQL%BULK_ROWCOUNT attribute use the same subscripts. For example, if FORALL uses the range 5..10, so does SQL%BULK_ROWCOUNT. If the FORALL statement uses the INDICES OF clause to process a sparse collection, SQL%BULK_ROWCOUNT has corresponding sparse subscripts. If the FORALL statement uses the VALUES OF clause to process a subset of elements, SQL%BULK_ROWCOUNT has subscripts corresponding to the values of the elements in the index collection. If the index collection contains duplicate elements, so that some DML statements are issued multiple times using the same subscript, then the corresponding elements of SQL%BULK_ROWCOUNT represent the sum of all rows affected by the DML statement using that subscript.

SQL%BULK_ROWCOUNT is usually equal to 1 for inserts, because a typical insert operation affects only a single row. For the INSERT SELECT construct, SQL%BULK_ROWCOUNT might be greater than 1. For example, the FORALL statement in Example 12-14 inserts an arbitrary number of rows for each iteration. After each iteration, SQL%BULK_ROWCOUNT returns the number of items inserted.

Example 12-14 Counting Rows Affected by FORALL with SQL%BULK_ROWCOUNT

DROP TABLE emp_by_dept;
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 (employee_id, department_id)
      SELECT employee_id, department_id
      FROM employees
      WHERE department_id = deptnums(i)
      ORDER BY department_id, employee_id;

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

Result:

Dept 10: inserted 1 records
Dept 20: inserted 2 records
Dept 30: inserted 6 records
Dept 40: inserted 1 records
Dept 50: inserted 45 records
Dept 60: inserted 5 records
Dept 70: inserted 1 records
Dept 80: inserted 34 records
Dept 90: inserted 3 records
Dept 100: inserted 6 records
Dept 110: inserted 2 records
Dept 120: inserted 0 records
Dept 130: inserted 0 records
Dept 140: inserted 0 records
Dept 150: inserted 0 records
Dept 160: inserted 0 records
Dept 170: inserted 0 records
Dept 180: inserted 0 records
Dept 190: inserted 0 records
Dept 200: inserted 0 records
Dept 210: inserted 0 records
Dept 220: inserted 0 records
Dept 230: inserted 0 records
Dept 240: inserted 0 records
Dept 250: inserted 0 records
Dept 260: inserted 0 records
Dept 270: inserted 0 records
Dept 280: inserted 0 records
Total records inserted: 106

You can also use the implicit cursor attributes explained in "Implicit Cursors" after running a FORALL statement.

BULK COLLECT Clause

Retrieving query results into one or more collections in a single operation is more efficient than using loops to retrieve one result row at a time. To retrieve query results into collections, use the BULK COLLECT clause. The BULK COLLECT clause can appear in the SELECT INTO statement, FETCH statement, or RETURNING INTO clause.

Note:

PL/SQL processes the BULK COLLECT INTO clause similar to the way it processes a FETCH statement inside a LOOP statement. PL/SQL does not raise an exception when a statement with a BULK COLLECT INTO clause returns no rows. You must check the target collections for emptiness (if they are associative arrays) or nullness (if they are varrays or nested tables), as in Example 12-21.

Topics:

SELECT INTO Statement with BULK COLLECT Clause

The SELECT INTO statement with the BULK COLLECT clause selects an entire result set into one or more collection variables. For more information about this statement, see "SELECT INTO Statement".

Caution:

The SELECT INTO statement with the BULK COLLECT clause is vulnerable to aliasing, which can cause unexpected results. For details, see "SELECT BULK COLLECT INTO Statements and Aliasing".

Example 12-15 selects two database columns into two collections (nested tables).

Example 12-15 Bulk-Selecting Two Database Columns into Two Nested Tables

DECLARE
  TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
  TYPE NameTab IS TABLE OF employees.last_name%TYPE;
 
  enums NumTab;
  names NameTab;
 
  PROCEDURE print_first_n (n POSITIVE) IS
  BEGIN
    IF enums.COUNT = 0 THEN 
      DBMS_OUTPUT.PUT_LINE ('Collections are empty.');
    ELSE
      DBMS_OUTPUT.PUT_LINE ('First ' || n || ' employees:');
 
      FOR i IN 1 .. n LOOP
        DBMS_OUTPUT.PUT_LINE (
          '  Employee #' || enums(i) || ': ' || names(i));
      END LOOP;
    END IF;
  END;
 
BEGIN
  SELECT employee_id, last_name
  BULK COLLECT INTO enums, names
  FROM employees
  ORDER BY employee_id;
 
  print_first_n(3);
  print_first_n(6);
END;
/

Result:

First 3 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
First 6 employees:
Employee #100: King
Employee #101: Kochhar
Employee #102: De Haan
Employee #103: Hunold
Employee #104: Ernst
Employee #105: Austin

Example 12-16 selects a result set into a nested table of records.

Example 12-16 Bulk-Selecting into Nested Table of Records

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
 
  stock_managers  NameSet;  -- nested table of records
 
BEGIN 
  -- Assign values to nested table of records:
 
  SELECT first_name, last_name, hire_date
    BULK COLLECT INTO stock_managers
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

Result:

01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin

Topics:

SELECT BULK COLLECT INTO Statements and Aliasing

In a statement of the form

SELECT column BULK COLLECT INTO collection FROM table ...

column and collection are analogous to IN NOCOPY and OUT NOCOPY subprogram parameters, respectively, and PL/SQL passes them by reference. As with subprogram parameters that are passed by reference, aliasing can cause unexpected results.

In Example 12-17, the intention is to select specific values from a collection, numbers1, and then store them in the same collection. The unexpected result is that all elements of numbers1 are deleted. For workarounds, see Example 12-18 and Example 12-19.

Example 12-17 SELECT BULK COLLECT INTO Statement with Unexpected Results

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
  
  FOR j IN 1..numbers1.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
  END LOOP;
 
  --Self-selecting BULK COLLECT INTO clause:
 
  SELECT a.COLUMN_VALUE
  BULK COLLECT INTO numbers1
  FROM TABLE(numbers1) a
  WHERE a.COLUMN_VALUE > p.i
  ORDER BY a.COLUMN_VALUE;
 
  DBMS_OUTPUT.PUT_LINE('After SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
END p;
/

Invoke p:

BEGIN
  p(2);
END;
/

Result:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After SELECT statement
numbers1.COUNT() = 0
 
PL/SQL procedure successfully completed.

Invoke p:

BEGIN
  p(10);
END;
/

Result:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After SELECT statement
numbers1.COUNT() = 0

Example 12-18 uses a cursor to achieve the result intended by Example 12-17.

Example 12-18 Cursor Workaround for Example 12-17

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
  
  CURSOR c IS
    SELECT a.COLUMN_VALUE
    FROM TABLE(numbers1) a
    WHERE a.COLUMN_VALUE > p.i
    ORDER BY a.COLUMN_VALUE;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Before FETCH statement');
    DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
 
  OPEN c;
  FETCH c BULK COLLECT INTO numbers1;
  CLOSE c;
 
  DBMS_OUTPUT.PUT_LINE('After FETCH statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
  IF numbers1.COUNT() > 0 THEN
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
  END IF;
END p;
/

Invoke p:

BEGIN
  p(2);
END;
/

Result:

Before FETCH statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After FETCH statement
numbers1.COUNT() = 3
numbers1(1) = 3
numbers1(2) = 4
numbers1(3) = 5

Invoke p:

BEGIN
  p(10);
END;
/

Result:

Before FETCH statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
After FETCH statement
numbers1.COUNT() = 0

Example 12-19 selects specific values from a collection, numbers1, and then stores them in a different collection, numbers2. Example 12-19 performs faster than Example 12-18.

Example 12-19 Second Collection Workaround for Example 12-17

CREATE OR REPLACE TYPE numbers_type IS
  TABLE OF INTEGER
/
CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS
  numbers1  numbers_type := numbers_type(1,2,3,4,5);
 numbers2  numbers_type := numbers_type(0,0,0,0,0);
  
BEGIN
  DBMS_OUTPUT.PUT_LINE('Before SELECT statement');
  
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
  
  FOR j IN 1..numbers1.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
 
  FOR j IN 1..numbers2.COUNT() LOOP
    DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
  END LOOP;
 
  SELECT a.COLUMN_VALUE
  BULK COLLECT INTO numbers2      -- numbers2 appears here
  FROM TABLE(numbers1) a        -- numbers1 appears here
  WHERE a.COLUMN_VALUE > p.i
  ORDER BY a.COLUMN_VALUE;
 
  DBMS_OUTPUT.PUT_LINE('After SELECT statement');
  DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT());
 
  IF numbers1.COUNT() > 0 THEN
    FOR j IN 1..numbers1.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j));
    END LOOP;
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT());
 
  IF numbers2.COUNT() > 0 THEN
    FOR j IN 1..numbers2.COUNT() LOOP
      DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j));
    END LOOP;
  END IF;
END p;
/

Invoke p:

BEGIN
  p(2);
 END;
/

Result:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 5
numbers2(1) = 0
numbers2(2) = 0
numbers2(3) = 0
numbers2(4) = 0
numbers2(5) = 0
After SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 3
numbers2(1) = 3
numbers2(2) = 4
numbers2(3) = 5
 
PL/SQL procedure successfully completed.

Invoke p:

BEGIN
  p(10);
END;
/

Result:

Before SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 5
numbers2(1) = 0
numbers2(2) = 0
numbers2(3) = 0
numbers2(4) = 0
numbers2(5) = 0
After SELECT statement
numbers1.COUNT() = 5
numbers1(1) = 1
numbers1(2) = 2
numbers1(3) = 3
numbers1(4) = 4
numbers1(5) = 5
numbers2.COUNT() = 0
Limiting Rows for a Bulk SELECT Operation (ROWNUM Pseudocolumn)

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

You can process very large result sets by fetching a specified number of rows at a time from a cursor.

Example 12-20 Limiting Query Results with Pseudocolumn ROWNUM

DECLARE
  TYPE SalList IS TABLE OF employees.salary%TYPE;
  sals SalList;
BEGIN
  -- Limit number of rows to 50

  SELECT salary BULK COLLECT INTO sals
  FROM employees
  WHERE ROWNUM <= 50;

  -- Retrieve ~10% rows from table

  SELECT salary BULK COLLECT INTO sals FROM employees SAMPLE (10);
END;
/
Guidelines for Looping Through Collections

When a result set is stored in a collection, it is easy to loop through the results and refer to different columns. This technique can be very fast, but also very memory-intensive. If you use it often:

  • To loop once through the result set, use a cursor FOR LOOP (see "Query Result Set Processing With Cursor FOR LOOP Statements").

    This technique avoids the memory overhead of storing a copy of the result set.

  • Instead of looping through the result set to search for certain values or filter the results into a smaller set, do the searching or filtering in the query of the SELECT INTO statement.

    For example, in simple queries, use WHERE clauses; in queries that compare multiple result sets, use set operators such as INTERSECT and MINUS. For information about set operators, see Oracle Database SQL Language Reference.

  • Instead of looping through the result set and running another query for each result row, use a subquery in the query of the SELECT INTO statement (see "Query Result Set Processing with Subqueries").

  • Instead of looping through the result set and running another DML statement for each result row, use the FORALL statement (see "FORALL Statement").

FETCH Statement with BULK COLLECT Clause

The FETCH statement with the BULK COLLECT clause fetches an entire result set into one or more collections. For more information about this statement, see "FETCH Statement".

Example 12-21 fetches an entire result set into two collections (nested tables).

Example 12-21 Bulk-Fetching into Two Nested Tables

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
    ORDER BY last_name;

  names  NameList;
  sals   SalList;

  TYPE RecList IS TABLE OF c1%ROWTYPE;
  recs RecList;

  v_limit PLS_INTEGER := 10;

  PROCEDURE print_results IS
  BEGIN
    -- Check if collections are empty:

    IF names IS NULL OR names.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('No results!');
    ELSE
      DBMS_OUTPUT.PUT_LINE('Result: ');
      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 simultaneously ---');
  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 columns from result set come from one record
    DBMS_OUTPUT.PUT_LINE (
      '  Employee ' || recs(i).last_name || ': $' || recs(i).salary
    );
  END LOOP;
END;
/

Result:

--- Processing all results simultaneously ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500
--- Processing 10 rows at a time ---
Result:
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Result:
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500
--- Fetching records rather than columns ---
Employee Abel: $11000
Employee Cambrault: $11000
Employee De Haan: $17000
Employee Errazuriz: $12000
Employee Fripp: $18540.29
Employee Greenberg: $12008
Employee Hartstein: $13000
Employee Higgins: $12008
Employee Kaufling: $17862
Employee King: $24000
Employee Kochhar: $17000
Employee Mourgos: $13113.87
Employee Ozer: $11500
Employee Partners: $13500
Employee Raphaely: $11000
Employee Russell: $14000
Employee Vishney: $10500
Employee Vollman: $14696.58
Employee Weiss: $22907.66
Employee Zlotkey: $10500

Example 12-22 fetches a result set into a collection (nested table) of records.

Example 12-22 Bulk-Fetching into Nested Table of Records

DECLARE
  CURSOR c1 IS
    SELECT first_name, last_name, hire_date
    FROM employees;
  
  TYPE NameSet IS TABLE OF c1%ROWTYPE;
  stock_managers  NameSet;  -- nested table of records
 
  TYPE cursor_var_type is REF CURSOR;
  cv cursor_var_type;
 
BEGIN 
  -- Assign values to nested table of records:
 
  OPEN cv FOR
    SELECT first_name, last_name, hire_date
    FROM employees
    WHERE job_id = 'ST_MAN'
    ORDER BY hire_date;
 
  FETCH cv BULK COLLECT INTO stock_managers;
  CLOSE cv;
 
  -- Print nested table of records:
 
    FOR i IN stock_managers.FIRST .. stock_managers.LAST LOOP
      DBMS_OUTPUT.PUT_LINE (
        stock_managers(i).hire_date || ' ' ||
        stock_managers(i).last_name  || ', ' ||
        stock_managers(i).first_name
      );
    END LOOP;END;
/

Result:

01-MAY-03 Kaufling, Payam
18-JUL-04 Weiss, Matthew
10-APR-05 Fripp, Adam
10-OCT-05 Vollman, Shanta
16-NOV-07 Mourgos, Kevin
Limiting Rows for a Bulk FETCH Operation (LIMIT Clause)

The optional LIMIT clause, allowed only in bulk FETCH statements, limits the number of rows fetched from the database.

In Example 12-23, with each iteration of the loop, the FETCH statement fetches ten rows (or fewer) into associative array empids. The previous values are overwritten. Note the use of empids.COUNT to determine when to exit the loop.

Example 12-23 Controlling Number of BULK COLLECT Rows with LIMIT

DECLARE
  TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  CURSOR c1 IS
    SELECT employee_id
    FROM employees
    WHERE department_id = 80
    ORDER BY employee_id;

  empids  numtab;
  rows    PLS_INTEGER := 10;
BEGIN
  OPEN c1;
  LOOP  -- Fetch 10 rows or fewer in each iteration
    FETCH c1 BULK COLLECT INTO empids LIMIT rows;
    EXIT WHEN empids.COUNT = 0;  -- Not: EXIT WHEN c1%NOTFOUND
    DBMS_OUTPUT.PUT_LINE ('------- Results from One Bulk Fetch --------');
    FOR i IN 1..empids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE ('Employee Id: ' || empids(i));
    END LOOP;
  END LOOP;
  CLOSE c1;
END;
/

Result:

------- Results from One Bulk Fetch --------
Employee Id: 145
Employee Id: 146
Employee Id: 147
Employee Id: 148
Employee Id: 149
Employee Id: 150
Employee Id: 151
Employee Id: 152
Employee Id: 153
Employee Id: 154
------- Results from One Bulk Fetch --------
Employee Id: 155
Employee Id: 156
Employee Id: 157
Employee Id: 158
Employee Id: 159
Employee Id: 160
Employee Id: 161
Employee Id: 162
Employee Id: 163
Employee Id: 164
------- Results from One Bulk Fetch --------
Employee Id: 165
Employee Id: 166
Employee Id: 167
Employee Id: 168
Employee Id: 169
Employee Id: 170
Employee Id: 171
Employee Id: 172
Employee Id: 173
Employee Id: 174
------- Results from One Bulk Fetch --------
Employee Id: 175
Employee Id: 176
Employee Id: 177
Employee Id: 179

RETURNING INTO Clause with BULK COLLECT Clause

The RETURNING INTO clause with the BULK COLLECT clause can appear in an INSERT, UPDATE, DELETE, or EXECUTE IMMEDIATE statement. The BULK COLLECT clause causes the statement to stores its result set in one or more collections. For more information, see "RETURNING INTO Clause".

Example 12-24 deletes rows from a table and returns them in two collections (nested tables).

Example 12-24 Returning Deleted Rows in Two Nested Tables

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id;

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

Result:

Deleted 6 rows:
Employee #114: Raphaely
Employee #115: Khoo
Employee #116: Baida
Employee #117: Tobias
Employee #118: Himuro
Employee #119: Colmenares

Using FORALL and BULK COLLECT Together

You can combine the BULK COLLECT clause with a FORALL statement. The output collections are built up as the FORALL statement iterates.

In Example 12-25, the employee_id value of each deleted row is stored in the collection e_ids. The collection depts has three elements, so the FORALL statement iterates three times. If each DELETE issued by the FORALL statement deletes five rows, then the collection e_ids, which stores values from the deleted rows, has 15 elements when the statement completes.

Example 12-25 FORALL with BULK COLLECT

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
SELECT * FROM employees
ORDER BY employee_id, department_id;

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  depts  NumList := NumList(10,20,30);

  TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
  e_ids  enum_t;

  TYPE dept_t IS TABLE OF employees.department_id%TYPE;
  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;
/

Result:

Deleted 9 rows:
Employee #200 from dept #10
Employee #201 from dept #20
Employee #202 from dept #20
Employee #114 from dept #30
Employee #115 from dept #30
Employee #116 from dept #30
Employee #117 from dept #30
Employee #118 from dept #30
Employee #119 from dept #30

The column values returned by each execution are added to the values returned previously. If you use a FOR loop instead of the FORALL statement, the set of returned values is overwritten by each DELETE statement.

You cannot use the SELECT BULK COLLECT statement in a FORALL statement.

Client Bulk-Binding of Host Arrays

Client programs (such as OCI and Pro*C programs) can use PL/SQL anonymous blocks to bulk-bind input and output host arrays. This is the most efficient way to pass collections to and from the database server.

In the client program, declare and assign values to the host variables to be referenced in the anonymous block. In the anonymous block, prefix each host variable name with a colon (:) to distinguish it from a PL/SQL collection variable name. When the client program runs, the database server runs the PL/SQL anonymous block.

In Example 12-26, the anonymous block uses a FORALL statement to bulk-bind a host input array. In the FORALL statement, the DELETE statement refers to four host variables: scalars lower, upper, and emp_id and array depts.

Example 12-26 Anonymous Block That Bulk-Binds Input Host Array

BEGIN
  FORALL i IN :lower..:upper
    DELETE FROM employees
    WHERE department_id = :depts(i);
END;
/

Collecting Data About User-Defined Identifiers

PL/Scope extracts, organizes, and stores data about user-defined identifiers from PL/SQL source code. You can retrieve source code identifier data with the static data dictionary views *_IDENTIFIERS. For more information, see Oracle Database Advanced Application Developer's Guide.

Profiling and Tracing PL/SQL Programs

To help you isolate performance problems in large PL/SQL programs, PL/SQL provides these tools, implemented as PL/SQL packages:

Tool Package Description
Profiler API DBMS_PROFILER Computes the time that your PL/SQL program spends at each line and in each subprogram.

You must have CREATE privileges on the units to be profiled.

Saves run-time statistics in database tables, which you can query.

Trace API DBMS_TRACE Traces the order in which subprograms run.

You can specify the subprograms to trace and the tracing level.

Saves run-time statistics in database tables, which you can query.

PL/SQL hierarchical profiler DBMS_HPROF Reports the dynamic execution program profile of your PL/SQL program, organized by subprogram invocations. Accounts for SQL and PL/SQL execution times separately.

Requires no special source or compile-time preparation.

Generates reports in HTML. Provides the option of storing results in relational format in database tables for custom report generation (such as third-party tools offer).


Topics:

For a detailed description of PL/SQL hierarchical profiler, see Oracle Database Advanced Application Developer's Guide.

Profiler API: Package DBMS_PROFILER

The Profiler API ("Profiler") is implemented as PL/SQL package DBMS_PROFILER, whose services compute the time that your PL/SQL program spends at each line and in each subprogram and save these statistics in database tables, which you can query.

Note:

You can use Profiler only on units for which you have CREATE privilege. You do not need the CREATE privilege to use the PL/SQL hierarchical profiler (see Oracle Database Advanced Application Developer's Guide).

To use Profiler:

  1. Start the profiling session.

  2. Run your PL/SQL program long enough to get adequate code coverage.

  3. Flush the collected data to the database.

  4. Stop the profiling session.

After you have collected data with Profiler, you can:

  1. Query the database tables that contain the performance data.

  2. Identify the subprograms and packages that use the most execution time.

  3. Determine why your program spent more time accessing certain data structures and running certain code segments.

    Inspect possible performance bottlenecks such as SQL statements, loops, and recursive functions.

  4. Use the results of your analysis to replace inappropriate data structures and rework slow algorithms.

    For example, with an exponential growth in data, you might need to replace a linear search with a binary search.

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

Trace API: Package DBMS_TRACE

The Trace API ("Trace") is implemented as PL/SQL package DBMS_TRACE, whose services trace execution by subprogram or exception and save these statistics in database tables, which you can query.

To use Trace:

  1. (Optional) Limit tracing to specific subprograms and choose a tracing level.

    Tracing all subprograms and exceptions in a large program can produce huge amounts of data that are difficult to manage.

  2. Start the tracing session.

  3. Run your PL/SQL program.

  4. Stop the tracing session.

After you have collected data with Trace, you can query the database tables that contain the performance data and analyze it in the same way that you analyze the performance data from Profiler (see "Profiler API: Package DBMS_PROFILER").

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

Tuning Computation-Intensive PL/SQL Code

For PL/SQL code that does many mathematical calculations, investigate the data types PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE.

For integer arithmetic, the PLS_INTEGER data type is more efficient than the NUMBER or INTEGER data type. You can use PLS_INTEGER to write pure PL/SQL code for integer arithmetic, or convert NUMBER or INTEGER values to PLS_INTEGER for manipulation by PL/SQL. For more information about PLS_INTEGER, see "PLS_INTEGER and BINARY_INTEGER Data Types".

The BINARY_FLOAT and BINARY_DOUBLE data types make it practical to write PL/SQL programs to do number-crunching, for scientific applications involving floating-point calculations. These data types act much like the native floating-point types on many hardware systems, with semantics derived from the IEEE-754 floating-point standard. The way these data types represent decimal data make them less suitable for financial applications, where precise representation of fractional amounts is more important than pure performance. (see Oracle Database SQL Language Reference)

In a package, you can write overloaded versions of subprograms that accept different numeric parameters. The math subprograms can be optimized for each kind of parameter (BINARY_FLOAT, BINARY_DOUBLE, NUMBER, PLS_INTEGER), avoiding unnecessary conversions.

The built-in math functions (such as SQRT, SIN, COS) have fast overloaded versions that accept BINARY_FLOAT and BINARY_DOUBLE parameters. You can speed up math-intensive code by passing variables of these types to such functions, and by invoking the TO_BINARY_FLOAT or TO_BINARY_DOUBLE functions when passing expressions to such functions.

Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables

Some programs (a general-purpose report writer for example) must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements.

Formerly, to run dynamic SQL statements, you had to use the supplied package DBMS_SQL. Now, in PL/SQL, you can run any kind of dynamic SQL statement using an interface called native dynamic SQL. The main PL/SQL features involved are the EXECUTE IMMEDIATE statement and cursor variables.

Native dynamic SQL code is more compact and much faster than calling theDBMS_SQL package. Example 12-27 declares a cursor variable and associates it with a dynamic SELECT statement.

Example 12-27 Associating a Cursor with a Dynamic SELECT Statement

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  emp_cv      EmpCurTyp;
  v_ename     VARCHAR2(15);
  v_sal       NUMBER := 1000;
  table_name  VARCHAR2(30) := 'employees';
BEGIN
  OPEN emp_cv FOR 'SELECT last_name, salary FROM ' || table_name ||
    ' WHERE salary > :s' USING v_sal;
  CLOSE emp_cv;
END;
/

For more information, see Chapter 7, "PL/SQL Dynamic SQL."

Compiling PL/SQL Units for Native Execution

You can usually speed up PL/SQL units by compiling them into native code (processor-dependent system code), which is stored in the SYSTEM tablespace.

You can natively compile any PL/SQL unit of any type, including those that Oracle Database supplies.

Natively compiled program units work in all server environments, including shared server configuration (formerly called "multithreaded server") and Oracle Real Application Clusters (Oracle RAC).

On most platforms, PL/SQL native compilation requires no special set-up or maintenance. On some platforms, the DBA might want to do some optional configuration.

See Also:

You can test to see how much performance gain you can get by enabling PL/SQL native compilation.

If you have determined that PL/SQL native compilation will provide significant performance gains in database operations, Oracle recommends compiling the entire database for native mode, which requires DBA privileges. This speeds up both your own code and calls to all of the built-in PL/SQL packages.

Topics:

* Requires DBA privileges.

Determining Whether to Use PL/SQL Native Compilation

Whether to compile a PL/SQL unit for native or interpreted mode depends on where you are in the development cycle and on what the program unit does.

While you are debugging program units and recompiling them frequently, interpreted mode has these advantages:

  • You can use PL/SQL debugging tools on program units compiled for interpreted mode (but not for those compiled for native mode).

  • Compiling for interpreted mode is faster than compiling for native mode.

After the debugging phase of development, in determining whether to compile a PL/SQL unit for native mode, consider:

  • PL/SQL native compilation provides the greatest performance gains for computation-intensive procedural operations. Examples are data warehouse applications and applications with extensive server-side transformations of data for display.

  • PL/SQL native compilation provides the least performance gains for PL/SQL subprograms that spend most of their time running SQL.

  • When many program units (typically over 15,000) are compiled for native execution, and are simultaneously active, the large amount of shared memory required might affect system performance.

How PL/SQL Native Compilation Works

Without native compilation, the PL/SQL statements in a PL/SQL unit are compiled into an intermediate form, system code, which is stored in the catalog and interpreted at run time.

With PL/SQL native compilation, the PL/SQL statements in a PL/SQL unit are compiled into native code and stored in the catalog. The native code need not be interpreted at run time, so it runs faster.

Because native compilation applies only to PL/SQL statements, a PL/SQL unit that uses only SQL statements might not run faster when natively compiled, but it does run at least as fast as the corresponding interpreted code. The compiled code and the interpreted code make the same library calls, so their action is the same.

The first time a natively compiled PL/SQL unit runs, it is fetched from the SYSTEM tablespace into shared memory. Regardless of how many sessions invoke the program unit, shared memory has only one copy it. If a program unit is not being used, the shared memory it is using might be freed, to reduce memory load.

Natively compiled subprograms and interpreted subprograms can invoke each other.

PL/SQL native compilation works transparently in an Oracle Real Application Clusters (Oracle RAC) environment.

The PLSQL_CODE_TYPE compilation parameter determines whether PL/SQL code is natively compiled or interpreted. For information about this compilation parameters, see "PL/SQL Units and Compilation Parameters".

Dependencies, Invalidation, and Revalidation

Recompilation is automatic with invalidated PL/SQL modules. For example, if an object on which a natively compiled PL/SQL subprogram depends changes, the subprogram is invalidated. The next time the same subprogram is called, the database recompiles the subprogram automatically. Because the PLSQL_CODE_TYPE setting is stored inside the library unit for each subprogram, the automatic recompilation uses this stored setting for code type.

Explicit recompilation does not necessarily use the stored PLSQL_CODE_TYPE setting. For the conditions under which explicit recompilation uses stored settings, see "PL/SQL Units and Compilation Parameters".

Setting Up a New Database for PL/SQL Native Compilation

If you have DBA privileges, you can set up a new database for PL/SQL native compilation by setting the compilation parameter PLSQL_CODE_TYPE to NATIVE. The performance benefits apply to all the built-in PL/SQL packages, which are used for many database operations.

Note:

If you compile the whole database as NATIVE, Oracle recommends that you set PLSQL_CODE_TYPE at the system level.

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

If you have DBA privileges, 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 explained in this section. Before making the conversion, review "Determining Whether to Use PL/SQL Native Compilation".

Note:

If you compile the whole database as NATIVE, Oracle recommends that you set PLSQL_CODE_TYPE at the system level.

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 run-time 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.

Note:

The following procedure describes the conversion to native compilation. If you must recompile all PL/SQL modules to interpreted compilation, make these changes in the steps.
  • Skip the first step.

  • Set the PLSQL_CODE_TYPE compilation parameter to INTERPRETED rather than NATIVE.

  • Substitute dbmsupgin.sql for the dbmsupgnv.sql script.

  1. Ensure that a test PL/SQL unit can be compiled. 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 were 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 Oracle Database Administrator's Guide.

  3. Set PLSQL_CODE_TYPE to NATIVE in the compilation parameter file. If the database is using a server parameter file, then set this after the database has started.

    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, so explicitly set it to the desired compilation type.

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

  5. Run this code to list the invalid PL/SQL units. You can save the output of the query for future reference with the SQL SPOOL statement:

    -- 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';
    -- To stop spooling the output: SPOOL OFF
    

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

    ALTER PACKAGE SYS.DBMS_OUTPUT COMPILE BODY REUSE SETTINGS;
    

    If the units cannot be validated, save the spooled log for future resolution and continue.

  6. Run this query to determine how many objects are compiled NATIVE and INTERPRETED (to save the output, use the SQL SPOOL statement):

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

    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 parallelism. See the comments in the script for information about 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 invalid PL/SQL units using the query in step 5. 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. Reexecute the query in step 6. 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, use this statement:

    ALTER SYSTEM DISABLE RESTRICTED SESSION;
    

Performing Multiple Transformations with Pipelined Table Functions

An efficient way to perform multiple transformations on data is to chain pipelined table functions.

Pipelined table functions let you program row sources. To select rows from these sources, you invoke the pipelined table functions in SELECT statements. In a SELECT statement, a pipelined function invocation can appear in the select list or FROM clause. In the FROM clause, put the invocation in a table collection expression.

Note:

You cannot run a pipelined table function over a database link. The reason is that the return type of a pipelined table function is a SQL user-defined type, which can be used only in a single database (as explained in Oracle Database Object-Relational Developer's Guide). Although the return type of a pipelined table function might appear to be a PL/SQL type, the database actually converts that PL/SQL type to a corresponding SQL user-defined type.

Topics:

Overview of Table Functions

A table function returns a collection of rows, which can be treated like a relational table in the FROM clause of a query. As input, a table function can take a collection of rows, as either a collection variable or cursor variable.

To improve the performance of a table function, you can:

  • Stream the function results directly to the next process, eliminating intermediate staging between processes

  • Enable the function for parallel execution

    Functions enabled for parallel execution can run concurrently.

  • Pipeline the function results

    A pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows. Response time improves because the entire collection need not be constructed and returned to the server before the query can return a single result row. (Also, the function needs less memory, because the object cache need not materialize the entire collection.)

Note:

A pipelined table function always references the current state of the data. After opening the cursor on the collection, if the data in the collection is changed, then the change is reflected in the cursor. PL/SQL variables are private to a session and are not transactional. Therefore, the notion of read-consistency, well known for its applicability to table data, does not apply to PL/SQL collection variables.

See Also:

Oracle Database Data Cartridge Developer's Guide for information about using pipelined and parallel table functions

Creating Pipelined Table Functions

A pipelined table function must be either a standalone stored function or a package function. For a standalone stored function, specify the PIPELINED option in the CREATE FUNCTION statement (for syntax, see "CREATE FUNCTION Statement"). For a package function, specify the PIPELINED option in both the function declaration and function definition (for syntax, see "Function Declaration and Definition").

The return type of a pipelined table function must be a collection type defined either at schema level or inside a package (therefore, it cannot be an associative array type). The elements of the collection type must be SQL data types, not data types supported only by PL/SQL (such as PLS_INTEGER and BOOLEAN). For information about collection types, see "Collection Types". For information about the SQL data types, see Oracle Database SQL Language Reference.

You can use the SQL data types ANYTYPE, ANYDATA, and ANYDATASET 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 types to create unnamed types, including anonymous collection types. For information about these types, see Oracle Database PL/SQL Packages and Types Reference.

Inside a pipelined table function, use the PIPE ROW statement to return individual elements of the collection to the invoker without returning control to the invoker. With PIPE ROW, the function can process a row, immediately return it to the invoker, and continue to process rows. See "PIPE ROW Statement" for its syntax and semantics.

Note:

  • If a pipelined table function is part of an autonomous transaction, it must COMMIT or ROLLBACK before each PIPE ROW statement, to avoid an error in the invoking subprogram.

  • To improve performance, the PL/SQL run-time system delivers the rows to the invoker in batches.

As in every function, every execution path in a pipelined table function must lead to a RETURN statement, which returns control to the invoker. However, in a pipelined table function, a RETURN statement need not return a value to the invoker. See "RETURN Statement" for its syntax and semantics.

Example 12-28 creates a package that includes a pipelined table function, and then invokes the function in a SELECT statement.

Example 12-28 Creating and Invoking a Pipelined Table Function

CREATE OR REPLACE 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 f1;
END pkg1;
/

Invoke pipelined table function in SELECT statement:

SELECT * FROM TABLE(pkg1.f1(5));

Result:

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
 
5 rows selected.

Pipelined Table Functions for Transformations

A table function that has a cursor variable parameter can serve as a transformation function. It can use the cursor variable to fetch the input rows, perform the transformation on them, and pipe the transformed rows to the invoker.

In Example 12-29, the pipelined table function transforms each row of the employees table to two nested table rows, which it pipes to the query that invokes it.

Example 12-29 Pipelined Table Function for Transformation

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 f_trans;
END refcur_pkg;
/

Invoke f_transc in query:

SELECT * FROM TABLE (
   refcur_pkg.f_trans (
     CURSOR (SELECT * FROM employees WHERE department_id = 60)
   )
);

Result:

VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      Hunold
       103 AHUNOLD                        590.423.4567
       104 Bruce                          Ernst
       104 BERNST                         590.423.4568
       105 David                          Austin
       105 DAUSTIN                        590.423.4569
       106 Valli                          Pataballa
       106 VPATABAL                       590.423.4560
       107 Diana                          Lorentz
       107 DLORENTZ                       590.423.5567

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-subprogram execution. For example, this 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 runs in a different process (or set of processes).

Optimizing Multiple Invocations of Pipelined Table Functions

Multiple invocations of a pipelined table function, either in 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 the database automatically buffers rows for it. If the function is not really deterministic, results are unpredictable.

Fetching from Results of Pipelined Table Functions

PL/SQL cursors and cursor variables 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. Cursor variable assignments based on table functions do not have any special semantics.

However, the SQL optimizer does 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 run 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 running two SQL statements and if 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 cursor variable parameter. For example, this function is declared to accept an argument of the predefined 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 causes the results of a subquery to be passed as a cursor variable parameter.

A predefined weak REF CURSOR type SYS_REFCURSOR is also supported. With SYS_REFCURSOR, you need not 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 cursor variable argument must match the column type, or an error is generated. Weak cursor variable arguments to table functions can only be partitioned using the PARTITION BY ANY clause. You cannot use range or hash partitioning for weak cursor variable arguments.

PL/SQL functions can accept multiple IN cursor variables, as in Example 12-30.

For more information about cursor variables, see "Creating Cursor Variables".

Example 12-30 Function with Two Cursor Variable Parameters

CREATE OR REPLACE 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 g_trans;
END refcur_pkg;
/

Use g_trans table function in query:

SELECT * FROM TABLE (
  refcur_pkg.g_trans (
    CURSOR (SELECT * FROM employees WHERE department_id = 60),
    CURSOR (SELECT * FROM departments WHERE department_id = 60)
  )
);

Result:

VAR_NUM VAR_CHAR1                      VAR_CHAR2
---------- ------------------------------ ------------------------------
       103 Alexander                      Hunold
        60 IT                             1400
       104 Bruce                          Ernst
        60 IT                             1400
       105 David                          Austin
        60 IT                             1400
       106 Valli                          Pataballa
        60 IT                             1400
       107 Diana                          Lorentz
        60 IT                             1400
 
10 rows selected.

You can pass table function return values to other table functions by creating a cursor variable that iterates over the returned data:

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

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

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

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

A table function can compute aggregate results using the cursor variable parameter, as in Example 12-31, which computes a weighted average by iterating over a set of input rows.

Example 12-31 Pipelined Table Function as Aggregate Function

DROP TABLE gradereport;
CREATE TABLE gradereport (
  student VARCHAR2(30),
  subject VARCHAR2(30),
  weight NUMBER,
  grade NUMBER
);

INSERT INTO gradereport (student, subject, weight, grade)
VALUES ('Mark', 'Physics', 4, 4);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Chemistry', 4, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
VALUES ('Mark','Maths', 3, 3);
 
INSERT INTO gradereport (student, subject, weight, grade) 
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
    -- Function accepts cursor variable and loops through all 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 weighted_average;
END pkg_gpa;
/

Use function in query (column_value is a keyword that returns the contents of nested table):

SELECT w.column_value "weighted result" FROM TABLE (
  pkg_gpa.weighted_average (
    CURSOR (SELECT weight, grade FROM gradereport)
  )
) w;

Result is a nested table with single row:

weighted result
---------------
            3.5
 
1 row selected.

Performing DML Statements Inside Pipelined Table Functions

To run DML statements, declare a pipelined table function with the AUTONOMOUS_TRANSACTION pragma, which causes the function to run in a transaction not shared by other processes. For example (where CollType is a previously declared collection type):

CREATE OR REPLACE 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 Statements on Pipelined Table Functions

A pipelined table function cannot be the target table in UPDATE, INSERT, or DELETE statement. For example, these statements raise an exception:

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;

This INSTEAD OF trigger fires 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.

NO_DATA_NEEDED Exception

You must understand the predefined exception NO_DATA_NEEDED in two cases:

  • You include an OTHERS exception handler in a block that includes a PIPE ROW statement

  • Your code that feeds a PIPE ROW statement must be followed by a clean-up procedure

    Typically, the clean-up procedure releases resources that the code no longer needs.

When the invoker of a pipelined table function needs no more rows from the function, the PIPE ROW statement raises NO_DATA_NEEDED. If the pipelined table function does not handle NO_DATA_NEEDED, as in Example 12-32, then the function invocation terminates but the invoking statement does not terminate. If the pipelined table function handles NO_DATA_NEEDED, its exception handler can release the resources that it no longer needs, as in Example 12-33.

In Example 12-32, the pipelined table function pipe_rows does not handle the NO_DATA_NEEDED exception. The SELECT statement that invokes pipe_rows needs only four rows. Therefore, during the fifth invocation of pipe_rows, the PIPE ROW statement raises the exception NO_DATA_NEEDED. The fifth invocation of pipe_rows terminates, but the SELECT statement does not terminate.

Example 12-32 Pipelined Table Function that Does Not Handle NO_DATA_NEEDED

CREATE TYPE t IS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION pipe_rows RETURN t PIPELINED IS
  n NUMBER := 0;
BEGIN
  LOOP
    n := n + 1;
    PIPE ROW (n);
  END LOOP;
END pipe_rows;
/
SELECT COLUMN_VALUE
  FROM TABLE(pipe_rows())
  WHERE ROWNUM < 5
/

Result:

COLUMN_VALUE
------------
           1
           2
           3
           4

4 rows selected.

If the exception-handling part of a block that includes a PIPE ROW statement includes an OTHERS exception handler to handle unexpected exceptions, then it must also include an exception handler for the expected NO_DATA_NEEDED exception. Otherwise, the OTHERS exception handler handles the NO_DATA_NEEDED exception, treating it as an unexpected error. The following exception handler reraises the NO_DATA_NEEDED exception, instead of treating it as a irrecoverable error:

EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    RAISE;
  WHEN OTHERS THEN
    -- (Put error-logging code here)
    RAISE_APPLICATION_ERROR(-20000, 'Fatal error.');
END;

In Example 12-33, assume that the package External_Source contains these public items:

  • Procedure Init, which allocates and initializes the resources that Next_Row needs

  • Function Next_Row, which returns some data from a specific external source and raises the user-defined exception Done (which is also a public item in the package) when the external source has no more data

  • Procedure Clean_Up, which releases the resources that Init allocated

The pipelined table function get_external_source_data pipes rows from the external source by invoking External_Source.Next_Row until either:

  • The external source has no more rows.

    In this case, the External_Source.Next_Row function raises the user-defined exception External_Source.Done.

  • get_external_source_data needs no more rows.

    In this case, the PIPE ROW statement in get_external_source_data raises the NO_DATA_NEEDED exception.

In either case, an exception handler in block b in get_external_source_data invokes External_Source.Clean_Up, which releases the resources that Next_Row was using.

Example 12-33 Pipelined Table Function that Handles NO_DATA_NEEDED

CREATE OR REPLACE FUNCTION get_external_source_data
  RETURN t AUTHID DEFINER PIPELINED IS
BEGIN
  External_Source.Init();           -- Initialize.
  <<b>> BEGIN
    LOOP                            -- Pipe rows from external source.
      PIPE ROW (External_Source.Next_Row());
    END LOOP;
  EXCEPTION
    WHEN External_Source.Done THEN  -- When no more rows are available,
      External_Source.Clean_Up();   --  clean up.
    WHEN NO_DATA_NEEDED THEN        -- When no more rows are needed,
      External_Source.Clean_Up();   --  clean up.
      RAISE NO_DATA_NEEDED;           -- Optional, equivalent to RETURN.
  END b;
END get_external_source_data;
/

Updating Large Tables in Parallel

The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps:

  1. Group sets of rows in the table into smaller chunks.

  2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk.

This technique is recommended whenever you are updating a lot of data. Its advantages are:

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_PARALLEL_EXECUTE package