Examples Using FORALL and BULK COLLECT
TimesTen supports bulk binding and the FORALL statement and BULK COLLECT feature.
See FORALL and BULK COLLECT Operations.
Examples in this section cover the following:
Using FORALL with SQL%BULK_ROWCOUNT
The %BULK_ROWCOUNT cursor attribute is a composite structure designed for use with the FORALL statement.
The attribute acts like an associative array (index-by table). Its ith element stores the number of rows processed by the ith execution of the INSERT statement. If the ith execution affects no rows, then %BULK_ROWCOUNT(i) returns zero.
This is demonstrated in the following example.
Command> DECLARE
TYPE num_list_type IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
v_nums num_list_type;
BEGIN
v_nums (1) := 1;
v_nums (2) := 3;
v_nums (3) := 5;
v_nums (4) := 7;
v_nums (5) := 11;
FORALL i IN v_nums.FIRST .. v_nums.LAST
INSERT INTO num_table (n) VALUES (v_nums (i));
FOR i IN v_nums.FIRST .. v_nums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE ('Inserted '||
SQL%BULK_ROWCOUNT (i) || ' row (s)' ||
' on iteration ' || i );
END LOOP;
END;
/
Inserted 1 row (s) on iteration 1
Inserted 1 row (s) on iteration 2
Inserted 1 row (s) on iteration 3
Inserted 1 row (s) on iteration 4
Inserted 1 row (s) on iteration 5
PL/SQL procedure successfully completed.Using BULK COLLECT INTO with Queries
Use BULK COLLECT with the SELECT statement in PL/SQL to retrieve rows without using a cursor.
This example selects all rows from the departments table for a specified location into a nested table, then uses a FOR LOOP to output data.
Command> CREATE OR REPLACE PROCEDURE get_departments (p_loc NUMBER) IS
TYPE dept_tab_type IS
TABLE OF departments%ROWTYPE;
v_depts dept_tab_type;
BEGIN
SELECT * BULK COLLECT INTO v_depts
FROM departments
where location_id = p_loc;
FOR i IN 1 .. v_depts.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (v_depts(i).department_id
|| ' ' || v_depts (i).department_name);
END LOOP;
END;
/
Procedure created.The following executes the procedure and verifies the results:
Command> EXECUTE GET_DEPARTMENTS (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
PL/SQL procedure successfully completed.
Command> SELECT department_id, department_name FROM departments WHERE
location_id = 1700;
< 10, Administration >
< 30, Purchasing >
< 90, Executive >
< 100, Finance >
< 110, Accounting >
< 120, Treasury >
< 130, Corporate Tax >
< 140, Control And Credit >
< 150, Shareholder Services >
< 160, Benefits >
< 170, Manufacturing >
< 180, Construction >
< 190, Contracting >
< 200, Operations >
< 210, IT Support >
< 220, NOC >
< 230, IT Helpdesk >
< 240, Government Sales >
< 250, Retail Sales >
< 260, Recruiting >
< 270, Payroll >
21 rows found.Using BULK COLLECT INTO with Cursors
This example uses a cursor to bulk-collect rows from the departments table with a specified location_id. value.
Results are the same as in the preceding section, Using BULK COLLECT INTO with Queries.
Command> CREATE OR REPLACE PROCEDURE get_departments2 (p_loc NUMBER) IS
CURSOR cur_dept IS
SELECT * FROM departments
WHERE location_id = p_loc;
TYPE dept_tab_type IS TABLE OF cur_dept%ROWTYPE;
v_depts dept_tab_type;
BEGIN
OPEN cur_dept;
FETCH cur_dept BULK COLLECT INTO v_depts;
CLOSE cur_dept;
FOR i IN 1 .. v_depts.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (v_depts (i).department_id
|| ' ' || v_depts (i).department_name );
END LOOP;
END;
/
Procedure created.
Command> EXECUTE GET_DEPARTMENTS2 (1700);
10 Administration
30 Purchasing
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
PL/SQL procedure successfully completed.Using SAVE EXCEPTIONS with BULK COLLECT
SAVE EXCEPTIONS enables an UPDATE, INSERT, or DELETE statement to continue executing after it issues an exception. When the statement finishes, an error is issued to signal that at least one exception occurred. Exceptions are collected into an array that you can examine using %BULK_EXCEPTIONS after the statement has executed.
In this example, PL/SQL raises predefined exceptions because some new values are too large for the job_id column. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returns 2, and the contents of SQL%BULK_EXCEPTIONS are (7, 01401) and (13, 01401), indicating the error number and the line numbers where the error was detected. To get the error message, the negative of SQL%BULK_EXCEPTIONS(i).ERROR_CODE is passed to the error-reporting function SQLERRM (which expects a negative number).
The following script is executed using ttIsql:
-- create a temporary table for this example
CREATE TABLE emp_temp AS SELECT * FROM employees;
DECLARE
TYPE empid_tab IS TABLE OF employees.employee_id%TYPE;
emp_sr empid_tab;
-- create an exception handler for ORA-24381
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA EXCEPTION_INIT(dml_errors, -24381);
BEGIN
SELECT employee_id
BULK COLLECT INTO emp_sr FROM emp_temp
WHERE hire_date < '1994-12-30';
-- add '_SR' to the job_id of the most senior employees
FORALL i IN emp_sr.FIRST..emp_sr.LAST SAVE EXCEPTIONS
UPDATE emp_temp SET job_id = job_id || '_SR'
WHERE emp_sr(i) = emp_temp.employee_id;
-- If any errors occurred during the FORALL SAVE EXCEPTIONS,
-- a single exception is raised when the statement completes.
EXCEPTION
-- 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;Results are as follows:
Number of statements that failed: 2
Error #1 occurred during iteration #7
Error message is ORA-01401: inserted value too large for column
Error #2 occurred during iteration #13
Error message is ORA-01401: inserted value too large for column
PL/SQL procedure successfully completed.