2 Replies Latest reply: Apr 22, 2013 1:05 PM by rp0428 RSS

    BULK COLLECT INTO (Oracle 11 G)

    1000434
      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
          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
            >
            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