I want to use this code in a package:
Declare
TYPE mc01TableTYPE IS TABLE OF Varchar2(4);
v_MC01Table mc01TableTYPE := mc01TableTYPE();
v_ResTable mc01TableTYPE := mc01TableTYPE();
TYPE string_list IS TABLE OF VARCHAR2(100);
Var_All string_list := string_list('var1', 'var2');
BEGIN
-- Fill the collecteion for the attempt
v_MC01Table.Delete;
Select MC01
Bulk Collect Into v_MC01Table
From YW_VGEW_EV
Where PDATE=SysDate
Order by MC01;
-- This makes the errors
Select MC01
Bulk Collect Into v_ResTable
From YW_VGEW_EV
Where PDATE Between Sysdate-1 And SysDate
And MC01 MEMBER OF Var_All;
--And MC01 IN (SELECT MC01 FROM TABLE(Var_All));
-- For watch the result
For i in 1..v_ResTable.Count
Loop
Dbms_Output.Put_Line('v_ResTable('||i||'): '||v_ResTable(i));
End Loop;
End;
The MEMBER OF command gives these errors:
PLS-00642: local collection types not allowed
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR
If I change the MEMBER OF line to this.
And MC01 IN (SELECT MC01 FROM TABLE(Var_All));
then I get these errors:
PLS-00642: local collection types not allowed
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
I have a working solution with the LIKE operator, but I want to know how to use the nested table in this case.