PLSQL script not working with passing multiple values into IN() caluse

Question

I am very new in PLSQL, i want to pass an array of number into IN() clause value, But Sqldeveloper throw following error messages:

Error report -
ORA-06550: line 11, column 60:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 53:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

This is my code :

DECLARE
   TYPE sc IS TABLE OF transactionhistory.NBSUBCOMPANY%TYPE INDEX BY PLS_INTEGER; 
   arr sc;
BEGIN
   arr(0) := 000;
   arr(1) := 111;
   arr(2) := 222;
   arr(3) := 333;

   select count(th.CHCARDNUMBER) as transactions from transactionhistory th INNER JOIN cards ch on ch.NBATTMID=th.NBATTMID where th.dtdate>=to_date('01-oct-2016','dd-mon-yyyy') and th.dtdate<to_date('01-nov-2016','dd-mon-yyyy') 
   and ch.NBSUBCOMPANY IN (select column_value from table (arr))
   and ((th.CHTRANSTYPE in ('2940', '2916', '2941', '2942', '2943', '2944', '2945', '2902', '2917', '2925') and th.NBBASEAMT < 0) or (th.CHTRANSTYPE in ('2922', '2923', '2926', '2950', '2951', '2952', '2953', '2954', '2955') and th.NBBASEAMT > 0) or (th.CHTRANSTYPE in ('1101', '1102', '1104', '1105', '1106', '1107', '1109') and th.BASEII_STATUS = 'C') or th.CHTRANSTYPE not in ('2940', '2916', '2941', '2942', '2943', '2944', '2945', '2902', '2917', '2925', '2922', '2923', '2926', '2950', '2951', '2952', '2953', '2954', '2955', '1101', '1102', '1104', '1105', '1106', '1107', '1109')); 
END;

Please suggest me how can i pass this.

Possible duplicate of [Array in IN() clause oracle PLSQL](https://stackoverflow.com/questions/15515772/array-in-in-clause-oracle-plsql) — XING, Jul 07 '17 at 09:38
@XING thanks for reply but things are different, i already visit the URL, they not passing values into IN() they just print values one by one like a simple iterator on array. But i want to pass whole array into IN(). — Sawai Singh, Jul 07 '17 at 10:26

Sudipta Mondal · Answer 1 · 2017-07-07T10:38:14.520

You will need to have a workaround.

SQL doesn't support local collection types to be used in the way you want.

Sample Procedure below which will help in resolving

CREATE OR REPLACE type TEST_TYPE
IS
    TABLE OF NUMBER;

You will need to create a type in the database for this to work. Can be created inside a package, if you have one.

DECLARE
    TEST_VAR TEST_TYPE := TEST_TYPE();
BEGIN
    TEST_VAR.extend(1);
    TEST_VAR(TEST_VAR.last) := 222;
    TEST_VAR.extend(1);
    TEST_VAR(TEST_VAR.last) := 333;

    FOR rec IN
    (SELECT column_value
    FROM TABLE(TEST_VAR)
    )
    LOOP
        dbms_output.put_line(rec.column_value);

    END LOOP;

END;

Output

222
333

This way, you can use select column_value from table(test_var) for your IN() clause.

Also, you don't necessarily need to follow the extend(i) part. you can simply do the below as well

TEST_VAR TEST_TYPE := TEST_TYPE(222,333);

Have a read - local collection types not allowed in SQL statements

XING · Answer 2 · 2017-07-07T13:38:20.293

You can use MEMBER OF clause. See below:

As @Sudipta mentioned you cannot use a collection decalred in PLSQL block as you are doing, you need to declare it outside of the PLSQL block.

CREATE OR REPLACE TYPE sc IS TABLE OF NUMBER;

and then

DECLARE
  -- TYPE sc IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

   arr   sc:=sc();

   num number;

BEGIN
   arr.extend(4);
   arr(1) := 0;
   arr(2) := 1;
   arr(3) := 2;
   arr(4) := 3;

   Select count(1)
   into num
   from employee 
   -- You can use either commented one or uncommented one. your choice. 
   --where employee_id in (select column_value from table(arr));
   where employee_id member of arr;

END;

PLSQL script not working with passing multiple values into IN() caluse

2 Answers2