PL/SQL No Data found error on forall loop

Question

I am getting no data found error while looping over an array. The execute immediate has data, but the forall loop is giving no data found error and not able to iterate over the collection.

Please find the code below. code_arr.FIRST seems to have some issue. Table has data and executing sql gives data on editor. Could you please help.

create or replace PACKAGE TEST AS
  FUNCTION TEST RETURN NUMBER;
 END;


create or replace PACKAGE BODY TEST AS
  FUNCTION TEST RETURN NUMBER
    IS
        TYPE typ_varchar IS TABLE OF VARCHAR2 (1000) INDEX BY BINARY_INTEGER;
        lv_statement   VARCHAR2 (1000);
        code_arr       typ_varchar;
        var1 varchar(1000);
  BEGIN
        lv_statement :=    'SELECT  lnm.code FROM employee lnm';

        EXECUTE IMMEDIATE lv_statement BULK COLLECT
        INTO code_arr;

          FORALL ix1 IN code_arr.FIRST .. code_arr.LAST SAVE EXCEPTIONS
          SELECT code_arr(ix1) into var1 FROM DUAL;

        RETURN 1;
  END;
END;

Thanks in advance for your help.

Mathew

Answer 1

FORALL is meant for bulk DML and not for looping through data. The syntax diagram shows this:

To be pedantic, SELECT is a form of DML, although it's usually considered separate from commands that modify objects. That might be why the original code sort of works but throws an error at run time instead of at compile time.

If all you need to do is loop through data, just use a cursor for loop like this. Oracle automatically uses bulk collect for these types of loops:

begin
    for employees in
    (
        SELECT  lnm.code FROM employee lnm
    ) loop
        --Do something here.
        null;
    end loop;
end;
/