Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List

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

select * from table
   (sys.ODCIVarchar2List('AAA','BBB','CCC'));

COLUMN_VALUE
------------
AAA
BBB
CCC

Example 2:

select column_value 
from table(sys.dbms_debug_vc2coll('Gold', 'Silver', 'Diamond', 'Platinum'))

COLUMN_VALUE
------------
Gold
Silver
Diamond
Platinum

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:

select column_value 
from table(sys.dbms_debug_vc2coll('ACCOUNTING', 'RESEARCH', 'SALES', 'CUSTOMERSERVICE', 'SHIPPING'))
minus
select dname
from dept;

Reference:
Extensibility Constants, Types, and Mappings

vivek
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
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 ?