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

Question

I have a procedure and I am trying to use a collection in where statement.

 procedure purge_table is

    type t_ids is table of number;
    arr_ids    t_ids ;

   begin  
   select distinct (s.id) bulk collect
              into arr_ids    
        from students s;

   select count(*)
      into v_deleted_row_count
      from students s
      where s.id in (select * from table(arr_ids));

   end;

I am getting "local collection types are not allowed i SQL statements" for the line containing where statement. I searched for the error as far as I understand my syntax is correct but I dont understand what does "Assuming that your collection is defined in SQL, not just in PL/SQL, you can use the TABLE operator " mentioned in the accepted answer here: Array in IN() clause oracle PLSQL.

Also the accepted answer here

https://dba.stackexchange.com/questions/141325/how-can-i-use-an-array-variable-inside-the-in-operator-for-oracle-sql

suggests the same thing as I did.

I guess it has to be related to defining a collection in SQL. Could you please help me with that?

Answer 1

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>