Bulk Collect into is storing less no of rows in collection when using LIMIT?

Question

I have written the following anonymous PL SQL Block.

However, the line dbms_output.put_line(total_tckt_col.LAST) gives me output as 366 (in DBMS_OUTPUT is SQL Developer) which is correct when no limit is set.

If the limit is set to 100 in the FETCH statement then dbms_output.put_line(total_tckt_col.LAST) gives me 66.

What am I doing wrong here?

    DECLARE

           CURSOR cur_total_tckt  
            is
                select  t.ticket_id ticket_id, t.created_date created_date, t.created_by created_by, t.ticket_status ticket_status, 
                t.last_changed last_changed, h.created_date closed_date
                    from n01.cc_ticket_info t
                    inner join n01.cc_ticket_status_history h 
                    on (t.ticket_id = h.ticket_id)
                    where t.last_changed >= '6/28/2012 17:28:59' and t.last_changed < (sysdate + interval '1' day);

        type total_tckt_colcn
        is
            TABLE OF cur_total_tckt%rowtype;
            total_tckt_col total_tckt_colcn;
            total_coach_col total_tckt_colcn;
        begin

        total_tckt_col  := total_tckt_colcn ();
        total_coach_col := total_tckt_colcn ();
            OPEN cur_total_tckt;
            loop
                fetch cur_total_tckt bulk collect into total_tckt_col;
 --  fetch cur_total_tckt bulk collect into total_tckt_col limit 100;
            EXIT
            WHEN (cur_total_tckt%NOTFOUND);
            END LOOP ;
            CLOSE cur_total_tckt;   

            dbms_output.put_line(total_tckt_col.LAST);  

            FOR i IN total_tckt_col.first..total_tckt_col.last
            LOOP

            dbms_output.put_line(i);

            END LOOP;
        end;

Answer 1

The problem is when you are looping through with limit set to 100, you are dropping previous fetch results.

So 3 times 100 rows are fetched which get dropped everytime you loop through and in last turn 66 are fetched, so you get result as 66.

You need to accumulate all the results to get correct count.

For correct usage of bulk collect with limit, see this example:

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
    CURSOR employees_cur 
    IS 
        SELECT * FROM employees;

    TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
        INDEX BY PLS_INTEGER;

    l_employees employees_aat;
BEGIN   
    OPEN employees_cur;
    LOOP
        FETCH employees_cur 
            BULK COLLECT INTO l_employees LIMIT limit_in;

        FOR indx IN 1 .. l_employees.COUNT 
        LOOP
            analyze_compensation (l_employees(indx));
        END LOOP;

        EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows;

So your for loop should reside inside normal loop where you bulk collect with limit.

So correct code will be :

DECLARE

           CURSOR cur_total_tckt  
            is
                select  t.ticket_id ticket_id, t.created_date created_date, t.created_by created_by, t.ticket_status ticket_status, 
                t.last_changed last_changed, h.created_date closed_date
                    from n01.cc_ticket_info t
                    inner join n01.cc_ticket_status_history h 
                    on (t.ticket_id = h.ticket_id)
                    where t.last_changed >= '6/28/2012 17:28:59' and t.last_changed < (sysdate + interval '1' day);

        type total_tckt_colcn
        is
            TABLE OF cur_total_tckt%rowtype;
            total_tckt_col total_tckt_colcn;
            total_coach_col total_tckt_colcn;
        begin

        total_tckt_col  := total_tckt_colcn ();
        total_coach_col := total_tckt_colcn ();
            OPEN cur_total_tckt;
            loop

             fetch cur_total_tckt bulk collect into total_tckt_col limit 100;


            dbms_output.put_line(total_tckt_col.LAST);  

            FOR i IN total_tckt_col.first..total_tckt_col.last
            LOOP

            dbms_output.put_line(i);

            END LOOP;

            EXIT
            WHEN (cur_total_tckt%NOTFOUND);
            END LOOP ;
            CLOSE cur_total_tckt;
        end;