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:
- Copy the report from the Database Administration section of the Reports panel
- Paste it to the User Defined Reports section
- 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.
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.
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.
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.
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.
I get a message back saying it succeeded. But let’s check the data to be sure.
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.
18 Comments
Love this post! I am currently trying to create a report that helps with building deployment packages. I have a sql that loads object names directly from a custom table and then extract the ddl with dbms_metadata concatenated with customized header comments for each object. Is there a way to export/spool out each ddl string into seperate paths with the custom context menu to the client machine?
To be sure that I didn’t do anything stupid. I took the Sessions report from Data Dictionary | Database Administration | Sessions. Saved it, opened it so it was added as a User Defined Report. Added a Child Report, and then all actions (kill, enable and disable trace) were gone.
It would be great if that could be fixed, please 🙂
You’d have to add the child reports using an xml editor once you have custom actions in it. The reports editor isn’t setup to support custom actions.
So, add this child reports first.
Then code in the actions.
Then, only ever update the report by touching the XML source going forward.
Great post. I just wonder if you add some thing, like a Child Report to the report where you have manually added some actions, will the actions still be there? For me, they are removed every time. Very annoying. I have checked with Office XML Handler that I have not messed up anything with brackets but it looks good.
Cheers /Jocke
Did you add the child report using the GUI or your text editor?
Once you add custom actions, you need to stay out of the GUI for editing your report.
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!
Yay! Great.
Is there any way to build a commit into the update? or do I have to commit in another session?
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?
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.
code is :
“”
One more time! My code is currently trying to do:
begin my_procedure; end;
Unfortunately, I believe the ‘;’ breaks the action.
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.
And the generated SQL code to kill/trace the session is correct? If so, then cool…but wondering what the difference is.
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.
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.
Thanks Pavel!
This post is AWESOME! Custom actions was the last missing piece keeping me from building my very own report utopia. Thank you so much!
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.