Oracle: Bulk collect into "table of number" gives "numeric or value error"

Question

I've come across an example of doing this on the net which also fails on my DB (10g) but here's my version.

...
TYPE prog_rec_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

prog_rec_list prog_rec_type;

begin

...

EXECUTE IMMEDIATE 'SELECT PROGRESS_RECID FROM ' || v_table_name || v_where BULK COLLECT INTO prog_rec_list;


--ERROR FOUND IN THIS SECTION
FOR i IN prog_rec_list.FIRST..prog_rec_list.LAST
LOOP


--DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || i);

null;

END LOOP;

...
END;

Much appreciate the help.

Answer 1

The result set is empty. So you need to check that you got some results from that SELECT PROGRESS_RECID FROM ' || v_table_name || v_where

Answer 2

you may try this,

FOR i IN 1 .. prog_rec_list.COUNT LOOP
..
END LOOP