Querying the OSB Reporting Provider table

We are using the OSB JMS Reporting Provider to track messages.
We must build custom reports to track the activity within a range of time.

The only problem is that the key-value pairs are stored as SEMICOLON (;) separated lists key1=value1;key2=value2....

Nothing is easier that writing SQL queries supported by PL/SQL functions.

Here how to create the function and types needed:
http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i

if you run
select tokenize('key1=value1;key2=value2', ';') from dual;

you get the list
GM.TOKEN_LIST('key1=value1','key2=value2')

Since my PL/SQL skills are very basic, I will follow a pure SQL Functions approach:


WITH csv_data AS (SELECT '&your_csv_string' AS CSV ,'[^ |;]+' REGEX FROM DUAL)

SELECT regexp_replace(TRIM(REGEXP_SUBSTR(A.CSV, A.REGEX, 1, LEVEL)),'[^[:print:]]', '') STR FROM csv_data a CONNECT BY LEVEL <= REGEXP_COUNT(A.CSV, A.REGEX) ORDER BY 1 Honestly I prefer a "poor man's" approach, that I will explain in another post.