2 Replies Latest reply: Mar 21, 2013 12:48 AM by 998210 RSS

    A fuzzy problem when used BULK COLLECT in CURSOR fetching

    998210
      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
          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
            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.