CODEWITHSUNDEEP
sqloracle-databaseplsql
Dreamer
Dreamer

Reputation: 11

How to fetch into collection using ref cursor, when the record count in table is a multiple of the limit number specified?

create table with id column

CREATE TABLE TEST_TAB
  (ID NUMBER
  );

create type

CREATE type numbertabletype IS TABLE OF NUMBER;

insert 100 records

  INSERT INTO TEST_TAB
  SELECT LL FROM
    (SELECT LEVEL LL FROM DUAL CONNECT BY LEVEL<=100
    );

Create function that loops through 100 records in 10 loops, cursor will fetch into numbertable type collection with LIMIT 10.

CREATE OR REPLACE FUNCTION LOOP_TEST
RETURN NUMBERTABLETYPE
IS
  lv_coll NUMBERTABLETYPE ;
  LV_COUNT NUMBER:=0;
  CURSOR c1
  IS
    SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;
BEGIN
  OPEN c1;
  LOOP
    dbms_output.put_line('BEFORE FETCH CURSOR COUNT '||C1%ROWCOUNT);
    FETCH c1 bulk collect INTO lv_coll limit 10;
    dbms_output.put_line('AFTER FETCH CURSOR COUNT '||C1%ROWCOUNT);
    EXIT
  WHEN c1%NOTFOUND;
    LV_COUNT:=LV_COUNT+1;
    dbms_output.put_line(' BELOW NOT FOUND '||LV_COUNT);
    dbms_output.put_line('COLLECTION COUNT '||lv_coll.count);
  END LOOP;
  CLOSE c1;
  RETURN lv_coll;
END;
/

Run sql as script, it returns null when rownum = 100

CURSOR c1
IS
  SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;

Run sql as script, it returns values when rownum = 99, replace cursor c1 in function with below cursor.

CURSOR c1
IS
  SELECT ID FROM TEST_TAB WHERE ROWNUM<=99;

so How do you tackle a scenario when the limit is a multiple of total records to loop.

Upvotes: 0

Views: 1515

Answers (2)

Popeye
Popeye

Reputation: 35920

I tried to run your case in an anonymous block and it failed to output in case of ROWNUM<=100.

I changed the little bit in your code and it worked.

-- 
DECLARE
  lv_coll NUMBERTABLETYPE ;
  lv_col2 NUMBERTABLETYPE ; -- added this line to hold the data
  LV_COUNT NUMBER:=0;
  CURSOR c1
  IS
    SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;
BEGIN
  OPEN c1;
  LOOP
    --dbms_output.put_line('BEFORE FETCH CURSOR COUNT '||C1%ROWCOUNT);
    FETCH c1 bulk collect INTO lv_coll limit 10;
    EXIT WHEN lv_coll.COUNT = 0; -- added this line
    lv_col2 := lv_coll; -- added this line
    --dbms_output.put_line('AFTER FETCH CURSOR COUNT '||C1%ROWCOUNT);
   -- EXIT
  --WHEN c1%NOTFOUND;
   -- LV_COUNT:=LV_COUNT+1;
    --dbms_output.put_line(' BELOW NOT FOUND '||LV_COUNT);
    --dbms_output.put_line('COLLECTION COUNT '||lv_coll.count);
  END LOOP;
  CLOSE c1;
  --RETURN lv_coll;
  FOR I IN 1..lv_col2.COUNT LOOP -- added this line
  dbms_output.put_line(lv_col2(I)); -- added this line
  END LOOP;
END;
/

db<>fiddle demo

Upvotes: 0

MT0
MT0

Reputation: 168361

The code is working perfectly; it is just not doing what you expect.

When the code loops it will overwrite the lv_coll collection each time so that it will contain no more than 10 items.

When it fetches the 91-100th rows it fills the collection with 10 items and processes it but, because it has not tried to read another row, it is unaware that there are no more rows left in the cursor and has not yet reached the c1%NOTFOUND condition to terminate the loop.

When it repeats the loop, after that, it will find that the cursor has now been exhausted and will read zero rows. So, in this final loop, the lv_coll collection is not NULL but is a collection containing zero elements and that is what is going to get returned.

Compare this to when there are only 99 rows in the cursor. When the loop tries to read the 91st - 100th rows it will read the 91st - 99th rows and try to read the 100th but will find that the cursor has been exhausted and c1%NOTFOUND will be true exiting the loop and causing the collection to be returned with only 9 items.

If you want to return all the elements from the cursor then you will need to use a second collection to aggregate them as the one populated from the cursor will be overwritten at each loop:

CREATE OR REPLACE FUNCTION LOOP_TEST
RETURN NUMBERTABLETYPE
IS
  lv_coll  NUMBERTABLETYPE;
  all_items  NUMBERTABLETYPE := NUMBERTABLETYPE();
  LV_COUNT NUMBER:=0;
  ids      VARCHAR2(30);
  CURSOR c1
  IS
    SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;
BEGIN
  OPEN c1;
  LOOP
    dbms_output.put_line('BEFORE FETCH CURSOR COUNT '||C1%ROWCOUNT);
    FETCH c1 bulk collect INTO lv_coll limit 10;
    dbms_output.put_line('AFTER FETCH CURSOR COUNT '||C1%ROWCOUNT);
    EXIT WHEN c1%NOTFOUND;
    LV_COUNT:=LV_COUNT+1;
    all_items := all_items MULTISET UNION ALL lv_coll;
    dbms_output.put_line(' BELOW NOT FOUND '||LV_COUNT);
    dbms_output.put_line('COLLECTION COUNT '||lv_coll.count);
--    SELECT LISTAGG(COLUMN_VALUE,',') WITHIN GROUP( ORDER BY ROWNUM )
--    INTO   ids
--    FROM   TABLE(lv_coll);
--    dbms_output.put_line('IDS: '||ids);
  END LOOP;
  CLOSE c1;
  RETURN all_items;
END;
/

Which can be called using:

DECLARE
  ids numbertabletype;
  vals VARCHAR2(3000);
BEGIN
  ids := LOOP_TEST();

  DBMS_OUTPUT.PUT_LINE( 'NUMBER OF IDs: ' || CASE WHEN ids IS NULL THEN 'NULL' ELSE TO_CHAR( ids.COUNT ) END );

  SELECT LISTAGG(COLUMN_VALUE,',') WITHIN GROUP( ORDER BY ROWNUM )
  INTO   vals
  FROM   TABLE(ids);

  DBMS_OUTPUT.PUT_LINE( 'values: ' || vals );
END;
/

db<>fiddle here

Upvotes: 1

Related Questions