Yet another Oracle XML challenge

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 result:

The standard CLOB limit of 4000 characters applies to this solution.