In clause in oracle

[Shailesh Narkhede]

In clause in oracle takes 1000 parameter maximum.
If we give more than that then it is giving exception.

I do not want to use select query inside the IN clause.
Actually I am calling one method. to which I am passing String Array of 1500 PK that I want to use in IN clause.that I am doing by FOR loop and appending to query.
but I do not want change signature of my method.

Please tell us alternative solution.

Thanks in advance.

[Sridhar Sreenivasan]

What version of Oracle DB are you using?
"I do not want to use select query inside the IN clause"
Any specific reason of not wanting to use such a solution?

If it's version 11, then based on the information you've provided an alternative solution is to use a select query inside the IN clause as follows:

Assuming the current sql query you have is as follows:
select * from tableA where columnA in(<array of string>);

The above can be modified to
A type "string_type" can be defined as a table of varchar2 in the Oracle DB.
Then the array of string can be assigned to the type variable "string_type_var".
select * from tableA where columnA in(select column_value from table(:string_type_var));

The above solution is efficient and clean.

[Shailesh Narkhede]

Thanks for replay Sridher.
I got it.