SQL Dynamic binding in a IN list

Question

Curent situation

Currently I have this PL-SQL script with almost fifty words "hard-coded":

DECLARE
categoryToSearch VARCHAR2(16);
BEGIN
categoryToSearch := 'my_category';
FOR my_object IN (SELECT my_field FROM my_schema.my_table
                  WHERE other_field = categoryToSearch
                  AND my_field IN ('WORD_1', 'WORD_2', 'WORD_3', 'WORD_4', 'WORD_5', 'WORD_6')
                  GROUP BY my_field ORDER BY my_field)
LOOP
    -- <loop code>
END LOOP;
-- <code>

This is working (yes I only display 6 words for the example)

What I want

But now I would like to change dynamically this list of words, so I implemented the following:

DECLARE
categoryToSearch VARCHAR2(16);
listOfWords      VARCHAR2(512);
BEGIN
categoryToSearch := 'my_category';
listOfWords      := '''WORD_1'', ''WORD_2'', ''WORD_3'', ''WORD_4'', ''WORD_5'', ''WORD_6''';
FOR my_object IN (SELECT my_field FROM my_schema.my_table
                  WHERE other_field = categoryToSearch
                  AND my_field IN ( listOfWords )        -- I changed this, putting a variable instead of a hard-coded list
                  GROUP BY my_field ORDER BY my_field)
LOOP
    -- <loop code>
END LOOP;
-- <code>

This "compiles" (ho ho ho), I mean no SQL errors. But strange issue, this code do NOT return any rows compare to the first one, like listOfWord became empty, different or badly interpreted, I don't know why.

Questions:

• Why my new version is not working ?
• Why it is working with categoryToSearch and NOT with listOfWords ?
• When using IN ( listOfWord ), could it be any issue with the quotes ? (=> assignment)
• Am I doing correctly the binding ? (eg. using : or @ ?)
• Can I use a USING to bind ? i try but nothing work
  
  

I tried:

FOR my_object IN (SELECT my_column FROM schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN ( :my_list )
                  GROUP BY my_field ORDER BY my_field) USING listOfWords

even

FOR my_object IN ('SELECT my_column FROM schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN (' || listOfWords || ')
                  GROUP BY my_field ORDER BY my_field')

and

FOR my_object IN (EXECUTE IMMEDIATE 'SELECT my_column FROM schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN (' || listOfWords || ')
                  GROUP BY my_field ORDER BY my_field')

and even:

FOR my_object IN (SELECT my_column FROM schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN ( @listOfWords )
                  GROUP BY my_field ORDER BY my_field)

Answer 1

I tried the collection, but I cannot perform any "create or replace" since our DBA does not allow it.

You can use a built-in collection type, such as odcivarchar2list:

DECLARE
schemaToAnalyze  VARCHAR2(16);
categoryToSearch VARCHAR2(16);
listOfWords      SYS.ODCIVARCHAR2LIST;
BEGIN
categoryToSearch := 'my_category';
--schemaToAnalyze  := 'my_schema.my_table';
listOfWords      := SYS.ODCIVARCHAR2LIST('WORD_1', 'WORD_2', 'WORD_3', 'WORD_4', 'WORD_5', 'WORD_6');
FOR my_object IN (SELECT my_field FROM my_table -- schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN ( SELECT * FROM TABLE(listOfWords) )
                  GROUP BY my_field ORDER BY my_field)
LOOP
    dbms_output.put_line(my_object.my_field);
END LOOP;
END;
/

db<>fiddle

Answer 2

You can reproduce the second attempt, which has variable concatenation, through use of SYS_REFCURSOR while keeping the current local variable (listOfWords) within a stored function or procedure such as

CREATE OR REPLACE FUNCTION Fn_Analyze_Schema RETURN SYS_REFCURSOR IS 
  v_recordset SYS_REFCURSOR; 
  v_sql       VARCHAR2(32767);
  listOfWords VARCHAR2(32767) := '''WORD_1'',''WORD_2'',''WORD_3'',....';
BEGIN
  v_sql := 'SELECT my_column 
              FROM schemaToAnalyze
             WHERE other_field = categoryToSearch
               AND my_field IN ('||listOfWords||')
             GROUP BY my_field 
             ORDER BY my_field';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

Then call the following code from SQL Developer's command line

VAR rc REFCURSOR
EXEC :rc := Fn_Analyze_Schema;
PRINT rc

Answer 3

you can try to remove the double claws on your variable assignment listOfwords