This content has been marked as final. Show 2 replies
ORACLE 11G (The same code works fine in Oracle 9i)
FETCH v_cursor BULK COLLECT INTO AuditData, AuditData1;
the above statement gives me an error:-
ORA-01007: variable not in select list
ORA-06512: at "DEC0512.FCT_AUD_TRAIL_REAL_ENTRY_UPD", line 175
ORA-06512: at line 1
But if the change the statemement to
FETCH v_cursor BULK COLLECT INTO AuditData;
The query works fine.
Any help will be much appreciated.
If the second query 'works fine' then your 'AuditData' collection is consuming all of the result set columns and there is nothing to put into the other collection.
So you have a mismatch between the result set your cursor returns and those collections you are trying to put the data into.
This works for me:
DECLARE CURSOR c1 IS (SELECT empno, deptno FROM emp); empno_tab dbms_sql.number_table; deptno_tab dbms_sql.number_table; BEGIN OPEN c1; LOOP --Loop added FETCH c1 BULK COLLECT INTO empno_tab, deptno_tab LIMIT 3; -- process 3 records at a time -- process the first 3 records DBMS_OUTPUT.PUT_LINE('Processing ' || empno_tab.COUNT || ' records.'); FOR i in 1..empno_tab.count loop dbms_output.put_line('empno is [' || empno_tab(i) || '].'); end loop; EXIT WHEN c1%NOTFOUND; END LOOP; DBMS_OUTPUT.PUT_LINE('All done'); END; Processing 3 records. empno is . empno is . empno is . Processing 3 records. empno is . empno is . empno is . Processing 3 records. empno is . empno is . empno is . Processing 3 records. empno is . empno is . empno is . Processing 2 records. empno is . empno is . All done