how to use array as bind variable

Question

I have following array, which will be populated based on some external criteria.

TYPE t_column IS TABLE OF TABLE_1.COLUMN_1%TYPE INDEX BY PLS_INTEGER;
ar_column t_column;

Now, I want to use this ar_column into another cursor, how can i bind it ?

I am looking at something like select * from table1 where column in (ar_colum[0],ar_colum[1] ...); (its a pseudo code)

Answer 1

Using PL/SQL collections in SQL statements requires a few extra steps. The data type must be created, not simply declared as part of a PL/SQL block. Associative arrays do no exist in SQL and must be converted into a nested table or varray at some point. And the TABLE operator must be used to convert the data.

create table table1(column_1 number);
insert into table1 values (1);
commit;

create or replace type number_nt is table of number;

declare
    ar_column number_nt := number_nt();

    v_count number;
begin
    ar_column.extend; ar_column(ar_column.last) := 1;
    ar_column.extend; ar_column(ar_column.last) := 2;

    select count(*)
    into v_count
    from table1
    where column_1 in (select * from table(ar_column));

    dbms_output.put_line('Count: '||v_count);
end;
/

Count: 1