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

Question

I want to retrieve data from a table with multiple values in a variable and with where clause on this variable.

My database version is 11.1

CREATE OR REPLACE PACKAGE BODY pr_retrieve_data as
PROCEDURE FETCH_MYTABLE_DETAILS() is

TYPE ACCNT_NUMBER_TYPE IS TABLE OF MYTABLE.MYCOLUMN%TYPE;
L_ACCNT_NUMBER ACCNT_NUMBER_TYPE;

L_ACCNT_NUMBER.EXTEND(3);
L_ACCNT_NUMBER(1) := 1;
L_ACCNT_NUMBER(2) := 2;
L_ACCNT_NUMBER(3) := 3;

FOR indx in (select column1,
                    column2
             from   SOMEOTHERTABLE SOT
             WHERE  SOT.ACCNT_NUMBER IN (SELECT * FROM TABLE(L_ACCNT_NUMBER))) --The code fails here with PLS-00642 error.
LOOP

...

END LOOP;

end FETCH_MYTABLE_DETAILS;
end pr_retrieve_data;

How can I fetch data from SOMEOTHERTABLE with multiple values in a variable and with where clause on this variable?

Answer 1

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