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

vivek

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.
vivek

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz