If you have read my previous tip about Oracle XML API
You would learn to use XML API as nice quick’n’dirty replacement to tons of decode functions.
In this challenge I got on Oracle 9i (yes they still exist in production 2013).
- We have a string such as ‘123#124#122′, we need to split the different values into record , sort them and map them from a Value-key pair table in an Oralce 9i database.
- And for reasons of implementation it has to be done in pure SQL, not PL/SQL.
- and it is best to avoid creating any stored procedures or functions.
A more elaborate description of the problem : Splitting a delimited text with Oracle SQL into records, and sorting them out.
This is a real challenge in the production environment I’m working in.
Googling around the problem , you will see that most people sort this with Regular expressions, which is not implemented in Oracle9i !
So I turned to tweaking my previous technique which is using XML.
The main problem now is converting this String delimited : ‘123#124#122′ into ‘<records> <record>123</record> <record>124</record> <record>122</record> </records>’ !
Which turns out to be very simple using the Oracle SQL REPLACE function : ‘<apnlist><apn>’||replace(‘123#124#122′,’#’,'</apn><apn>’)||'</apn></apnlist>’
So the final product is :
WITH XMLDATA as ( select xmlType( '<apnlist><apn>'||replace('123#124#122','#','</apn><apn>')||'</apn></apnlist>' ) as DAYS from dual ) SELECT rownum, extractValue(VALUE(p),'//apn/text()') "apn" FROM XMLDATA,TABLE(XMLSEQUENCE(EXTRACT(DAYS, '/apnlist/*'))) p order by to_number(extractValue(VALUE(p),'//apn/text()')) ;
The standard CLOB limit of 4000 characters applies to this solution.