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

The query would be:

Extensibility Constants, Types, and Mappings


Oracle Fusion Middleware Architect at FUSION Applied
Vivek is an Oracle Certified Fusion Middleware architect. He has over 18 years of experience implementing custom software solutions in the Financial, Health, and Government sectors. He is one of the founders of Fusion Applied.

Leave a Reply

1 Comment threads
0 Thread replies
Most reacted comment
Hottest comment thread
1 Comment authors
Arjun Recent comment authors
newest oldest most voted
Notify of

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