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)

use parenteshis ar_colum(0)... and of course it has to be filled first — Jorge Campos
– Jorge Campos, Commented Jan 8, 2014 at 23:36
lol .. I want something dynamic. I can't type in all the array elements while writing the script. — Em Ae
– Em Ae, Commented Jan 8, 2014 at 23:42
I don't follow... Can you post just a sample example? And the portion of the code you are trying to use it? — Jorge Campos
– Jorge Campos, Commented Jan 8, 2014 at 23:43
possible duplicate of Need help coming up with a query or an alternate solution — Justin Cave
– Justin Cave, Commented Jan 8, 2014 at 23:52
This appears to be a duplicate of a question you asked a handful of minutes ago. — Justin Cave
– Justin Cave, Commented Jan 8, 2014 at 23:52

Jon Heller · Accepted Answer · 2014-01-13 05:40:22Z

3

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

answered Jan 13, 2014 at 5:40

Jon Heller

36.9k6 gold badges84 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use array as bind variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related