When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

==============

General

==============

When working with FETCH cursor BULK COLLECT INTO, where to put the EXIT WHEN cursor%NOTFOUND; ?

For Example:

The Limit is 20000 but actual data is 20

Where do you put the EXIT WHEN  cursor%NOTFOUND ?

==============

Code Example 1 - not good

==============

Example 1 - after the FETCH

This is not good - as the BEGIN Block of FORALL is never reached.

 PROCEDURE purge_temp_keys(v_return_code OUT NUMBER, 

                           v_affiliate_id IN NUMBER )IS

  TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tb_rows    ARR_ROWID;
  row       PLS_INTEGER;
  v_counter INTEGER;
  
  CURSOR purge_population_cur (v_affiliate_id in number) IS
  SELECT rowid FROM SFI_SPARX_TEMP_KEYS
    WHERE affiliate_id = v_affiliate_id;

  v_bulk    NUMBER(10):=0;
  -- ora-24381 error(s) in array dml in oracle
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  v_msg_str varchar2(200):='';

BEGIN
  v_return_code:=0;
  OPEN purge_population(v_affiliate_id);
  LOOP
    v_bulk := v_bulk+1;    
    FETCH purge_population_cur bulk collect into tb_rows limit 20000;
    EXIT WHEN purge_population_cur%NOTFOUND;
    BEGIN
      FORALL row IN 1 .. tb_rows.count()
      DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
      COMMIT;
    EXCEPTION
      WHEN dml_errors THEN
        COMMIT;
        RAISE;
      WHEN others THEN -- other exceptions :
        COMMIT;
    END;
    --EXIT WHEN purge_population_cur%NOTFOUND;
  END LOOP;
  CLOSE purge_population_cur;
  commit;
  v_return_code:=0;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      CLOSE purge_population_cur;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
    v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;

Example 2 

The EXIT is at the end of the LOOP

This is correct

Now code in BLOCK is looped once

 PROCEDURE purge_temp_keys(v_return_code OUT NUMBER, 

                           v_affiliate_id IN NUMBER )IS

  TYPE ARR_ROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tb_rows   ARR_ROWID;
  row       PLS_INTEGER;
  v_counter INTEGER;
  
  CURSOR purge_population_cur(v_affiliate_id in number) IS
  SELECT rowid FROM SFI_SPARX_TEMP_KEYS
    WHERE affiliate_id = v_affiliate_id;

  v_bulk    NUMBER(10):=0;
  -- ora-24381 error(s) in array dml in oracle
  dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  v_msg_str varchar2(200):='';

BEGIN
  v_return_code:=0;
  OPEN purge_population_cur(v_affiliate_id);
  LOOP
    v_bulk := v_bulk+1;    
    FETCH purge_population_cur bulk collect into tb_rows limit 20000;
    --EXIT WHEN purge_population_cur%NOTFOUND;
    BEGIN
      FORALL row IN 1 .. tb_rows.count()
      DELETE SFI_SPARX_TEMP_KEYS WHERE rowid = tb_rows(row);
      COMMIT;
    EXCEPTION
      WHEN dml_errors THEN
        COMMIT;
        RAISE;
      WHEN others THEN -- other exceptions :
        COMMIT;
    END;
    EXIT WHEN purge_population_cur%NOTFOUND;
  END LOOP;
  CLOSE purge_population_cur;
  commit;
  v_return_code:=0;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      CLOSE purge_population_cur;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    RAISE;
    v_return_code := -1;
END PURGE_SFI_SPARX_TEMP_KEYS;