INDICES OF clause

INDICES OF clause


Support for bulk binds with sparse collections was introduced in Oracle 10g by adding the


INDICES OF and VALUES OF clauses in the FORALL statement.


The INDICES OF clause allows a bulk operation on a sparse collection by removing the


reference to specific elements. In addition, upper and lower bounds can be specified


using the BETWEEN clause. Allowable syntaxes include:


FORALL index IN INDICES OF collection
FORALL index IN INDICES OF collection BETWEEN start AND end


The usage of the INDICES OF clause is shown in the forall_indices_of.sql script below.


forall_indices_of.sql


SET SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;


l_tab t_forall_test_tab := t_forall_test_tab();
BEGIN
FOR i IN 1 .. 1000 LOOP
l_tab.extend;


l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;


-- Make collection sparse.


l_tab.delete(301);
l_tab.delete(601);
l_tab.delete(901);


EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;


DBMS_OUTPUT.put_line(‘Start FORALL’);
BEGIN


-- This will fail due to sparse collection.


FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(SQLERRM);
END;


EXECUTE IMMEDIATE ‘TRUNCATE TABLE forall_test’;


DBMS_OUTPUT.put_line(‘Start FORALL INDICES OF’);


-- This works fine with sparse collections.


FORALL i IN INDICES OF l_tab
INSERT INTO forall_test VALUES l_tab(i);
END;
/