BusinessObjects-How to find which report is running from database session

Sometimes you see very large SQL expressions which are killing your database systems. With audit reports you can find which reports have run after they finished. But with this little code you can directly find the user, the report and the universe from your database directly on demand.

http://www.dallasmarks.org/blog/2011/10/tips-and-tricks-identifying-business-objects-queries-using-end_sql/

Just put this lines to your universe parameters "END_SQL" line:
/* @Variable('UNVNAME') - @Variable('BOUSER') - @Variable('DOCNAME') */

Thats it. Your query will look like this:

Your dba will appreciate you.

ORACLE-Transpose Column Value to Row in Oracle

You have a table with various columns. One of your column is a text column where the values are seperated with semicolon (";"). You want to transpose this column values into rows.

Generally we use instr function for this kind of purposes. But there is a another way to do this.

Let's suppose that your select statement is like this:

SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL

the output:

and our new statement:

WITH T AS(
SELECT 'test;tester;siteconfidence;' TXT FROM DUAL
union all
SELECT 'AA;BB;TT;' TXT FROM DUAL)
SELECT TXT FROM T
MODEL
RETURN UPDATED ROWS
PARTITION BY(ROWNUM RN)
DIMENSION BY (0 POSITION)
MEASURES (TXT,NVL(LENGTH(REGEXP_REPLACE(TXT,'[^;]+','')),0) NB_MOT)
RULES
(TXT[FOR POSITION FROM 1 TO NB_MOT[0] INCREMENT 1] = REGEXP_SUBSTR(TXT[0],'[^;]+',1,CV(POSITION)))

and the output:
 

Tweet