Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List
They say examples are the best way to learn: Example 1:
|
1 2 3 4 5 6 7 8 |
select * from table (sys.ODCIVarchar2List('AAA','BBB','CCC')); COLUMN_VALUE ------------ AAA BBB CCC |
Example 2:
|
1 2 3 4 5 6 7 8 9 |
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 Read more about Oracle SQL Tip: Using dbms_debug_vc2coll and sys.ODCIVarchar2List[…]