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.
SELECT
...
FROM
...
table(sys.dbms_debug_vc2coll('STU_ADM_PROFESSIONAL%','STU_ONESTOP_%')) ck_role
WHERE
gzrrqap_security_role LIKE CK_role.COLUMN_VALUE
or a mixture of static and wildcard values:
TABLE (sys.dbms_debug_vc2coll ('P09',
'P10%',
'P11%',
'P12%',
'P13%',
'P14%')) CK_value
WHERE
....
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.
