Local Collection Types Not Allowed in SQL Statements

local collection types not allowed error in PL/SQL ORA-06550

Try to use cursors and BULK COLLECT instead:
http://www.dba-oracle.com/t_oracle_bulk_collect.htm

should look something like this:

DECLARE
TYPE name_array is array(50) OF varchar2(100);
var_input  name_array; 

cursor c1 is
SELECT owner
  FROM sys.all_objects;

BEGIN
    open c1;
    fetch c1 bulk collect into var_input;
    close c1;

  FOR i IN var_input.FIRST .. var_input.LAST
    LOOP
        dbms_output.put_line(var_input(i));
    END LOOP;
END;

Didn't check the code

local collection types not allowed in SQL statements - Oracle

Until Oracle 11g, if you declare type under the scope of a PLSQL block, it cannot be called in SQL statement within the block and this is the reason you are getting this error:

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

You can create a type outside the SCOPE of PLSQL block and then use it in SQL. See below:

--Created a type of Number assuming ID is number
CREATE OR REPLACE TYPE R05_ID_TYPE IS TABLE OF NUMBER;
/
--Block
DECLARE
    --TYPE R05_ID_TYPE IS TABLE OF R01_TABLE.R01_ID%TYPE;

     R01_IDS                       R05_ID_TYPE;
BEGIN
     SELECT R1.R01_ID AS R01_ID
     BULK COLLECT INTO R01_IDS
       FROM R01_TABLE R1,
            R05_TABLE R5
      WHERE R1.R01_ID = R5.R05_R01_ID_FK
            AND R5.R05_STATUS = 'D'
            AND R5.R05_DATE_TIME_CAPTURED <= TRUNC (SYSDATE) - 1095
            AND R1.R01_ID NOT IN (
                    SELECT R01.R01_ID
                      FROM R01_TABLE R01,
                           R05_TABLE R05
                     WHERE R05.R05_STATUS != 'D'
                           AND R01.R01_ID = R05.R05_R01_ID_FK);

     DBMS_OUTPUT.PUT_LINE (R01_IDS.COUNT);

     FOR INDX IN 1 .. R01_IDS.COUNT
     LOOP
          DBMS_OUTPUT.PUT_LINE (R01_IDS (INDX) );
          INSERT 
                INTO R01_TABLE_ARCHIVED
                     (R01_ID_TYPE,
                      R01_IDENTITY_NUMBER,
                      R01_PASSPORT_COUNTRY,
                      R01_DATE_TIME_CAPTURED)
               SELECT RR1_R01_ID_TYPE,
                      RR1_R01_IDENTITY_NUMBER,
                      RR1_R01_PASSPORT_COUNTRY,
                      RR1_R01_DATE_TIME_CAPTURED
                 FROM (SELECT R01_ID_TYPE,
                              R01_IDENTITY_NUMBER,
                              R01_PASSPORT_COUNTRY,
                              R01_DATE_TIME_CAPTURED
                         FROM R01_TABLE
                        --Using the collection in SQL statement
                        WHERE R01_ID IN (Select column_value from table(R01_IDS) );
     END LOOP;
COMMIT;
END;

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

Create the type as schema object. However, you cannot inherit data type from table (AS TABLE OF MYTABLE.MYCOLUMN%TYPE):

CREATE OR REPLACE TYPE ACCNT_NUMBER_TYPE AS TABLE OF NUMBER;

Note, in newer Oracle versions you can use local collection types also in SQL. Feature was introduced in version 12.1.

In your particular case you can also use

...
WHERE SOT.ACCNT_NUMBER MEMBER OF L_ACCNT_NUMBER

Getting local collection types not allowed in SQL statements even though I use table operator

Type should be created at SQL level and then used in your PL/SQL procedure. Something like this (based on Scott's schema):

SQL> set serveroutput on
SQL> create or replace type emp_tab as table of number;
  2  /

Type created.

SQL> create or replace procedure purge_table is
  2      arr_ids    emp_tab := emp_tab();
  3      v_deleted_row_Count number;
  4  begin
  5     select distinct (s.empno) bulk collect
  6       into arr_ids
  7       from emp s where deptno = 10;
  8
  9     select count(*)
 10        into v_deleted_row_count
 11        from emp s where s.empno in (select * from table(arr_ids));
 12
 13     dbms_output.put_line('Number = ' || v_deleted_row_count);
 14  end;
 15  /

Procedure created.

SQL> exec purge_table;
Number = 3

PL/SQL procedure successfully completed.

SQL>

Oracle PL/SQL local collection type not allowed error

I would like to store a SELECT result into a variable, and use the
variable in a WHERE clause of another select statement. Is that
possible

I am not sure what you want to achieve but looking at you statements above I give an example below:

CREATE OR REPLACE PROCEDURE TEST IS
    CURSOR c1 IS 
    SELECT * FROM Table1;

    TYPE num_arr_type IS TABLE OF NUMBER(5);

    var1  num_arr_type;

    i_rec Table1%ROWTYPE;
    v_num number;

BEGIN
     --- This is how you store value to a collection
    SELECT num
      bulk collect into  var1
    FROM Table3;

     --- Loop to get individual records from your collection
   For i in 1..var1.count
    loop    
       SELECT a.num  
         into  v_num             
            FROM Table2 a
           --This is how you pass the value stored in your collection one by one in loop.
           WHERE a.cod_agt = var1(i);   

       dbms_output.put_line(v_num);  
    end loop;             

END TEST;

local collection types not allowed in SQL statements

To achive that you should use something like:

CREATE OR REPLACE TYPE type_record_1...
/

CREATE OR REPLACE TYPE type_table_1 AS TABLE OF type_record_1;
/

Oracle does not allow types declared in package to be casted as table.
I talk about Oracle until 11, still not check 12c new features :(.

---

Related Topics