Sometimes you run across the need to perform a LIKE comparison on a value with a wildcard. Simple right?
WHERE gzrrqap_security_role LIKE 'STU_ADM_PROFESSIONAL%'
Now, what if you wanted a list of values to compare to? You can't just list them the way you're used to doing it in a static IN list. What now?
Use the sys.dbms_debug_vc2coll collection to basically perform a collection in the WHERE clause by building a table on the fly in the FROM.
gzrrqap_security_role LIKE CK_role.COLUMN_VALUE
or a mixture of static and wildcard values:
TABLE (sys.dbms_debug_vc2coll ('P09',
AND PROGRAM_LEVEL_2 LIKE CK_value.COLUMN_VALUE)
It's pretty handy and can solve many problems. It simply converts parameters (values) into the "table representation" - basically, the opposite to "pivot" result.