3 Replies Latest reply: Jun 14, 2010 5:36 AM by Twinkle RSS

    PLS-00642: local collection types not allowed in SQL statements

    Twinkle
      Hi,

      I want to retrieve empno in plsql table.
      Now based on the empno in plsql table I want to retrieve thier deptno in another plsql table/varray.
      SQL> declare
        2   type vdeptno  is table of number;
        3    v_deptno vdeptno;
        4    TYPE e_tab is table of  PLS_INTEGER INDEX BY PLS_INTEGER;
        5    empno_tab e_tab;
        6   Begin
        7  Select empno bulk collect into empno_tab FROM emp;
        8  FOR i in empno_tab.FIRST..empno_tab.LAST 
        9  LOOP
       10   dbms_output.put_line(empno_tab(i));
       11   Select deptno into v_deptno
       12   FROM emp
       13    where empno=empno_tab(i);
       14   END loop;
       15  END;
       16  /
       Select deptno into v_deptno
                          *
      ERROR at line 11:
      ORA-06550: line 11, column 21:
      PLS-00642: local collection types not allowed in SQL statements
      Any other way to do the same.

      Twinkle
        • 1. Re: PLS-00642: local collection types not allowed in SQL statements
          Prathamesh
          When you use this method ...

          type vdeptno is table of number;
          v_deptno vdeptno;

          You need to allocate space to the object prior to inserting into it , (use .EXTENDS for this). Or you need to initialize it while declaring it.


          or try the following

          SQL> declare
          2 type vdeptno is table of number;
          3 v_deptno vdeptno;
          4 TYPE e_tab is table of PLS_INTEGER INDEX BY PLS_INTEGER;
          5 empno_tab e_tab;
          6 Begin
          7 Select empno bulk collect into empno_tab FROM emp;
          8 FOR i in empno_tab.FIRST..empno_tab.LAST
          9 LOOP
          10 dbms_output.put_line(empno_tab(i));
          11 Select deptno bulk collect into v_deptno
          12 FROM emp
          13 where empno=empno_tab(i);
          14 END loop;
          15 END;
          16 /

          Edited by: user9276238 on Jun 14, 2010 3:26 AM
          • 2. Re: PLS-00642: local collection types not allowed in SQL statements
            Prazy
            Twinkle,

            I really could not able to understand what you are trying to achieve. Anyways here is what you are looking for.
            declare
            type vdeptno  is table of number;
            v_deptno vdeptno := vdeptno();
            TYPE e_tab is table of  PLS_INTEGER INDEX BY PLS_INTEGER;
            empno_tab e_tab;
             Begin
            Select empno bulk collect into empno_tab FROM emp;
            FOR i in empno_tab.FIRST..empno_tab.LAST
             LOOP
            dbms_output.put_line(empno_tab(i));
            v_deptno.extend;
            Select deptno into v_deptno(i)
            FROM emp
            where empno=empno_tab(i);
            END loop;
            END;
            /
            HTH,
            Prazy
            • 3. Re: PLS-00642: local collection types not allowed in SQL statements
              Twinkle
              Thanks Both of you,
              Actually the requirement is much more complex so while solving it I got confused in basics only.
              Twinkle