Tuesday, February 9, 2010

OBIEE Grabbing the logical SQL

Sometimes you want to “grab” the logical from a dashboard or report without showing it to the user or having to switch on logging.

Step 1: Create you report with an sql view:

image

image

Add a static text box:

image

Add this code from javascript master Joe Betram:

<script type="text/javascript">

// Original code from Joe Betram

// See :http://forums.oracle.com/forums/thread.jspa?messageID=4026864&#4026864
var tds = document.getElementsByTagName('td');
var lSQL = new Array();
for(var td=0;td<tds.length;td++){
if( tds[td].className != 'SqlViewCell' ){
continue;
}
tds[td].style.display = "NONE";
lSQL.push(tds[td].innerHTML);
}
for(var len =0; len < lSQL.length; len++){
document.write("Stored logical SQL in slot " + len + " is: " + lSQL[len] + "<BR>");
}
</script>

Don’t forget to check the contains HTML Markup box:

image

Check the results:
image

With a little bit of tweaking you can use this script to for instance to call a web service or channel the SQL to another logging program.

Till Next Time

2 comments:

rnm1978 said...

Nicely done.

Of course, Usage Tracking also records Logical SQL, so depending on the purpose (not in this case from looking at the forum thread) may be more appropriate for logging Logical SQL. But as you say, it needs switching on first.

Cheers, RNM.

mike said...

“Wow” you are a genius for sure what great ways to get ranked high and obtain good traffic flow from your article. you for sharing your information it was very good reading for sure. I am looking forward to any more of your articles Thank you produce in the near future.

online collaborative