ThatJeffSmith

How To Add Custom Actions To Your User Reports

I’ve shown you and more than a few customers how to customize your Monitor Sessions page/report. If you need a refresher, that’s here.

But basically it comes down to this:

  1. Copy the report from the Database Administration section of the Reports panel
  2. Paste it to the User Defined Reports section
  3. Edit to your heart’s delight

But.

Some of you have noticed that when you do this, you lose the ability to kill and trace sessions on a right-click.

Right-click, and try not to make it personal

Right-click, and try not to make it personal

So this post is all about how to

Add Context Menu ‘Actions’ to a Report

We’ll start with doing the ‘Kill’ stuff.

You need to save the report out to an XML file, edit the file, then add the report back to SQL Developer.

Save to an XML file, then open it with your favorite Editor

Save to an XML file, then open it with your favorite Editor

Now you need to add a section to your DISPLAY tag. Your report can have one or more displays, so make sure you add it to the section you want.

Here’s two I’m going to add to a report that has a simple SELECT * FROM GV$SESSION in it:

 <item reload="true" reloadparent="false" removeFromParent="false">
         <title>Kill Session</title>
              <prompt type="confirm">
                   <label>Kill Session?</label>
               </prompt>
               <sql><![CDATA[ALTER SYSTEM KILL SESSION '#SID#, #SERIAL##' IMMEDIATE]]></sql>
            <help>Database Kill -9 You, it's nothing personal, probably</help>
       </item>
     <item reload="true" reloadparent="false" removeFromParent="false">
       <title>Disconnect Session</title>
              <prompt type="confirm">
                   <label>Disconnect Session?</label>
               </prompt>
               <sql><![CDATA[ALTER SYSTEM DISCONNECT SESSION '#SID#, #SERIAL##' POST_TRANSACTION]]></sql>
            <help>Nicer than kill, after your work is done, you go bye-bye</help>
       </item>

And by the way, to keep myself mostly sane, I’m adding this bit of code directly above where I close my DISPLAY tag. But add it wherever works best for you in terms of readability.

Save the report.

Load it back – right click on the User Defined Reports – Open – point to your XML file.

Run it.

Suhweet!

Suhweet!

And what does that do, exactly? Remember, the SQL panel will show you the SQL being sent to the database when you hit ‘OK.’ And if you’re nice, you can build a little message for the HELP as well.

Seems nicer anyway

Seems nicer anyway

How It Works

You can reference column values for the selected row with the #COL# notation. So in my SQL #C-DATA section:

<sql><![CDATA[ALTER SYSTEM DISCONNECT SESSION '#SID#, #SERIAL##' POST_TRANSACTION]]></sql>

I’m passing the Session ID and Serial over to the statement. It’s a string, so I’m quoting it. And GV$SERIAL# has a ‘#’ in the name, so I have to say #SERIAL## to get the value out. I can test this by looking at the ‘SQL’ panel to make sure it’s generating the text correctly.

And don’t add a ‘;’ to your SQL statement. It will break the action.

Another Example

Remember my BEER table? I have a nice report that shows me breweries by countries. I select a country in a chart, then get a nice child report of breweries in that country.

I want to add a context menu to mark a brewery as one that I’ve visited in person.

I’ve added a CHAR column to my BEER table, now I just need to have the report fire off an UPDATE.

Pretty easy.

Now my report has 2 displays – one for the parent and one for the child report. So I need to add this code to the 2nd display.

<item reload="true" reloadparent="false" removeFromParent="false">
         <title>Visited This Brewery</title>
              <prompt type="confirm">
                   <label>You have been to this brewery in person?</label>
               </prompt>
               <sql><![CDATA[UPDATE BEER SET BEEN_THERE = 'Y' WHERE BREWERY = '#BREWERY#' and CITY = '#CITY#']]></sql>
            <help>What would you say, you do here?</help>
       </item>

Again, note there’s no semicolon and I quoted the strings.

Now I open the report again, run it, and right-click on the last brewery I visited. This one is just outside Asheville and is one of my favorites here in North Carolina.

Yes, please

Yes, please

I get a message back saying it succeeded. But let’s check the data to be sure.

Excellent!

Excellent!

But Maybe You Want to Run A Query and Generate a Display?

You have just described a Child Report. Go build one of those instead :) I’ve done that here for the Sessions report with a custom take on the SQL and XPLAN child reports.