[quote:1d2b187172=“river-wind”]
edit2: I’ve moved the missing objects from the original audit universe into the SQLServer universe, and removed some questionable formatting functions from those objects like “TRIM(TRAILING ‘\0’ FROM CONVERT([table.fieldname], CHAR))”.
[/quote]
Y’all may have opened yourself up to a world of pain by removing the trailing null. I’m in the middle of an auditing project built on top of Oracle and that NULL (and the CLOB field it was buried in) were pure evil.
Link 1
Link 2
And here’s how the problem manifests.
Some reports use multiple universes and some use one universe multiple times (think report tabs). In the audit tables this looks like multiple rows in the audit_detail table with detail_type_id = 2 for one event_id.
So when flattening the table to extract a list of distinct universes involved in one report, you want to end up with a single text string that looks something like “Universe 1, Universe Two, Universe 3”. If you have an embedded NULL still attached to each column value when you concatenate, the data WILL get concatenated, but when you go to display it, every cout, printf, and all other zero terminated array of character routines in the world will stop showing you the results as soon as it encounters that first NULL and your list of 2+ universes suddenly shows only one universe.
You get the same thing if you elect to concatenate the report name (detail_type_id = 8 ) on the end of the folder name (detail_type_id = 43). You wanted “User\save\location\Report.wid” but you’ll see “User\save\location”.
Also, I don’t know about other databases, but if you’ve followed the recommendation and have put your audit tables on some other server you may be using a DBLINK to select the data. Oracle will not let you select a LOB type field across a DBLINK in a useful manner. the conversion from LOB to string must occur on the db instance where the LOB lives.
Also, Oracle limits plain SQL to returning 2,000 bytes (yes, VARCHAR2 can be up to 4000 characters, but you can still only get 2,000 from a LOB). Using PL/SQL, you can return 32k bytes. One BO generated SQL I extracted was well over 60,000 bytes. I had to select it 2,000 characters at a time and copy/paste from Toad.
So yeah, LOBs are evil and BO embedded a trailing NULL at the end of every string. That’s terrific if you’re writing your code in C, you can memcpy into your union struct and presto you have a traditional zero terminated C string. But if you’re living in SQL-land…
Sorry, I’m on an anti-“embedded NULL as a character” in my database rant, but I hope the information proves useful. I lost several days of productivity until I discovered that the dbms_lob.length( “Hello” ) was not the same as dbms_lob.length( “Hello” ) - where that second “Hello” came from a column, not a hard coded string.
kbrazell 
(BOB member since 2003-08-19)