Friday, December 4, 2015

Using sys.dbms_debug_vc2coll Collections

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.