Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Saturday, October 21, 2006

Binding list variable

Today I was working with XMLType, and as a side effect I found interesting solution to transform list of values contained in single string to rows. You can find some solutions on Tom Kyte blog. But my solution is new approach that uses xmlType and xmlSequence
SQL> var list varchar2(200)
SQL> exec :list := '2,4,6,8,10,34,33';

PL/SQL procedure successfully completed.

SQL> select items.extract('/l/text()').getStringVal() item
  2  from table(xmlSequence(
  3    extract(XMLType('<all><l>'||
  4      replace(:list,',','</l><l>')||'</l></all>')
  5          ,'/all/l'))) items;

ITEM                                                                            
--------------------------------------------------------------------------------
2                                                                               
4                                                                               
6                                                                               
8                                                                               
10                                                                              
34                                                                              
33                                                                              

7 rows selected.
Simple! Isn’t it?
Cheers, Paweł

3 comments:

Joaquin said...

Hey, clever indeed!
I've never put any attention to XML on Oracle, seems like I will be doing it from some time now on.

Thanks for sharing. I have included you on my blog list.

Dhina said...

Hey Dude
your solution is Mind blowing.
I was breaking my head to solve this problem. your Idea is Awesome.
It's really cool.

Antoello said...

Thanks, I can't say anything else.
Great!

 

Copyright © Paweł Barut
Printing from DOS to USB Printer