This discussion is archived
2 Replies Latest reply: Mar 20, 2013 10:48 PM by 998210 RSS

A fuzzy problem when used BULK COLLECT in CURSOR fetching

998210 Newbie
Currently Being Moderated
When I used FETCH cursor BULK COLLECT INTO table statement, the flow path is strange.

[Oracle version: 11.2.0.1.0]

Firstly, let's see the table and the codes:
-----
table name: fruits
id name category time
1 Apple Fruit 2013-03-01
2 Orange Fruit 2013-05-01
3 Balana Fruit 2013-03-23
4 Spinach Vegetable 2013-04-02
5 Celery Vegetable 2013-04-02
6 Cabbage Vegetable 2013-04-02

-----

the code 1:
declare
2 cursor fcursor is select * from fruits;
3 type fftable is table of fruits%rowtype;
4 ftable fftable;
5 begin
6 open fcursor;
7 loop
8 dbms_output.put_line('test!');
9 fetch fcursor bulk collect into ftable limit 1;
10 dbms_output.put('A' || ftable.count || ' ');
11 for i in 1..ftable.count loop
12 dbms_output.put_line(ftable(i).name);
13 end loop;
14 dbms_output.put('a' || ftable.count || ' ');
15 exit when fcursor%notfound;
16 dbms_output.put_line(ftable.count || ' ' || fcursor%rowcount);
17 end loop;
18 close fcursor;
19 end;
20 /

-----
the output 1:
test!
A1 Apple
a1 1 1
test!
A1 Orange
a1 1 2
test!
A1 Balana
a1 1 3
test!
A1 Spinach
a1 1 4
test!
A1 Celery
a1 1 5
test!
A1 Cabbage
a1 1 6
test!

-----
the code 2:
declare
2 cursor fcursor is select * from fruits;
3 ftable fruits%rowtype;
4 begin
5 open fcursor;
6 loop
7 dbms_output.put_line('test!');
8 fetch fcursor into ftable;
9 dbms_output.put_line(ftable.name);
10 exit when fcursor%notfound;
11 dbms_output.put_line(fcursor%rowcount);
12 end loop;
13 close fcursor;
14 end;
15 /

-----
the output 2:
test!
Apple
1
test!
Orange
2
test!
Balana
3
test!
Spinach
4
test!
Celery
5
test!
Cabbage
6
test!
Cabbage

-----
See? As common logic, the output 1 should be the same as the output 2. But here, the output 1 had not the duplicated Cabbage! And another strange thing is that there are "test!" in the output 1, it means that the flow path of code 1 was broken when the executor reached "fetch fcursor bulk collect into ftable limit 1;" but not "exit when fcursor%notfound;"!
What happened? I beg for the answer of this question ,please!

Edited by: Texvnars on 2013-3-20 下午10:36
  • 1. Re: A fuzzy problem when used BULK COLLECT in CURSOR fetching
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your 4 digit Oracle version.

    No - it won't be the same based on the code you posted.

    The first set of code has this:
    10 dbms_output.put('A' || ftable.count || ' ');
    You used 'put' and not 'put_line' like you used in the second set of code.

    The problem you mentioned though is because in the second set of code you have this
    8 fetch fcursor into ftable;
    9 dbms_output.put_line(ftable.name);
    10 exit when fcursor%notfound;
    You are using 'put_line' to output that last 'name' even though there weren't any more rows fetched.

    You need to check the '%notfound' IMMEDIATELY AFTER the FETCH when you fetch a single row. You put the check at the END of the loop if you do a BULK COLLECT.

    Everything is working as expected.
  • 2. Re: A fuzzy problem when used BULK COLLECT in CURSOR fetching
    998210 Newbie
    Currently Being Moderated
    Thank you for your replying.
    I just have turned the 'put' to the 'put_line' and the output was as what I expected. So it means that the 'put' would not flush the cache immediately. I should use the 'put' carefully.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points