0

I would like to use input variables in a query. The input variable should be a set.

For example:

This is a working query:

select * from table_1

where key_1 in ('1', '2', '3')

But I would like to replace the set by an input variable

select * from table_1

where key_1 in (:input_set)

Then I would set the input to: :input_set = ('1, 2, 3')

However I cannot find a way to get this working.

sddk
  • 1,115
  • 1
  • 10
  • 20
Nani_Momo
  • 13
  • 2
  • 1
    Please tag RDBMS that you use. – Marko Ivkovic Jul 07 '21 at 13:06
  • 1
    Where does this list come from? There's no such entity as set, but you may use collection types. They should be used in different way and don't allow you to pass them via some "manual input" without special code. – astentx Jul 07 '21 at 13:53

1 Answers1

0

Thanks OldProgrammer.

I used a part of the answer from: Oracle: using IN clause with text field?

select * from table_1
where key_1 in (
when pzgr_id in (
            select  regexp_substr(str, '[^,]+',1,level)
            from    (select :input_set str from dual)
            connect by level <= regexp_count(str,'[^,]+')
)

Using the input set '1,2,3'. Important not to use spaces between the 1, 2, 3.

Nani_Momo
  • 13
  • 2