-1

How to bind arrays in a variable in PL/SQL.

For Example i have below array:

array_col3 = {1,2,3,4,5,6,7}

now i want to select from a table based on the array.

select * from tabA where col3 in (select * from (:array_col3));
Jyoti mishra
  • 597
  • 4
  • 16
Abhi85
  • 17
  • 1
  • 5
  • Possible duplicate of [Array in IN() clause oracle PLSQL](https://stackoverflow.com/questions/15515772/array-in-in-clause-oracle-plsql) – XING Jul 12 '17 at 06:51
  • It appears you are defining the array in some language other than PL/SQL and want to pass it as a bind variable. If that is the case you need to use dynamic SQL. [This other SO thread has the solution you need](https://stackoverflow.com/a/8969591/146325). – APC Jul 12 '17 at 17:44

3 Answers3

1

Hope this below snippet helps.

SET serveroutput ON;
DECLARE
  lv sys.odcivarchar2list:=sys.odcivarchar2list('1','2','3','4');
  lv1 sys.odcivarchar2list;
BEGIN
  SELECT * BULK COLLECT
  INTO lv1
  FROM
    ( SELECT LEVEL LVL FROM DUAL CONNECT BY LEVEL < 10
    )A
  WHERE A.lvl IN
    (SELECT COLUMN_VALUE FROM TABLE(LV)
    ) ;
  dbms_output.put_line(lv1.COUNT);
END;

###############################OUTPUT##########################################

anonymous block completed
4

###############################OUTPUT##########################################
Avrajit Roy
  • 3,233
  • 1
  • 13
  • 25
1

You need a table() expression:

select * from tabA where col3 in (select * from table((:array_col3)));

or alternatively the member of operator:

select * from tabA where col3 member of :array_col3;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
0

In plsql its not so simple. You have to use types, have a look at dbms_utility package .

Example: 

Va dbms_utility.number_array;
 Va(1) := 1;
 Va(2) := 2;
Leandro Franciscato
  • 26
  • 5