[Solved] BO XIr3.1 Audit issue provokes SQL question

system · February 18, 2013, 11:10pm

I’m stuck on a SQL while working on an audit report.

We have reports that draw data from multiple universes. In the AUDIT_DETAIL table I see multiple rows for a single EVENT_ID where DETAIL_TYPE_ID = 2. And that part is just fine, it’s expected.

But when it comes to querying that I run into a spot of bother that I’d love an extra pair of eyes to look at please.

SELECT event_id
      ,DBMS_LOB.SUBSTR( Detail_Text, 100, 1 ) AS name_name
      ,ROW_NUMBER( ) OVER (PARTITION BY event_id ORDER BY DBMS_LOB.SUBSTR( Detail_Text, 100, 1 )) AS rnum
FROM   php_boe_audit.AUDIT_DETAIL
WHERE  detail_type_id = 2 AND event_id = 10401996175157592097

The event_id filter on this is simply to reduce the results to a bite sized chunk for development. In real life I’ll be using an event data filter to collect only “new” events.

This SQL gives me two rows back (bite sized, see?)


event_id               name_name      rnum
10401996175157592097	Universe One	1
10401996175157592097	Universe Two	2

Now, for my output, I want the two universes to be listed as a single row, not two. Also, while this example has two universes, others have more, so my SQL solution has to be generic.

WITH got_rnum
     AS (SELECT '10401996175157592097' AS event_id, 'Universe One' AS name_name, 1 AS rnum FROM DUAL
         UNION ALL
         SELECT '10401996175157592097', 'Universe Two', 2 FROM DUAL)
SELECT event_id, LTRIM( SYS_CONNECT_BY_PATH( name_name, ', ' ), ', ' ) AS universe_list
FROM   got_rnum
WHERE  CONNECT_BY_ISLEAF = 1
START WITH rnum = 1
CONNECT BY event_id = PRIOR event_id AND rnum = PRIOR rnum + 1;

This SQL substitutes the output of the first as the sub query (I’m working up to the part that makes me scratch my head) and the results are exactly what I want to see.


event_id               universe_list
10401996175157592097	Universe One, Universe Two

If instead of dropping the hard coded results of the first SQL into the second, I drop in the SELECT statement itself.

WITH got_rnum
     AS (SELECT event_id
               ,DBMS_LOB.SUBSTR( Detail_Text, 100, 1 ) AS name_name
               ,ROW_NUMBER( ) OVER (PARTITION BY event_id ORDER BY DBMS_LOB.SUBSTR( Detail_Text, 100, 1 )) AS rnum
         FROM   php_boe_audit.AUDIT_DETAIL
         WHERE  detail_type_id = 2 AND event_id = 10401996175157592097)
SELECT event_id, LTRIM( SYS_CONNECT_BY_PATH( name_name, ', ' ), ', ' ) AS universe_list
FROM   got_rnum
WHERE  CONNECT_BY_ISLEAF = 1
START WITH rnum = 1
CONNECT BY event_id = PRIOR event_id AND rnum = (PRIOR rnum) + 1;

I get the following output


event_id               universe_list
10401996175157592097	Universe One

This is neither what I expected, nor what I wanted. But I suspect somehow it’s exactly what I asked for. Can a fresh pair of eyes see what I’m doing wrong and help me write the SQL in a way that it gives me what I want?

Please and thank you!

EDIT:
I even tried a rewrite of the final SQL and all it did was take longer to run (13 minutes instead of 3 minutes)

SELECT event_id, LTRIM( SYS_CONNECT_BY_PATH( name_name, ', ' ), ', ' ) AS universe_list
FROM   (SELECT event_id
              ,DBMS_LOB.SUBSTR( Detail_Text, 100, 1 ) AS name_name
              ,ROW_NUMBER( ) OVER (PARTITION BY event_id ORDER BY DBMS_LOB.SUBSTR( Detail_Text, 100, 1 )) AS rnum
        FROM   php_boe_audit.AUDIT_DETAIL
        WHERE  detail_type_id = 2 AND event_id = 10401996175157592097)
WHERE  CONNECT_BY_ISLEAF = 1
START WITH rnum = 1
CONNECT BY event_id = PRIOR event_id AND rnum = ( PRIOR rnum ) + 1;

Giving me the same undesirable output.

kbrazell (BOB member since 2003-08-19)

system · February 19, 2013, 5:26am

I believe you just want to flatten the universe list into one line per event. The following SQL (oracle) should flatten it & give a comma-separated universe list for you and the number of universes in the list has no impact on it working

Select Event_Id
      ,rtrim (xmlagg (xmlelement (e, Detail_Text|| ', ')).extract ('//text()'), ', ') as Universe_List
From   php_boe_audit.AUDIT_DETAIL 
Where  Detail_Type_Id = 2 
  And  Event_Id       = 10401996175157592097 
Group by Event_Id

I hope that helps

buzz (BOB member since 2005-08-12)

system · February 19, 2013, 4:23pm

Yes, flattening the results is my goal. Although in the mean time I’d also like to understand why my other approaches are not working - it’s some disconnect in my understanding of SQL.

Alas, the output from the SQL you gave was this…

	10401996175157592097

The column I am looking for is a complete blank in that output. Does that CLOB contain XML? I’ve not used the Oracle XML routines before.

That CLOB (and others like it) have been a long standing source of pain-in-the-tuckus for me.

kbrazell (BOB member since 2003-08-19)

system · February 19, 2013, 4:58pm

OK, I do have some new info…

Building a test case on another server the code works. The server where it works is running Oracle 11.2.something

The server where it does not work is version 11.1.0.6.0

I don’t know that this is the source of the problem, but it is certainly a difference (over which I have zero control).

EDIT: Nope, it’s not the version. It’s the data in the CLOB column. I can reproduce the error in both Oracle versions.

If I create a test table and manually insert data, my code works. But if I create my test table as a subset of the original, I can reproduce the error

CREATE TABLE AUDIT_DETAIL4 AS
SELECT *
FROM AUDIT_DETAIL
WHERE detail_type_id = 2 AND event_id = 10401996175157592097;

And then I use AUDIT_DETAIL4 as my source and the error shows up.

What the heck is SAP sticking in there? It’s not XML and it’s not a generic string (tests with generic strings prove the code works).

EDIT2: Ugh. I found something. Link. It’s the CLOB. SYS_CONNECT_BY_PATH does not support CLOBs. It’s not specifically my example, but I already know that CLOBs are evil.

kbrazell (BOB member since 2003-08-19)

system · February 19, 2013, 10:05pm

Aha! Solved.

It turns out that it’s a sum of several issues.

First: CLOBS are evil.
Second: SAP is including a trailing NULL in the text string they insert into the CLOB
Third: SYS_CONNECT_BY_PATH is not strictly a string function, so it concatenates the ENTIRE field. This is not bad in itself, but it propagates the previous two evils.

So as I select the data, each column is displayed individually from the SELECT. When the string that is my concatenated comma separated list of Universes is shown, it displays the field up to the first NULL, which terminates the string. So all subsequent parts of the string are hidden.

So using the contributed SQL (which was the best looking) the working version is this.

SELECT event_id
      ,RTRIM( XMLAGG( XMLELEMENT( e, SUBSTR( Detail_Text, 1, LENGTH( detail_text ) - 1 ) || ', ' ) ).EXTRACT( '//text()' )
             ,', ' )
          AS universe_list
FROM   php_boe_audit.AUDIT_DETAIL
WHERE  Detail_Type_Id = 2 AND Event_Id = 10401996175157592097
GROUP BY Event_Id

Getting it to work with the original version SQL looks like this

WITH got_rnum
     AS (SELECT event_id
               ,DBMS_LOB.SUBSTR( Detail_Text, LENGTH( Detail_Text ) - 1, 1 ) AS name_name
               ,ROW_NUMBER( ) OVER (PARTITION BY event_id ORDER BY DBMS_LOB.SUBSTR( Detail_Text, 100, 1 )) AS rnum
         FROM   php_boe_audit.AUDIT_DETAIL
         WHERE  detail_type_id = 2 AND event_id = 10401996175157592097)
SELECT event_id, LTRIM( SYS_CONNECT_BY_PATH( name_name, ', ' ), ', ' ) AS universe_list
FROM   got_rnum
WHERE  CONNECT_BY_ISLEAF = 1
START WITH rnum = 1
CONNECT BY event_id = PRIOR event_id AND rnum = PRIOR rnum + 1;

In production I’ll use the one that has the best plan and actual execution time.

EDIT: I discovered in various cut/paste exercises that Toad sometimes objects to text that is pasted from external sources (it might be a multi-byte character issue?) and the work around that I found was to paste-special text only into MS Word and the re-copy it. Using TextPad or even Notepad to paste/copy did not strip the “invalid character” errors.

kbrazell (BOB member since 2003-08-19)