I have a query like this:
SQL> select distinct t1.name from people t1 join who_likes_what t2 on t1.id = t2.people_id join hobbies t3 on t3.id = t2.hobbies_id and t3.text in ('Music', 'Horses') group by t1.name having count (t3.text) = 2;
NAME
--------------------
Bob
Amy
(2 is the number of elements in ('Music', 'Horses') )
1. Is there a way to parametrize the expression list in a single variable?
Having my brain turned off I had thought that if I created a (single) string variable containing the value '('Music', 'Horses')' I'd be done.
I understand why it cannot work, but I don't know how to replace it with a single variable.
I cannot/don't want to use (:var_1, :var_2, ..., :var_n) because the number of elements in the expression list is variable, and I should be using a SQL prepared statement.
In the other thread I have already been advised to use a single string like 'Music~Horses' and the following construct:
select regexp_substr(:set_1 || '~','[^~]+',1,level) txt from dual
but is there a way to achieve it directly?
2. Is there a function that returns the number of elements in an expression list?
(to obtain 2 from the entry for 1.)
Thank you for any suggestion.