This chapter explains how to write efficient new PL/SQL code and speed up existing PL/SQL code.
Prior to Oracle Database Release 10g, the PL/SQL compiler translated your source code to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizing compiler 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 take too long, you can lower the optimization by setting the compilation parameter
PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, you might see a change in exception action, either an exception that is not raised at all, or one that is raised earlier than expected. Setting
PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.
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, described in INLINE Pragma.
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
Programs that do a lot of mathematical calculations. You will want to investigate the data types
Functions that are called from PL/SQL queries, where the functions might be executed millions of times. You will want to look at all performance features to make the function as efficient as possible, and perhaps a function-based index to precompute the results for each row and save on query time.
Programs that spend a lot of time processing
DELETE statements, or looping through query results. You will want to investigate the
FORALL statement for issuing DML, and the
INTO clauses for queries.
Older code that does not take advantage of recent PL/SQL language features. With the many performance improvements in Oracle Database 10g, any code from earlier releases is a candidate for tuning.
Any program that spends a lot of time doing PL/SQL processing, as opposed to issuing DDL statements like
TABLE that are just passed directly to SQL. You will want to investigate native compilation. Because many built-in database features use PL/SQL, you can apply this tuning feature to an entire database to improve performance in many areas, not just your own code.
Before starting any tuning effort, benchmark the current system and measure how long particular subprograms take. PL/SQL in Oracle Database 10g includes many automatic optimizations, so you might see performance improvements without doing any tuning.
If SQL statements are slowing down your program:
Make sure you have appropriate indexes. There are different kinds of indexes for different situations. Your index strategy might be different depending on the sizes of various tables in a query, the distribution of data in each query, and the columns used in the
Make sure you have up-to-date statistics on all the tables, using the subprograms in the
Analyze the execution plans and performance of the SQL statements, using:
SQL Trace facility with
Rewrite the SQL statements if necessary. For example, query hints can avoid problems such as unnecessary full-table scans.
For more information about these methods, see Oracle Database Performance Tuning Guide.
Some PL/SQL features also help improve the performance of SQL statements:
If you are running SQL statements inside a PL/SQL loop, look at the
FORALL statement as a way to replace loops of
If you are looping through the result set of a query, look at the
BULK COLLECT clause of the
INTO statement as a way to bring the entire result set into memory in a single operation.
If a function is called within a SQL query, you can cache the function value for each row by creating a function-based index on the table in the query. The
INDEX statement might take a while, but queries can be much faster.
If a column is passed to a function within an SQL query, the query cannot use regular indexes on that column, and the function might be called for every row in a (potentially very large) table. Consider nesting the query so that the inner query filters the results to a small number of rows, and the outer query calls the function only a few times as shown in Example 12-1.
BEGIN -- Inefficient, calls function for every row FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP; -- Efficient, only calls function once for each distinct value. FOR item IN ( SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) ) LOOP DBMS_OUTPUT.PUT_LINE(item.col_alias); END LOOP; END; /
If you use
OUT parameters, PL/SQL adds some performance overhead to ensure correct action in case of exceptions (assigning a value to the
OUT parameter, then exiting the subprogram because of an unhandled exception, so that the
OUT parameter keeps its original value).
If your program does not depend on
OUT parameters keeping their values in such situations, you can add the
NOCOPY keyword to the parameter declarations, so the parameters are declared
This technique can give significant speedup if you are passing back large amounts of data in
OUT parameters, such as collections, big
VARCHAR2 values, or LOBs.
This technique also applies to member methods of object types. If these methods modify attributes of the object type, all the attributes are copied when the method ends. To avoid this overhead, you can explicitly declare the first parameter of the member method as
NOCOPY, instead of relying on PL/SQL's implicit declaration
OUT. For information about design considerations for object methods, see Oracle Database Object-Relational Developer's Guide.
To issue a series of DML statements, replace loop constructs with
To loop through a result set and store the values, use the
COLLECT clause on the query to bring the query results into memory in one operation.
If you must loop through a result set more than once, or issue other queries as you loop through a result set, you can probably enhance the original query to give you exactly the results you want. Some query operators to explore include
You can also nest one query inside another (known as a subselect) to do the filtering and sorting in multiple stages. For example, instead of calling a PL/SQL function in the inner
WHERE clause (which might call the function once for each row of the table), you can filter the result set to a small set of rows in the inner query, and call the function in the outer query.
PL/SQL provides many highly optimized string functions such as
LTRIM. The built-in functions use low-level code that is more efficient than regular PL/SQL.
If you use PL/SQL string functions to search for regular expressions, consider using the built-in regular expression functions, such as
You can search for regular expressions using the SQL operator
REGEXP_LIKE. See Example 6-10.
You can test or manipulate strings using the built-in functions
Regular expression features use characters like '.', '*', '^', and '$' that you might be familiar with from Linux, UNIX, or PERL programming. For multilanguage programming, there are also extensions such as '[:lower:]' to match a lowercase letter, instead of '[a-z]' which does not match lowercase accented letters.
PL/SQL stops evaluating a logical expression as soon as the result can be determined. This functionality is known as short-circuit evaluation. See Short-Circuit Evaluation.
When evaluating multiple conditions separated by
OR, put the least expensive ones first. For example, check the values of PL/SQL variables before testing function return values, because PL/SQL might be able to skip calling the functions.
At run time, PL/SQL converts between different 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.
Minimizing conversions might mean changing the types of your variables, or even working backward and designing your tables with different data types. Or, you might convert data once, such as from an
INTEGER column to a
PLS_INTEGER variable, and use the PL/SQL type consistently after that. The conversion from
PLS_INTEGER data type might improve performance, because of the use of more efficient hardware arithmetic. See Use PLS_INTEGER or SIMPLE_INTEGER for Integer Arithmetic.
If the value of the variable might be
NULL, or if the variable needs overflow checking, use the data type
If the value of the variable will never be
NULL, and the variable does not need overflow checking, use the data type
PLS_INTEGER values use less storage space than
NUMBER values, and
PLS_INTEGER operations use hardware arithmetic. For more information, see PLS_INTEGER and BINARY_INTEGER Data Types.
SIMPLE_INTEGER is a predefined subtype of
PLS_INTEGER. It has the same range as
PLS_INTEGER and has a
NULL constraint. It differs significantly from
PLS_INTEGER in its overflow semantics—for details, see Overflow Semantics.
The data type
NUMBER and its subtypes are represented in a special internal format, designed for portability and arbitrary scale and precision, not performance. Even the subtype
INTEGER is treated as a floating-point number with nothing after the decimal point. Operations on
INTEGER variables require calls to library routines.
Avoid constrained subtypes such as
SIGNTYPE in performance-critical code. Variables of these types require extra checking at run time, each time they are used in a calculation.
The data type
NUMBER and its subtypes are represented in a special internal format, designed for portability and arbitrary scale and precision, not performance. Operations on
INTEGER variables require calls to library routines.
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.
If the value of the variable will never be
NULL, use the subtype
BINARY_FLOAT instead of the base type
BINARY_DOUBLE. Each subtype has the same range as its base type and has a
NULL constraint. Without the overhead of checking for nullness,
SIMPLE_DOUBLE provide significantly better performance than
PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on
SIMPLE_DOUBLE values are done directly in the hardware. When
PLSQL_CODE_TYPE='INTERPRETED', the performance improvement is smaller.
These types do not always represent fractional values precisely, and handle rounding differently than the
NUMBER types. These types are less suitable for financial code where accuracy is critical.
You might need to allocate large
VARCHAR2 variables when you are not sure how big an expression result will be. You can conserve memory by declaring
VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the
VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.
When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool. Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package ages out of memory, and you reference it again, it is reloaded.
You can improve performance by sizing the shared memory pool correctly. Make it large enough to hold all frequently used packages, but not so large that memory is wasted.
You can pin frequently accessed packages in the shared memory pool, using the supplied package
DBMS_SHARED_POOL. When a package is pinned, it does not age out; it remains in memory no matter how full the pool gets or how frequently you access the package.
For more information about the
DBMS_SHARED_POOL package, see Oracle Database PL/SQL Packages and Types Reference.
The PL/SQL compiler issues warnings about things that do not make a program incorrect, but might lead to poor performance. If you receive such a warning, and the performance of this code is important, follow the suggestions in the warning and make the code more efficient.
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.
||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.
||Traces the order in which subprograms execute.
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||
||Reports the dynamic execution program profile of your PL/SQL program, organized by subprogram calls. 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).
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 executing 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, due to 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.
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 Using the Profiler API: Package DBMS_PROFILER).
For detailed information about the
DBMS_TRACE subprograms, see Oracle Database PL/SQL Packages and Types Reference.
PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the results to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL features that are known collectively as bulk SQL.
FORALL statement sends
DELETE statements in batches, rather than one at a time. The
COLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, bulk SQL can improve performance considerably.
|Binding Category||When This Binding Occurs|
Bulk SQL uses PL/SQL collections to pass large amounts of data back and forth in single operations. This process is called bulk binding. If the collection has n elements, bulk binding uses a single operation to perform the equivalent of n
DELETE statements. A query that uses bulk binding can return any number of rows, without requiring a
FETCH statement for each one.
To speed up
DELETE statements, enclose the SQL statement within a PL/SQL
FORALL statement instead of a
To speed up
INTO statements, include the
FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose
FOR loop. For full syntax and restrictions, see FORALL Statement.
The SQL statement can reference more than one collection, but
FORALL only improves performance where the index value is used as a subscript.
Usually, the bounds specify a range of consecutive index numbers. If the index numbers are not consecutive, such as after you delete collection elements, you can use the
OF clause to iterate over just those index values that really exist.
OF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound.
OF clause refers to a collection that is indexed by
PLS_INTEGER and whose elements are of type
FORALL statement iterates over the index values specified by the elements of this collection.
FORALL statement in Example 12-2 sends all three
DELETE statements to the SQL engine at once.
CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS VARRAY(20) OF NUMBER; depts NumList := NumList(10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM employees_temp WHERE department_id = depts(i); COMMIT; END; /
Example 12-3 loads some data into PL/SQL collections. Then it inserts the collection elements into a database table twice: first using a
FOR loop, then using a
FORALL statement. The
FORALL version is much faster.
CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15)); CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15)); DECLARE TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER; TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER; pnums NumTab; pnames NameTab; iterations CONSTANT PLS_INTEGER := 500; t1 INTEGER; t2 INTEGER; t3 INTEGER; BEGIN FOR j IN 1..iterations LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := DBMS_UTILITY.get_time; FOR i IN 1..iterations LOOP -- use FOR loop INSERT INTO parts1 VALUES (pnums(i), pnames(i)); END LOOP; t2 := DBMS_UTILITY.get_time; FORALL i IN 1..iterations -- use FORALL statement INSERT INTO parts2 VALUES (pnums(i), pnames(i)); t3 := DBMS_UTILITY.get_time; DBMS_OUTPUT.PUT_LINE('Execution Time (secs)'); DBMS_OUTPUT.PUT_LINE('---------------------'); DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((t2 - t1)/100)); DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100)); COMMIT; END; /
Executing this block shows that the loop using
FORALL is much faster.
The bounds of the
FORALL loop can apply to part of a collection, not necessarily all the elements, as shown in Example 12-4.
CREATE TABLE employees_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS VARRAY(10) OF NUMBER; depts NumList := NumList(5,10,20,30,50,55,57,60,70,75); BEGIN FORALL j IN 4..7 -- use only part of varray DELETE FROM employees_temp WHERE department_id = depts(j); COMMIT; END; /
You might need to delete some elements from a collection before using the collection in a
FORALL statement. The
OF clause processes sparse collections by iterating through only the remaining elements.
You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the
OF clause lets you set up simple collections whose elements serve as pointers to elements in the original collection.
Example 12-5 creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that does not work in a default
FORALL statement. The program uses a
FORALL statement with the
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
-- Create empty tables to hold order details CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2)); CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0; CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0; DECLARE -- Collections for set of customer names & 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; BEGIN setup_data(); DBMS_OUTPUT.PUT_LINE ('--- Original order data ---'); FOR i IN 1..cust_tab.LAST LOOP DBMS_OUTPUT.PUT_LINE ('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)); END LOOP; -- Delete invalid orders (where amount is null or 0). FOR i IN 1..cust_tab.LAST LOOP IF amount_tab(i) is null or amount_tab(i) = 0 THEN cust_tab.delete(i); amount_tab.delete(i); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE ('--- Data with invalid orders deleted ---'); FOR i IN 1..cust_tab.LAST LOOP IF cust_tab.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)); END IF; END LOOP; -- Because subscripts of collections are not consecutive, -- use FORALL...INDICES OF to iterate through actual subscripts, -- rather than 1..COUNT FORALL i IN INDICES OF cust_tab INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i)); -- Now process the order data differently -- Extract 2 subsets and store each subset in a different table -- Initialize the CUST_TAB and AMOUNT_TAB collections again. setup_data(); FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN -- Add a new element to this collection rejected_order_tab.EXTEND; -- Record the subscript from the original collection rejected_order_tab(rejected_order_tab.LAST) := i; END IF; IF amount_tab(i) > 2000 THEN -- Add a new element to this collection big_order_tab.EXTEND; -- Record the subscript from the original collection big_order_tab(big_order_tab.LAST) := i; END IF; END LOOP; -- Now it's easy to run one DML statement -- on one subset of elements, -- and another DML statement on a different subset. FORALL i IN VALUES OF rejected_order_tab INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i)); FORALL i IN VALUES OF big_order_tab INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i)); COMMIT; END; / -- Verify that the correct order details were stored SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders; SELECT cust_name "Customer", amount "Big order amount" FROM big_orders; SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders;
FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as shown in Example 12-6. 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.
CREATE TABLE emp_temp (deptno NUMBER(2), job VARCHAR2(18)); DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); BEGIN INSERT INTO emp_temp VALUES(10, 'Clerk'); -- Lengthening this job title causes an exception INSERT INTO emp_temp VALUES(20, 'Bookkeeper'); INSERT INTO emp_temp VALUES(30, 'Analyst'); COMMIT; FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements. UPDATE emp_temp SET job = job || ' (Senior)' WHERE deptno = depts(j); -- raises a "value too large" exception EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Problem in the FORALL statement.'); COMMIT; -- Commit results of successful updates. END; /
The cursor attributes
SQL%ROWCOUNT, return useful information about the most recently executed DML statement. For additional description of cursor attributes, see SQL Cursors (Implicit).
SQL cursor has one composite attribute,
%BULK_ROWCOUNT, for use with the
FORALL statement. This attribute works like an associative array:
SQL%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an
DELETE statement, as in Example 12-7.
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; /
FORALL statement and
%BULK_ROWCOUNT attribute use the same subscripts. For example, if
FORALL uses the range 5..10, so does
%BULK_ROWCOUNT. If the
FORALL statement uses the
INDICES OF clause to process a sparse collection,
%BULK_ROWCOUNT has corresponding sparse subscripts. If the
FORALL statement uses the
VALUES OF clause to process a subset of elements,
%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
%BULK_ROWCOUNT represent the sum of all rows affected by the DML statement using that subscript.
%BULK_ROWCOUNT is usually equal to 1 for inserts, because a typical insert operation affects only a single row. For the
%BULK_ROWCOUNT might be greater than 1. For example, the
FORALL statement in Example 12-8 inserts an arbitrary number of rows for each iteration. After each iteration,
%BULK_ROWCOUNT returns the number of items inserted.
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id FROM employees WHERE 1 = 0; DECLARE TYPE dept_tab IS TABLE OF departments.department_id%TYPE; deptnums dept_tab; BEGIN SELECT department_id BULK COLLECT INTO deptnums FROM departments; FORALL i IN 1..deptnums.COUNT INSERT INTO emp_by_dept SELECT employee_id, department_id FROM employees WHERE department_id = deptnums(i); FOR i IN 1..deptnums.COUNT LOOP -- Count how many rows were inserted for each department; that is, -- how many employees are in each department. DBMS_OUTPUT.PUT_LINE('Dept '||deptnums(i)||': inserted '|| SQL%BULK_ROWCOUNT(i)||' records'); END LOOP; DBMS_OUTPUT.PUT_LINE('Total records inserted: ' || SQL%ROWCOUNT); END; /
You can also use the scalar attributes
%ROWCOUNT after running a
FORALL statement. For example,
%ROWCOUNT returns the total number of rows processed by all executions of the SQL statement.
%NOTFOUND refer only to the last execution of the SQL statement. You can use
%BULK_ROWCOUNT to deduce their values for individual executions. For example, when
%BULK_ROWCOUNT(i) is zero,
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
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-9 shows how you can perform a number of DML operations, without stopping if some operations encounter errors. In the example,
EXCEPTION_INIT is used to associate the
DML_ERRORS exception with the predefined error ORA-24381. ORA-24381 is raised if any exceptions are caught and saved after a bulk operation.
All exceptions raised during the execution are saved in the cursor attribute
%BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:
The values stored by
%BULK_EXCEPTIONS always refer to the most recently executed
FORALL statement. The number of exceptions is saved in
COUNT. Its subscripts range from 1 to
The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using
SQLERRM as shown in Example 12-9.
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
OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to
ERROR_INDEX. If you use the
OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches
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
EXCEPTIONS, execution of the
FORALL statement stops when an exception is raised. In that case,
COUNT returns 1, and
SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution,
COUNT returns 0.
-- Temporary table for this example: CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE TYPE empid_tab IS TABLE OF employees.employee_id%TYPE; emp_sr empid_tab; -- 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, -- a single exception is raised 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; / DROP TABLE emp_temp;
The output from the example is similar to:
Number of statements that failed: 2 Error #1 occurred during iteration #7 Error message is ORA-12899: value too large for column Error #2 occurred during iteration #13 Error message is ORA-12899: value too large for column
In Example 12-9, PL/SQL raises predefined exceptions because updated values were too large to insert into the
job_id column. After the
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
ERROR_CODE was negated and then passed to the error-reporting function
SQLERRM, which expects a negative number.
COLLECT clause with a query is a very efficient way to retrieve the result set. Instead of looping through each row, you store the results in one or more collections, in a single operation. You can use the
COLLECT clause in the
INTO statements, and in the
COLLECT clause, all the variables in the
INTO list must be collections. The table columns can hold scalar or composite values, including object types.
Example 12-10 loads two entire database columns into nested tables.
DECLARE TYPE NumTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; enums NumTab; -- No need to initialize collections names NameTab; -- Values will be filled by SELECT INTO PROCEDURE print_results IS BEGIN IF enums.COUNT = 0 THEN DBMS_OUTPUT.PUT_LINE('No results!'); ELSE DBMS_OUTPUT.PUT_LINE('Results:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE (' Employee #' || enums(i) || ': ' names(i)); END LOOP; END IF; END; BEGIN -- Retrieve data for employees with Ids greater than 1000 SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000; -- Data was brought into memory by BULK COLLECT -- No need to FETCH each row from result set print_results(); -- Retrieve approximately 20% of all rows SELECT employee_id, last_name BULK COLLECT INTO enums, names FROM employees SAMPLE (20); print_results(); END; /
The collections are initialized automatically. Nested tables and associative arrays are extended to hold as many elements as needed. If you use varrays, all the return values must fit in the varray's declared size. Elements are inserted starting at index 1, overwriting any existing elements.
Because the processing of the
INTO clause is similar to a
FETCH loop, it does not raise a
NO_DATA_FOUND exception if no rows match the query. You must check whether the resulting nested table or varray is null, or if the resulting associative array has no elements, as shown in Example 12-11.
To prevent the resulting collections from expanding without limit, you can use the
LIMIT clause to or pseudocolumn
ROWNUM to limit the number of rows processed. You can also use the
SAMPLE clause to retrieve a random sample of rows.
DECLARE TYPE SalList IS TABLE OF employees.salary%TYPE; sals SalList; BEGIN -- Limit 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; /
You can process very large result sets by fetching a specified number of rows at a time from a cursor, as shown in the following sections.
You can fetch from a cursor into one or more collections as shown in Example 12-12.
DECLARE TYPE NameList IS TABLE OF employees.last_name%TYPE; TYPE SalList IS TABLE OF employees.salary%TYPE; CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000; names NameList; sals SalList; TYPE RecList IS TABLE OF c1%ROWTYPE; recs RecList; v_limit PLS_INTEGER := 10; PROCEDURE print_results IS BEGIN -- 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('Results: '); FOR i IN names.FIRST .. names.LAST LOOP DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i)); END LOOP; END IF; END; BEGIN DBMS_OUTPUT.PUT_LINE ('--- Processing all results at once ---'); OPEN c1; FETCH c1 BULK COLLECT INTO names, sals; CLOSE c1; print_results(); DBMS_OUTPUT.PUT_LINE ('--- Processing ' || v_limit || ' rows at a time ---'); OPEN c1; LOOP FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit; EXIT WHEN names.COUNT = 0; print_results(); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT_LINE ('--- Fetching records rather than columns ---'); OPEN c1; FETCH c1 BULK COLLECT INTO recs; FOR i IN recs.FIRST .. recs.LAST LOOP -- Now all columns from result set come from one record DBMS_OUTPUT.PUT_LINE(' Employee ' || recs(i).last_name || ': $' || recs(i).salary); END LOOP; END; /
Example 12-13 shows how you can fetch from a cursor into a collection of records.
DECLARE TYPE DeptRecTab IS TABLE OF departments%ROWTYPE; dept_recs DeptRecTab; CURSOR c1 IS SELECT department_id, department_name, manager_id, location_id FROM departments WHERE department_id > 70; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO dept_recs; END; /
LIMIT clause, allowed only in bulk
FETCH statements, limits the number of rows fetched from the database. In Example 12-14, with each iteration of the loop, the
FETCH statement fetches ten rows (or fewer) into index-by table
empids. The previous values are overwritten. Note the use of
COUNT to determine when to exit the loop.
DECLARE TYPE numtab IS TABLE OF NUMBER INDEX BY PLS_INTEGER; CURSOR c1 IS SELECT employee_id FROM employees WHERE department_id = 80; empids numtab; rows PLS_INTEGER := 10; BEGIN OPEN c1; -- Fetch 10 rows or less in each iteration LOOP FETCH c1 BULK COLLECT INTO empids LIMIT rows; EXIT WHEN empids.COUNT = 0; -- EXIT WHEN c1%NOTFOUND; -- incorrect, can omit some data DBMS_OUTPUT.PUT_LINE ('------- Results from Each Bulk Fetch --------'); FOR i IN 1..empids.COUNT LOOP DBMS_OUTPUT.PUT_LINE( 'Employee Id: ' || empids(i)); END LOOP; END LOOP; CLOSE c1; END; /
CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS TABLE OF employees.employee_id%TYPE; enums NumList; TYPE NameList IS TABLE OF employees.last_name%TYPE; names NameList; BEGIN DELETE FROM emp_temp WHERE department_id = 30 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; /
In Example 12-16, the
employee_id value of each deleted row is stored in the collection
e_ids. The collection
depts has 3 elements, so the
FORALL statement iterates 3 times. If each
DELETE issued by the
FORALL statement deletes 5 rows, then the collection
e_ids, which stores values from the deleted rows, has 15 elements when the statement completes.
CREATE TABLE emp_temp AS SELECT * FROM employees; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10,20,30); TYPE enum_t IS TABLE OF employees.employee_id%TYPE; TYPE dept_t IS TABLE OF employees.department_id%TYPE; e_ids enum_t; d_ids dept_t; BEGIN FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp_temp WHERE department_id = depts(j) RETURNING employee_id, department_id BULK COLLECT INTO e_ids, d_ids; DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN e_ids.FIRST .. e_ids.LAST LOOP DBMS_OUTPUT.PUT_LINE('Employee #' || e_ids(i) || ' from dept #' || d_ids(i)); END LOOP; END; /
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
You cannot use the
COLLECT statement in a
Host arrays are declared in a host environment such as an OCI or a Pro*C program and must be prefixed with a colon to distinguish them from PL/SQL collections. In the following example, an input host array is used in a
DELETE statement. At run time, the anonymous PL/SQL block is sent to the database server for execution.
DECLARE BEGIN -- Assume that values were assigned to host array -- and host variables in host environment FORALL i IN :lower..:upper DELETE FROM employees WHERE department_id = :depts(i); COMMIT; END;
In a statement of the form
SELECT column BULK COLLECT INTO collection FROM table ...
collection are analogous to
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.
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('Before SELECT statement'); 5 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 6 7 FOR j IN 1..numbers1.COUNT() LOOP 8 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 9 END LOOP; 10 11 --Self-selecting BULK COLLECT INTO clause: 12 13 SELECT a.COLUMN_VALUE 14 BULK COLLECT INTO numbers1 15 FROM TABLE(numbers1) a 16 WHERE a.COLUMN_VALUE > p.i 17 ORDER BY a.COLUMN_VALUE; 18 19 DBMS_OUTPUT.PUT_LINE('After SELECT statement'); 20 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 21 END p; 22 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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. SQL> BEGIN 2 p(10); 3 END; 4 / 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 SQL>
Example 12-18 Workaround for Example 12-17 Using a Cursor
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 4 CURSOR c IS 5 SELECT a.COLUMN_VALUE 6 FROM TABLE(numbers1) a 7 WHERE a.COLUMN_VALUE > p.i 8 ORDER BY a.COLUMN_VALUE; 9 BEGIN 10 DBMS_OUTPUT.PUT_LINE('Before FETCH statement'); 11 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 12 13 FOR j IN 1..numbers1.COUNT() LOOP 14 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 15 END LOOP; 16 17 OPEN c; 18 FETCH c BULK COLLECT INTO numbers1; 19 CLOSE c; 20 21 DBMS_OUTPUT.PUT_LINE('After FETCH statement'); 22 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 23 24 IF numbers1.COUNT() > 0 THEN 25 FOR j IN 1..numbers1.COUNT() LOOP 26 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 27 END LOOP; 28 END IF; 29 END p; 30 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL> BEGIN 2 p(10); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL> DROP TYPE numbers_type; Type dropped. SQL> DROP PROCEDURE p; Procedure dropped. SQL>
Example 12-19 Workaround for Example 12-17 Using a Second Collection
SQL> CREATE OR REPLACE TYPE numbers_type IS 2 TABLE OF INTEGER 3 / Type created. SQL> CREATE OR REPLACE PROCEDURE p (i IN INTEGER) IS 2 numbers1 numbers_type := numbers_type(1,2,3,4,5); 3 numbers2 numbers_type := numbers_type(0,0,0,0,0); 4 5 BEGIN 6 DBMS_OUTPUT.PUT_LINE('Before SELECT statement'); 7 8 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 9 10 FOR j IN 1..numbers1.COUNT() LOOP 11 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 12 END LOOP; 13 14 DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); 15 16 FOR j IN 1..numbers2.COUNT() LOOP 17 DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); 18 END LOOP; 19 20 SELECT a.COLUMN_VALUE 21 BULK COLLECT INTO numbers2 -- numbers2 appears here 22 FROM TABLE(numbers1) a -- numbers1 appears here 23 WHERE a.COLUMN_VALUE > p.i 24 ORDER BY a.COLUMN_VALUE; 25 26 DBMS_OUTPUT.PUT_LINE('After SELECT statement'); 27 DBMS_OUTPUT.PUT_LINE('numbers1.COUNT() = ' || numbers1.COUNT()); 28 29 IF numbers1.COUNT() > 0 THEN 30 FOR j IN 1..numbers1.COUNT() LOOP 31 DBMS_OUTPUT.PUT_LINE('numbers1(' || j || ') = ' || numbers1(j)); 32 END LOOP; 33 END IF; 34 35 DBMS_OUTPUT.PUT_LINE('numbers2.COUNT() = ' || numbers2.COUNT()); 36 37 IF numbers2.COUNT() > 0 THEN 38 FOR j IN 1..numbers2.COUNT() LOOP 39 DBMS_OUTPUT.PUT_LINE('numbers2(' || j || ') = ' || numbers2(j)); 40 END LOOP; 41 END IF; 42 END p; 43 / Procedure created. SQL> BEGIN 2 p(2); 3 END; 4 / 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. SQL> BEGIN 2 p(10); 3 END; 4 / 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 PL/SQL procedure successfully completed. SQL>
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.
PLS_INTEGER data type is a PL/SQL-only data type that is more efficient than the SQL data types
INTEGER for integer arithmetic. You can use
PLS_INTEGER to write pure PL/SQL code for integer arithmetic, or convert
INTEGER values to
PLS_INTEGER for manipulation by PL/SQL.
Within a package, you can write overloaded versions of subprograms that accept different numeric parameters. The math routines can be optimized for each kind of parameter (
PLS_INTEGER), avoiding unnecessary conversions.
The built-in math functions such as
COS, and so on already have fast overloaded versions that accept
BINARY_DOUBLE parameters. You can speed up math-intensive code by passing variables of these types to such functions, and by calling the
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 execute dynamic SQL statements, you had to use the supplied package
DBMS_SQL. Now, within PL/SQL, you can execute any kind of dynamic SQL statement using an interface called native dynamic SQL. The main PL/SQL features involved are the
IMMEDIATE statement and cursor variables (also known as
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, "Using Dynamic SQL."
OUT parameters are passed by value. The values of any
OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.
When the parameters represent large data structures such as collections, records, and instances of object types, this copying slows down execution and uses up memory. In particular, this overhead applies to each call to an object method: temporary copies are made of all the attributes, so that any changes made by the method are only applied if the method exits normally.
To avoid this overhead, you can specify the
NOCOPY hint, which allows the PL/SQL compiler to pass
OUT parameters by reference. If the subprogram exits normally, the action is the same as normal. If the subprogram exits early with an exception, the values of
OUT parameters (or object attributes) might still change. To use this technique, ensure that the subprogram handles all exceptions.
The following example asks the compiler to pass
v_staff by reference, to avoid copying the varray on entry to and exit from the subprogram:
DECLARE TYPE Staff IS VARRAY(200) OF Employee; PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...
Example 12-20 loads 25,000 records into a local nested table, which is passed to two local procedures that do nothing. A call to the subprogram that uses
NOCOPY takes much less time.
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; -- Copy element 1 into 2..50000 emp_tab.EXTEND(49999, 1); get_time(t1); -- Pass IN OUT parameter do_nothing1(emp_tab); get_time(t2); -- Pass IN OUT NOCOPY parameter do_nothing2(emp_tab); 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; /
The use of
NOCOPY increases the likelihood of parameter aliasing. For more information, see Understanding PL/SQL Subprogram Parameter Aliasing.
NOCOPY is a hint, not a directive. In the following cases, the PL/SQL compiler ignores the
NOCOPY hint and uses the by-value parameter-passing method; no error is generated:
The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.
The actual parameter is constrained, such as by scale or
NULL. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types.
The actual and formal parameters are records, one or both records were declared using
%TYPE, and constraints on corresponding fields in the records differ.
The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor
FOR loop, and constraints on corresponding fields in the records differ.
Passing the actual parameter requires an implicit data type conversion.
The subprogram is called through a database link or as an external subprogram.
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 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.
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 will speed up both your own code and calls to all of the built-in PL/SQL packages.
* 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, consider the following in determining whether to compile a PL/SQL unit for native mode:
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 executing 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.
With PL/SQL native compilation, the PL/SQL statements in a PL/SQL unit are compiled into native code and stored in the SYSTEM tablespace. 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 only calls SQL statements might not run faster when natively compiled, but it will 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 exactly the same.
The first time a natively compiled PL/SQL unit is executed, it is fetched from the SYSTEM tablespace into shared memory. Regardless of how many sessions call 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 call each other.
PLSQL native compilation works transparently in a Oracle Real Application Clusters (Oracle RAC) environment.
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 an new database for PL/SQL native compilation by setting the compilation parameter
NATIVE. The performance benefits apply to all the built-in PL/SQL packages, which are used for many database operations.
Note:If you compile the whole database as
NATIVE, Oracle recommends that you set
PLSQL_CODE_TYPEat the system level.
If you have DBA privileges, you can recompile all PL/SQL modules in an existing database to
INTERPRETED, using the
sql scripts respectively during the process described in this section. Before making the conversion, review Determining Whether to Use PL/SQL Native Compilation.
Note:If you compile the whole database as
NATIVE, Oracle recommends that you set
PLSQL_CODE_TYPEat the system level.
During the conversion to native compilation,
TYPE specifications are not recompiled by
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
sql script to exclude package specs from recompilation to
NATIVE, saving time in the conversion process.
When converting to interpreted compilation, the
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.
PLSQL_CODE_TYPE compilation parameter to
INTERPRETED rather than
sql for the
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 the Oracle Database Administrator's Guide.
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 compilation type that you want.
Start up the database in upgrade mode, using the
UPGRADE option. For information about SQL*Plus
STARTUP, see the SQL*Plus User's Guide and Reference.
REM To save the output of the query to a file: SPOOL pre_update_invalid.log SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID'; REM To stop spooling the output: SPOOL OFF
If any Oracle supplied units are invalid, try to validate them 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.
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
plsql_code_type are special internal objects and can be ignored.
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
Before you run the
sql script, Oracle recommends that no other sessions are connected to avoid possible problems. You can ensure this with the following statement:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
sql script as the user
SYS. This script recompiles all the PL/SQL modules using a default degree of parellelism. See the comments in the script for information about setting the degree explicitly.
If for any reason the script is abnormally terminated, rerun the
sql script to recompile any remaining invalid PL/SQL modules.
After the compilation completes successfully, verify that there are no new invalid PL/SQL units using the query in step 5. You can spool the output of the query to the
log file and compare the contents with the
log file, if it was created previously.
Reexecute the query in step 6. If recompiling with
sql, confirm that all PL/SQL units, except
TYPE specifications and package specifications if excluded, are
NATIVE. If recompiling with
sql, confirm that all PL/SQL units are
Disable the restricted session mode for the database, then start the services that you previously shut down. To disable restricted session mode, use the following statement:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
This section explains how to chain together special kinds of functions known as pipelined table functions. These functions are used in situations such as data warehousing to apply multiple transformations to data.
Note:A pipelined table function cannot be run 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 within 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.
Pipelined table functions let you use PL/SQL to program a row source. You invoke the table function as the operand of the table operator in the
FROM list of a SQL
SELECT statement. It is also possible to invoke a table function as a
SELECT list item; here, you do not use the table operator.
A table function can take a collection of rows as input. An input collection parameter can be either a collection type (such as a
VARRAY or a PL/SQL table) or a
Execution of a table function can be parallelized, and returned rows can be streamed directly to the next process without intermediate staging. Rows from a collection returned by a table function can also be pipelined, that is, iteratively returned as they are produced, instead of in a batch after all processing of the table function's input is completed.
Note:When rows from a collection returned by a table function are pipelined, the pipelined 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.
Streaming, pipelining, and parallel execution of table functions can improve performance:
By enabling multithreaded, concurrent execution of table functions
By eliminating intermediate staging between processes
By improving query response time: With non-pipelined table functions, the entire collection returned by a table function must be constructed and returned to the server before the query can return a single result row. Pipelining enables rows to be returned iteratively, as they are produced. This also reduces the memory that a table function requires, as the object cache need not materialize the entire collection.
By iteratively providing result rows from the collection returned by a table function as the rows are produced instead of waiting until the entire collection is staged in tables or memory and then returning the entire collection.
You declare a pipelined table function by specifying the
PIPELINED keyword. Pipelined functions can be defined at the schema level with
FUNCTION or in a package. The
PIPELINED keyword indicates that the function returns rows iteratively. The return type of the pipelined table function must be a supported collection type, such as a nested table or a varray. This collection type can be declared at the schema level or inside a package. Inside the function, you return individual elements of the collection type. The elements of the collection type must be supported SQL data types, such as
VARCHAR2. PL/SQL data types, such as
BOOLEAN, are not supported as collection elements in a pipelined function.
Example 12-21 shows how to assign the result of a pipelined table function to a PL/SQL collection variable and use the function in a
CREATE PACKAGE pkg1 AS TYPE numset_t IS TABLE OF NUMBER; FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED; END pkg1; / CREATE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x) FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS BEGIN FOR i IN 1..x LOOP PIPE ROW(i); END LOOP; RETURN; END; END pkg1; / -- pipelined function is used in FROM clause of SELECT statement SELECT * FROM TABLE(pkg1.f1(5));
A pipelined table function can accept any argument that regular functions accept. A table function that accepts a
CURSOR as an argument can serve as a transformation function. That is, it can use the
CURSOR to fetch the input rows, perform some transformation on them, and then pipeline the results out.
In Example 12-22, the
f_trans function converts a row of the
employees table into two rows.
-- Define the ref cursor types and function CREATE OR REPLACE PACKAGE refcur_pkg IS TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE; TYPE outrec_typ IS RECORD ( var_num NUMBER(6), var_char1 VARCHAR2(30), var_char2 VARCHAR2(30)); TYPE outrecset IS TABLE OF outrec_typ; FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED; END refcur_pkg; / CREATE OR REPLACE PACKAGE BODY refcur_pkg IS FUNCTION f_trans(p refcur_t) RETURN outrecset PIPELINED IS out_rec outrec_typ; in_rec p%ROWTYPE; BEGIN LOOP FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; -- first row out_rec.var_num := in_rec.employee_id; out_rec.var_char1 := in_rec.first_name; out_rec.var_char2 := in_rec.last_name; PIPE ROW(out_rec); -- second row out_rec.var_char1 := in_rec.email; out_rec.var_char2 := in_rec.phone_number; PIPE ROW(out_rec); END LOOP; CLOSE p; RETURN; END; END refcur_pkg; / -- SELECT query using the f_transc table function SELECT * FROM TABLE( refcur_pkg.f_trans(CURSOR (SELECT * FROM employees WHERE department_id = 60)));
In the preceding query, the pipelined table function
f_trans fetches rows from the
employees ..., performs the transformation, and pipelines the results back to the user as a table. The function produces two output rows (collection elements) for each input row.
CURSOR subquery is passed from SQL to a
CURSOR function argument as in Example 12-22, the referenced cursor is already open when the function begins executing.
In PL/SQL, the
ROW statement causes a pipelined table function to pipe a row and continue processing. The statement enables a PL/SQL table function to return rows as soon as they are produced. For performance, the PL/SQL run-time system provides the rows to the consumer in batches.
In Example 12-22, the
ROW(out_rec) statement pipelines data out of the PL/SQL table function.
out_rec is a record, and its type matches the type of an element of the output collection.
ROW statement may be used only in the body of pipelined table functions; an exception is raised if it is used anywhere else. The
ROW statement can be omitted for a pipelined table function that returns no rows.
A pipelined table function may have a
RETURN statement that does not return a value. The
RETURN statement transfers the control back to the consumer and ensures that the next fetch gets a
Because table functions pass control back and forth to a calling routine as rows are produced, there is a restriction on combining table functions and
AUTONOMOUS_TRANSACTION. If a table function is part of an autonomous transaction, it must
ROLLBACK before each
ROW statement, to avoid an error in the calling subprogram.
The database has three special SQL data types that enable you to dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type, including object and collection types. You can also use these three special types to create unnamed types, including anonymous collection types. The types are
ANYDATA type can be useful in some situations as a return value from table functions.
See Also:Oracle Database PL/SQL Packages and Types Reference for information about the interfaces to the
ANYDATASETtypes and about the
DBMS_TYPESpackage for use with these types
With serial execution, results are pipelined from one PL/SQL table function to another using an approach similar to co-routine execution. For example, the following statement pipelines results from function
g to function
SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g()))));
Parallel execution works similarly except that each function executes in a different process (or set of processes).
Multiple calls to a pipelined table function, either within the same query or in separate queries result in multiple executions of the underlying implementation. By default, there is no buffering or reuse of rows. For example:
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.
OPEN c FOR SELECT * FROM TABLE(f(...));
Cursors over table functions have the same fetch semantics as ordinary cursors.
CURSOR assignments based on table functions do not have any special semantics.
However, the SQL optimizer will not optimize across PL/SQL statements. For example:
DECLARE r SYS_REFCURSOR; BEGIN OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab))); SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r)); END; /
does not execute as well as:
SELECT * FROM TABLE(g(CURSOR(SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab))))));
This is so even ignoring the overhead associated with executing two SQL statements and assuming that the results can be pipelined between the two statements.
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
A predefined weak
SYS_REFCURSOR is also supported. With
SYS_REFCURSOR, you need not first create a
CURSOR type in a package before you can use it.
To use a strong
CURSOR type, you still must create a PL/SQL package and declare a strong
CURSOR type in it. Also, if you are using a strong
CURSOR type as an argument to a table function, then the actual type of the
CURSOR argument must match the column type, or an error is generated. Weak
CURSOR arguments to table functions can only be partitioned using the
ANY clause. You cannot use range or hash partitioning for weak
PL/SQL functions can accept multiple
CURSOR input variables as shown in Example 12-23.
For more information about cursor variables, see Declaring REF CURSOR Types and Cursor Variables.
-- Define the ref cursor types CREATE PACKAGE refcur_pkg IS TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE; TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE; TYPE outrec_typ IS RECORD ( var_num NUMBER(6), var_char1 VARCHAR2(30), var_char2 VARCHAR2(30)); TYPE outrecset IS TABLE OF outrec_typ; FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED; END refcur_pkg; / CREATE PACKAGE BODY refcur_pkg IS FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2) RETURN outrecset PIPELINED IS out_rec outrec_typ; in_rec1 p1%ROWTYPE; in_rec2 p2%ROWTYPE; BEGIN LOOP FETCH p2 INTO in_rec2; EXIT WHEN p2%NOTFOUND; END LOOP; CLOSE p2; LOOP FETCH p1 INTO in_rec1; EXIT WHEN p1%NOTFOUND; -- first row out_rec.var_num := in_rec1.employee_id; out_rec.var_char1 := in_rec1.first_name; out_rec.var_char2 := in_rec1.last_name; PIPE ROW(out_rec); -- second row out_rec.var_num := in_rec2.department_id; out_rec.var_char1 := in_rec2.department_name; out_rec.var_char2 := TO_CHAR(in_rec2.location_id); PIPE ROW(out_rec); END LOOP; CLOSE p1; RETURN; END; END refcur_pkg; / -- SELECT query using the g_trans table function SELECT * FROM TABLE(refcur_pkg.g_trans( CURSOR(SELECT * FROM employees WHERE department_id = 60), CURSOR(SELECT * FROM departments WHERE department_id = 60)));
You can pass table function return values to other table functions by creating a
CURSOR that iterates over the returned data:
SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));
You can explicitly open a
CURSOR 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 input ref cursor. Example 12-24 computes a weighted average by iterating over a set of input rows.
CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30), weight NUMBER, grade NUMBER); INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4); INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3); INSERT INTO gradereport VALUES('Mark','Maths', 3, 3); INSERT INTO gradereport VALUES('Mark','Economics', 3, 4); CREATE PACKAGE pkg_gpa IS TYPE gpa IS TABLE OF NUMBER; FUNCTION weighted_average(input_values SYS_REFCURSOR) RETURN gpa PIPELINED; END pkg_gpa; / CREATE PACKAGE BODY pkg_gpa IS FUNCTION weighted_average(input_values SYS_REFCURSOR) RETURN gpa PIPELINED IS grade NUMBER; total NUMBER := 0; total_weight NUMBER := 0; weight NUMBER := 0; BEGIN -- Function accepts ref cursor 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; END pkg_gpa; / -- Query result is a nested table with single row -- COLUMN_VALUE is keyword that returns contents of nested table SELECT w.column_value "weighted result" FROM TABLE( pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;
CREATE FUNCTION f(p SYS_REFCURSOR) RETURN CollType PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN NULL; END; /
During parallel execution, each instance of the table function creates an independent transaction.
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
OF triggers to update it. For example:
CREATE VIEW BookTable AS SELECT x.Name, x.Author FROM TABLE(GetBooks('data.txt')) x;
OF trigger fires when the user inserts a row into the
CREATE TRIGGER BookTable_insert INSTEAD OF INSERT ON BookTable REFERENCING NEW AS n FOR EACH ROW BEGIN ... END / INSERT INTO BookTable VALUES (...);
OF triggers can be defined for all DML operations on a view built on a table function.
Some languages, such as C and Java, provide a mechanism for user-supplied exception handling. If an exception raised within a table function is handled, the table function executes the exception handler and continues processing. Exiting the exception handler takes control to the enclosing scope. If the exception is cleared, execution proceeds normally.
An unhandled exception in a table function causes the parent transaction to roll back.