Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List

They say examples are the best way to learn:
Example 1:

Example 2:

Simply put, Oracle has pre-defined types that allow you to dynamically convert a comma-separated list of values into rows. Now you can join with another table or tables in a sql query.
We had a programming task where a list of values was being returned by an api which had to be validated against a database table. The requirement was to check if any values in the delimited list existed in the table and create new rows in the table only for the list items that did not exist. This requirement can now be easily coded by using either of the above system packages.
For Eg. Using the Oracle HR schema, if you have to find out which departments from the list below do not exist in the database
‘ACCOUNTING’, ‘RESEARCH’, ‘SALES’, ‘CUSTOMERSERVICE’, ‘SHIPPING’

The query would be:

Reference:
Extensibility Constants, Types, and Mappings

vivek
newest oldest most voted
Notify of
Arjun
Guest
Arjun

Sir thanks for explaining the usage, just one question is how DBMS_DEBUG_VC2COLL is a TYPE, though it looks like a package more ?