By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table(other_field, my_field) as
select 'my_category', 'WORD_2' from dual
union all
select 'my_category', 'WORD_4' from dual
union all
select 'my_category', 'WORD_7' from dual
union all
select 'other_category', 'WORD_1' from dual
4 rows affected
-- your oroginal attempt, modified to run
DECLARE
schemaToAnalyze VARCHAR2(16);
categoryToSearch VARCHAR2(16);
listOfWords VARCHAR2(512);
BEGIN
categoryToSearch := 'my_category';
--schemaToAnalyze := 'my_schema.my_table';
listOfWords := '''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 ( listOfWords )
GROUP BY my_field ORDER BY my_field)
LOOP
dbms_output.put_line(my_object.my_field);
END LOOP;
END;
/
1 rows affected
-- showing it works with a single 'value'
DECLARE
schemaToAnalyze VARCHAR2(16);
categoryToSearch VARCHAR2(16);
listOfWords VARCHAR2(512);
BEGIN
categoryToSearch := 'my_category';
--schemaToAnalyze := 'my_schema.my_table';
listOfWords := 'WORD_2';
FOR my_object IN (SELECT my_field FROM my_table -- schemaToAnalyze
WHERE other_field = categoryToSearch
AND my_field IN ( listOfWords )
GROUP BY my_field ORDER BY my_field)
LOOP
dbms_output.put_line(my_object.my_field);
END LOOP;
END;
/
1 rows affected
dbms_output:
WORD_2
-- with a built-in collection
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;
/
1 rows affected
dbms_output:
WORD_2
WORD_4