Wednesday, September 9, 2009

SQL Developer: Drill Down Reports

Finally, finally I've figured this out. I've googled "SQL Developer Drillable Reports" to no avail. The solution kept alluding me.

The first result you should get back is one from a fellow Tampan (Tampon?), Lewis Cunningham, from July 2006. OK, it's a bit old (I think it was still called Raptor back then), but I'll give it a try.

In it, Lewis talks about creating additional "pseudo" columns, SDEV_LINK_NAME, SDEV_LINK_OWNER, SDEV_LINK_OBJECT which appear to map to the corresponding columns in DBA_OBJECTS.



I tried that, and got...nothing. I tried changing the alias(es) to match the column I was using, again, to no avail.

Let me back up just a tad, I'm trying to create some reports based on the PLSQL_PROFILER_% tables:

* PLSQL_PROFILER_RUNS
* PLSQL_PROFILER_UNITS
* PLSQL_PROFILER_DATA

It's annoying to have to rewrite the SQL everytime. I did create a @profile script, but I had to pass the RUNID; so first, I had to know the RUNID.

So I took to Twitter as I know Kris Rice hangs out there sometimes.







That was last week, and I have been unable to get this to work. I could have sworn Kris had a good tutorial on it, but I think I confused it with the extensions you can create.

Anyway, I'm at it again tonight and I end up back at the link Kris originally pointed me to. For some reason (cough) I missed this crucial little nugget this first time
(the bind variable is case-sensitive)
Really? Could it be that easy? I UPPERed RUNID and voila! It worked!

To recap, go to the Reports tab, right click on a folder (I have one named "profiler") and select Add Report.



I fill out the Name, Description and Tooltip (optional)



Hit Apply which saves my report. Now I want a report that on PLSQL_PROFILER_UNITS that accepts the RUNID as an IN parameter.

First, create the report:



Go to the Binds tab and fill in the fields



Go to the Advanced tab and fill in the name of the report



Now, select your first report, right click, go to Reports and select the report you just created





Perfect!

Just a small reminder, the bind parameters are CASE SENSITIVE!
at

5 comments:

  1. Alex NuijtenSeptember 10, 2009 at 2:16 AM

    Not sure if it qualifies as a "Drill Down Report",... but I created some reports on PLSQL_PROFILER data some years ago

    ReplyDelete
  2. DomBrooksSeptember 10, 2009 at 4:12 AM

    You know SQL Developer has built-in profiler functionality (right click on package/procedure>profile) hooking into DBMS_HPROF and reporting the results?

    ReplyDelete
  3. oraclenerdSeptember 10, 2009 at 9:50 AM

    alex,

    thanks for the link, i'll check them out. maybe i can "convert" them into sql dev reports.

    chet

    ReplyDelete
  4. oraclenerdSeptember 10, 2009 at 9:51 AM

    dom

    no. thanks for telling me though.

    that's what i get for not reading the entire manual.

    at least i learned 2 new things though:
    1. how to create a drill down report
    2. i can profile anything more easily with sql dev

    ReplyDelete
  5. Anxiety ThoughtsOctober 13, 2017 at 9:54 AM

    Excellent instrunctions. Thanks. In the newer versions of SQL Developer the Advanced Tab is now call Drill Down.

    ReplyDelete