I have a query that contains a clause: field in :tags_set
(the field contains string values)
I would like test in sqlplus.
I have found this article https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:113012348062 explaining how to define variables in sqlplus.
However, my variable needs to contain something like ('xyz', 'abc')
I have tried
SQL> variable tags_set varchar2
SQL> exec :tags_set := '('Music')';
BEGIN :tags_set := '('Music')'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 23:
PLS-00103: Encountered the symbol "MUSIC" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol "* was inserted before "MUSIC" to continue.
;
Then, I did some search and found: Special Characters in Queries
Consequently, I changed the previous to:
SQL> exec :tags_set := '(\'Music\')';
BEGIN :tags_set := '(\'Music\')'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "MUSIC" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
and finally
SQL> exec :tags_set := '{('a')}';
BEGIN :tags_set := '{('a')}'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "A" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol "* was inserted before "A" to continue.
What is the way to make it working? (I know that I can pass something like 'xyz~abc' and process it inside the query)
Thanks in advance.