1

I want to create a procedure which accepts an array list for IN operator.

For eg:

Create or Replace Procedure Test (a Arraylist)
{
Select * from table1 where col1 IN (Arraylist)
}

If the procedure is called using two parameters then the query should be

Select * from table1 where col1 IN (val1,val2)

If the procedure is called using one parameter then the query should be

Select * from table1 where col1 IN (val1)
APC
  • 144,005
  • 19
  • 170
  • 281
user2190186
  • 23
  • 1
  • 6

1 Answers1

4

Create a collection type:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(100);
/

Then you can pass it to a procedure and use the MEMBER OF operator (rather than the IN operator):

CREATE PROCEDURE Test (
  in_list     IN  stringlist,
  out_results OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN out_results FOR
  SELECT *
  FROM   your_table
  WHERE  your_column MEMBER OF in_list;
END;
/

If you are connecting from an external language then you can quite easily pass an array to the procedure (Java example) or you can create the list in PL/SQL or from a delimited list.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • how is this procedure called from java ? – user2190186 Apr 03 '17 at 09:34
  • @user2190186 See the linked example in my answer. Or [this](http://stackoverflow.com/questions/37160300/how-to-pass-list-from-java-to-oracle-procedure/37161584#37161584) or [this](http://stackoverflow.com/a/42201985/1509264). – MT0 Apr 03 '17 at 09:39