Execute immediate not working with bulk collect
394333Jun 12 2003 — edited Jun 18 2003Hi,
I have five tables say t1, t2, t3, t4, t5.
All of them have the same structure. There is a column say c in all of them.
I have a stored procedure where I want to have the values of the column c in an array for which I use BULK COLLECT.
The which table is to be used is decided at run time for which I use EXECUTE IMMEDIATE.
The code is somewhat like this â
PROCEDURE p(num) IS
TYPE arr_number IS VARRAY(100) OF INTEGER;
arr ARR_NUMBER;
cntr NUMBER;
BEGIN
arr := ARR_NUMBER();
/* the number passed as an argument to the procedure is concatenated with t to decide the table from which data is to be fetched */
EXECUTE IMMEDIATE 'SELECT amt BULK COLLECT FROM t' || num INTO arr;
FOR cntr IN arr.FIRST .. arr.LAST
LOOP
DBMS_OUTPUT.PUT_LINE (arr(cntr));
END LOOP;
END p;
This code gives error ORA-00600 .................WHICH READS AS AN INTERNAL ERROR...... WITH NO HELP IN THE DOCUMENTATION.....
Can some one help me out plz....
TIA,
Ashish.