This discussion is archived
2 Replies Latest reply: Apr 22, 2013 11:05 AM by rp0428 RSS

BULK COLLECT INTO (Oracle 11 G)

1000434 Newbie
Currently Being Moderated
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.
  • 1. Re: BULK COLLECT INTO (Oracle 11 G)
    BluShadow Guru Moderator
    Currently Being Moderated
    Please post the relevant code so we can see what the error may be. There's no point in us guessing.

    {message:id=9360002}
  • 2. Re: BULK COLLECT INTO (Oracle 11 G)
    rp0428 Guru
    Currently Being Moderated
    >
    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 [7369].
    empno is [7499].
    empno is [7521].
    Processing 3 records.
    empno is [7566].
    empno is [7654].
    empno is [7698].
    Processing 3 records.
    empno is [7782].
    empno is [7788].
    empno is [7839].
    Processing 3 records.
    empno is [7844].
    empno is [7876].
    empno is [7900].
    Processing 2 records.
    empno is [7902].
    empno is [7934].
    All done

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points