Friday, April 13, 2012

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:

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

you get the list

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.

No comments: