Systems Engineering and RDBMS

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
DECLARE

TYPE testRecTab IS TABLE OF all_objects%ROWTYPE INDEX BY PLS_INTEGER;
test_recs testRecTab;
V_Err_count   NUMBER;

BEGIN

SELECT *
BULK COLLECT INTO test_recs
FROM all_objects;

dbms_output.put_line(‘No. of Objects: ‘ || test_recs.count);

END;
/

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.

Resources:

  • Oracle 11g PL/SQL Reference Manual – here.

2 Responses to “Bulk collect record collection”

  1. […] Bulk collect record collection […]

  2. moleboy said

    The various bulk operations have been an invaluable resource to me in almost every job I’ve had. Most notably was a position where I was converting new customer data into our system. This involved the processing of millions of rows. Bulk operations helps us turn that from a 3-5 day process down to a single day.
    If you aren’t using them, you should.
    The only method I’ve found more efficient is using “CREATE TABLE AS” methods, which are not always appropriate, but are lightning fast.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: