| Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) Part Number E17126-03 |
|
|
View PDF |
| Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) Part Number E17126-03 |
|
|
View PDF |
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:
Tuning Dynamic SQL with EXECUTE IMMEDIATE Statement and Cursor Variables
Performing Multiple Transformations with Pipelined Table Functions
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:
Oracle Database Reference for information about the PLSQL_OPTIMIZE_LEVEL compilation parameter
Oracle Database Reference for information about the static dictionary view ALL_PLSQL_OBJECT_SETTINGS
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:
Oracle Database Advanced Application Developer's Guide for information about the PL/SQL hierarchical profiler
Oracle Database Reference for information about the PLSQL_OPTIMIZE_LEVEL compilation parameter
Oracle Database Reference for information about the static dictionary view ALL_PLSQL_OBJECT_SETTINGS
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 inliningThe following kinds of PL/SQL code are very likely to benefit from tuning:
Older code that does not take advantage of new PL/SQL language features.
For information about new PL/SQL language features, see "What's New in PL/SQL?".
Before tuning older code, benchmark the current system and profile the older subprograms that your program invokes (see "Profiling and Tracing PL/SQL Programs"). With the many automatic optimizations of the PL/SQL optimizer (described in "PL/SQL Optimizer"), you might see performance improvements before doing any tuning.
Code that does many mathematical calculations.
Functions invoked in queries, which might run millions of times.
Code that spends much time processing database manipulation language (DML) statements or looping through query results.
Code that spends much time processing PL/SQL statements (as opposed to issuing database definition language (DDL) statements that PL/SQL passes directly to SQL).
Topics:
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:
Use appropriate indexes.
For details, see Oracle Database Performance Tuning Guide.
Use query hints to avoid unnecessary full-table scans.
For details, see Oracle Database Performance Tuning Guide.
Collect current statistics on all tables, using the subprograms in the DBMS_STATS package.
For details, see Oracle Database Performance Tuning Guide.
Analyze the execution plans and performance of the SQL statements, using:
EXPLAIN PLAN statement
For details, see Oracle Database Performance Tuning Guide.
SQL Trace facility with TKPROF utility
For details, see Oracle Database Performance Tuning Guide.
Use bulk SQL, a set of PL/SQL features that minimizes the performance overhead of the communication between PL/SQL and SQL.
For details, see "Bulk SQL and Bulk Binding".
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 functionsIf 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
See Also:
"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 onNOCOPY (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;
/
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".
See Also:
"Bulk SQL and Bulk Binding"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:
Oracle Database SQL Language Reference for information about SQL character functions that return character values
Oracle Database SQL Language Reference for information about SQL character functions that return NLS character values
Oracle Database SQL Language Reference for information about SQL character functions that return number values
Example 6-6 for an example of PL/SQL code that uses SQL character function REGEXP_LIKE
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 ...
See Also:
"Short-Circuit Evaluation"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.
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:
When declaring a local integer variable:
If the value of the variable might be NULL, or if the variable needs overflow checking, use the data type PLS_INTEGER, explained in "PLS_INTEGER and BINARY_INTEGER Data Types".
If the value of the variable can never be NULL, and the variable does not need overflow checking, use SIMPLE_INTEGER, explained in "SIMPLE_INTEGER Subtype of PLS_INTEGER".
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 theNUMBER types.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.
PL/SQL binding operations fall into these categories:
Topics:
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:
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.
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.
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.
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 theBULK 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:
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:
TheSELECT 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:
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
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; /
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").
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
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
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
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 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.
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.
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 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.
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:
Start the profiling session.
Run your PL/SQL program long enough to get adequate code coverage.
Flush the collected data to the database.
Stop the profiling session.
After you have collected data with Profiler, you can:
Query the database tables that contain the performance data.
Identify the subprograms and packages that use the most execution time.
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.
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.
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:
(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.
Start the tracing session.
Run your PL/SQL program.
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.
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.
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."
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:
Oracle Database Administrator's Guide for information about configuring a database
Platform-specific configuration documentation for your platform
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.
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.
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".
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".
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 asNATIVE, Oracle recommends that you set PLSQL_CODE_TYPE at the system level.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 asNATIVE, 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.
Ensure that a test PL/SQL unit can be compiled. For example:
ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
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.
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.
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.
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.
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.
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.
Shut down the database and restart in NORMAL mode.
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;
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.
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.
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.
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;
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:
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 functionsA 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:
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.
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
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).
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.
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.
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.
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.
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.
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;
/
The DBMS_PARALLEL_EXECUTE package enables you to incrementally update the data in a large table in parallel, in two high-level steps:
Group sets of rows in the table into smaller chunks.
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:
You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.
You do not lose work that has been done if something fails before the entire operation finishes.
You reduce rollback space consumption.
You improve performance.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_PARALLEL_EXECUTE package
|
 Copyright © 1996, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|