Bulk collect record collection
Posted by decipherinfosys on April 30, 2009
We have discussed bulk collect before in our posts. As we have shown before that bulk collect is very useful to process large data set. We can use bulk collect to avoid the looping for multiple records as well. We have covered in an earlier post how to collect multiple records into scalar variables. In this blog post, we will see how to use bulk collect clause to store results in collection records. Scalar variables are useful only when we have to deal with small number of columns but when we have to work with all the columns of the table, storing result set in collection of records is very useful. As usual, let us start with an example. Following is a little code snippet which stores the result set into collection of records.
SET SERVEROUTPUT ON
TYPE testRecTab IS TABLE OF all_objects%ROWTYPE INDEX BY PLS_INTEGER;
BULK COLLECT INTO test_recs
dbms_output.put_line(‘No. of Objects: ‘ || test_recs.count);
Here is the output.
No. of Objects: 55910
PL/SQL procedure successfully completed.
Using collection of records is very handy and clean when we have to deal with large number of records. Once data is bulk collected we can use either FOR loop for FORALL construct to deal with the data. We will show how can we use collection of records with an insert statement in the next blog post.
- Oracle 11g PL/SQL Reference Manual – here.