Oracle 9i now enables us to bulk fetch from Native Dynamic SQL statements.
Using record-based PL/SQL we can now BULK COLLECT into one associative array and FORALL INSERT the entire record into a target table without ever having to reference either a column from the staging or target tables or an attribute from a record element in the associative array. DECLARE 2 3 TYPE typ_aa_allobj IS TABLE OF all_objects%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_allobjs typ_aa_allobj; 6 7 BEGIN 8 9 SELECT * BULK COLLECT INTO aa_allobjs 10 FROM all_objects; 11 12 FORALL i IN aa_allobjs. PUT_LINE ( 17 TO_CHAR(SQL%ROWCOUNT) || 18 ' records inserted.' ); 19 20 END; 21 / Obviously this type of operation would usually be best implemented via SQL as an INSERT..
SELECT, but in the real-world we might have many transformations and business rules to apply to the data between the initial fetch and the final DML.
DECLARE 2 3 TYPE typ_aa_object IS TABLE OF user_objects%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_objects typ_aa_object; 6 7 v_predicates VARCHAR2(256) := :where_clause; 8 9 BEGIN 10 11 /* Execute the statement and bulk fetch the results... COUNT || 19 ' records fetched.' ); 20 21 END; 22 / DECLARE 2 3 TYPE typ_aa_object IS TABLE OF user_objects%ROWTYPE 4 INDEX BY PLS_INTEGER; 5 aa_objects typ_aa_object; 6 7 v_predicates VARCHAR2(256) := :where_clause; 8 9 cv SYS_REFCURSOR; 10 11 BEGIN 12 13 /* Open a cursor variable for dynamic SQL... COUNT || 29 ' records fetched.' ); 30 31 END; 32 / Oracle release 8.0 gave us the opportunity to define our own data types and create tables with columns based on these user-defined types.
*/ 12 EXECUTE IMMEDIATE ' SELECT * 13 FROM user_objects 14 WHERE ' || v_predicates 15 BULK COLLECT INTO aa_objects; 16 17 /* How many did we get ? */ 14 OPEN cv FOR ' SELECT * 15 FROM user_objects 16 WHERE ' || v_predicates; 17 18 /* Fetch from the ref cursor... When bulk PL/SQL processing was introduced in 8i, it provided a very limited scope for bulk fetching columns of user-defined types from tables into index-by tables or collections.
Bulk binding in PL/SQL was introduced in Oracle 8i as the FORALL statement.' records.' ); 39 40 DBMS_OUTPUT. ERROR_INDEX