how to convert condition IN list into string oracle using parameter plsql?

Question

I need to convert a condition IN list to a list of string using parameter.

from:

:p_parameter in ('a','b','c')

to:

:p_parameter = (a,b,c)

For example select kod from dual where kod in(:p_parameter)

if the input is :p_parameter in (a,b,c) then the output show

kod
------
a
b
c

Anyone got the idea?

Answer 1

Maybe LISTAGG will help you

    SELECT col1
           LISTAGG(p, ',') WITHIN GROUP (ORDER BY p) AS p
    FROM   p_tgable
    WHERE  p in ('a','b','c')
    GROUP  BY
           col1;

Answer 2

Can be done with Nested Table collection type.

First declare a custom nested type, let's name it STRINGS_TYPE:
create type STRINGS_TYPE AS TABLE OF VARCHAR2(100);

Then you can compare collections for equality, for example this will return 1 because these two collections are equal:

select 1 from dual 
where STRINGS_TYPE('a', 'b' , 'c' ) = STRINGS_TYPE('a', 'b', 'c')

Order does not matter, collections are equal, returns 1:

select 1 from dual 
where STRINGS_TYPE( 'b','a',  'c' ) = STRINGS_TYPE('a', 'b', 'c')

These two collections are not equal, nothing is returned:

select 1 from dual 
where STRINGS_TYPE( 'x','y', 'z', 'a', 'b', 'c' ) = STRINGS_TYPE('a', 'b', 'c')

 

In your code bind :p_parameter parameter like this:

String[] pArray = new String[]{"a", "b", "c"};

String selectSQL = "select 1 from dual where  :p_parameter = STRINGS_TYPE('a', 'b', 'c')";

Connection connection = database.getConnection();  

oracle.jdbc.OraclePreparedStatement preparedStatement = (oracle.jdbc.OraclePreparedStatement )connection.prepareStatement(selectSQL);

oracle.sql.ArrayDescriptor descriptor =  oracle.sql.ArrayDescriptor.createDescriptor("YOUR_SCHEMA.STRINGS_TYPE", connection);
oracle.sql.ARRAY pOracleARRAY = new oracle.sql.ARRAY(descriptor, connection, pArray);
preparedStatement.setARRAY(1, pOracleARRAY);

resultSet = preparedStatement.executeQuery();
// ...