How to SELECT from a comma delimited list
January 25th, 2006 By Eddie Awad
HR@XE> create type mytype as table of varchar2(10); 2 / Type created. HR@XE> select * from table (mytype(1,2,'a')) 2 / COLUMN_VALUE --------------------- 1 2 a
Or, instead of creating your own type, you can use a pre-defined one:
HR@XE> select * from table(sys.dbms_debug_vc2coll(1,2,'a')) 2 / COLUMN_VALUE --------------------- 1 2 a
January 25th, 2006 at 8:59 am
but that is not a comma delimited list. That is a collection. Entirely different beast alltogether.
most people would be thinking of a comma delimited list like this:
’1,2,3,4′
that won’t select from ’1,2,3,4′
http://tkyte.blogspot.com/2006/01/how-can-i.html
January 25th, 2006 at 9:33 am
By a comma delimited list, I meant exactly what most people think: a list of values seperated by a comma, like this ’1,2,3,4′ or like this ‘a,b,c,d,’ or like this ’1,a,3,d’….
Yes, you won’t select from ‘1,2,3,4′. But, what I was trying to show here was that, given a comma delimited list, you could transform each list element to a “row” using a collection type and a table function.
January 25th, 2006 at 12:53 pm
One more thing, if you want to bind (or parametrize) the (1,2,’a') in select * from table (mytype(1,2,’a')), in addition to Tom’s example, Francois has another nice one too.