Oracle Bulk Collect issue

Question

Hi Oracle experts.

I'm having a little issue with a piece of bulk collect sql that I was hoping you could help out with.

With the following code:

declare
    cursor c1
    is
    select customer,product
    from products;

    type type_cust is table of products.customer%type;
    type type_prod is table of products.product%type;

    v_array_cust    type_cust;
    v_array_prod    type_prod;
begin
    open c1;
    loop
        fetch c1 
        into v_array_cust, v_array_prod
        limit 1000;

        exit when c1%notfound;

        for i in 1..v_array_cust.count
        loop
            --Do some processing here.
        end loop;
    end loop;
end;
/

The cursor c1 returns 53166 rows.

However, the code process 53000 rows and then ends. It seems that when going to retrieve the last 166 records there is some sort of failure.

Will the fetch return %notfound if it find's less than 1000 records? Should I move the exit to the end of the loop? (I am going to try this but it is deep in a piece of code that take 3 hours to get to the failure point.)

Thanks in advance.

Answer 1

OK, well a better bit of googling than I'd already done gave me the answer you shouldn't use %notfound with limit.

Check here for an explanation.

Answer 2

Sorry to be the one to say it, but ack! cursors!...the way you've written this appears you've come from a linear programming background. Have you considered a set based solution to this?

Answer 3

Just for reference, here's one simple change to make the code run correctly:

open c1;
loop
    fetch c1 
    into v_array_cust, v_array_prod
    limit 1000;

    -- the count will be 0 if no rows were found, so this loop will do nothing.
    for i in 1..v_array_cust.count
    loop
        --Do some processing here.
    end loop;

    -- exit now if the last fetch got the last set of rows
    exit when c1%notfound;
end loop;
close c1;