How To Add Custom Actions To Your User Reports

thatjeffsmith SQL Developer 13 Comments

Tell Others About This Story:

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:

[xml] <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>
[/xml]

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:
[xml] <sql><![CDATA[ALTER SYSTEM DISCONNECT SESSION ‘#SID#, #SERIAL##’ POST_TRANSACTION]]></sql>
[/xml]

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.

[xml] <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>
[/xml]

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.

Tell Others About This Story:

Comments 13

  1. I have created a display editor that prints out trigger names for the selected table. Same thing like Triggers tab when you open a table. Next, I have created a context menu trying to disable the selected trigger in one go. I have tried to pass as parameter the trigger name, like #TRIGGER_NAME#, but the value could not be passed. Instead I got in return a SQL statement like ALTER TRIGGER #TRIGGER_NAME# DISABLE.
    If I take this logic, but instead of a display editor I go for building a report, then the report knows to translate #TRIGGER_NAME# into the name of the trigger that I have selected. Any idea why display editor does not know to interpret my #TRIGGER_NAME# parameter? Thank you!

  2. Yay! Great.
    Is there any way to build a commit into the update? or do I have to commit in another session?

  3. In your example you showed that you can set the brewery to “visited”. Is it possible to add a custom action that allows an input (free text or drop down list) so you can e.g. set a rating?

  4. I am trying to add the action to my sessions report to call a procedure to kill a session. We use a procedure to keep folks from killing another person’s session. the problem here is the ; I believe. It acts as though the command has been executed, but in reality does not seem to be executing it. I tried using EXEC my_procedure, but that does not seem to be valid in the block.

    1. One more time! My code is currently trying to do:

      begin my_procedure; end;

      Unfortunately, I believe the ‘;’ breaks the action.

  5. Jeff, in your example code you use #SERIAL## indicating that since # is part of the name, you need to add an additional #. When I tried this, the right click options of Kill/Disconnect session were ignored. I replaced with #SERIAL# and now it shows those two options in the right-click popup.

    1. thatjeffsmith Post
      Author
      1. Sorry, wrongly posted this to another post…pasting here for continuity:

        Nope, the code did not substitute SERIAL#. I tried again, this time deleted the report definition from the User Defined Reports tree. Added another # to make it #SERIAL## and imported the Report back in and this time the code generated was fine. I guess … well I don’t know what to guess….but it’s a cool trick. Thanks.

  6. Perfect!
    I am about 20 years in this business, but I am really impressed by flexibility and openness of SQL Developer. Thanks a lot for this blog.

    Recently I switched to SQL developer from TOAD and PL/SQL developer (mainly due price and licensing issues) and I don’t regret.

    1. thatjeffsmith Post
      Author
  7. This post is AWESOME! Custom actions was the last missing piece keeping me from building my very own report utopia. Thank you so much!

    1. Thanks for the post on creating custom report actions. Any plans to expose this in the reports editor to make it even simpler?

      BTW – The disconnect session is a great addition to the sessions report.

Leave a Reply

Your email address will not be published. Required fields are marked *