They say examples are the best way to learn:
select * from table (sys.ODCIVarchar2List('AAA','BBB','CCC')); COLUMN_VALUE ------------ AAA BBB CCC
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;
Extensibility Constants, Types, and Mappings