Integrate SQL*Plus Scripts in SQL Developer

I envy my DBA colleagues when they work with the Oracle Database from the command line in an incredibly efficient way. They just call a series of scripts with some parameters to get the desired information. Everything looks so easy, so smooth, so natural.

I’m a developer. Basically a mouse pusher. I like to work in an IDE. It’s comfortable. However, I’d also like to use some of these fancy SQL*Plus scripts from the IDE in an easy way. This means, the scripts have to be accessible and executable via mouse clicks only. The keyboard is used when changing default values of parameters. Is something like that possible? – Of course. In this blog post I show how.

ashtop.sql – The Script to Integrate

Tanel Poder provides an extensive collection of useful SQL*Plus scripts in his TPT Oracle GitHub repository. One of them is ashtop.sql. Here’s the header:

Line 14 shows the usage and line 17 an example.

And here’s the output of the example call against my Oracle Cloud ATP instance:

The columns username and sql_id are the group by columns. When I change the first parameter and pass just username the result looks like this:

Actually I can use every combination of columns in gv$active_session_history and dba_users as grouping columns. A nice SQL*Plus script.

Let’s integrate ashtop.sql in SQL Developer.

Step 1 – Install tpt-oracle

Download or clone Tanel Poder’s Troubleshooting Scripts (TPT) from GitHub. I keep these scripts on my MacBook in /Users/phs/github/tpt-oracle.

Step 2 – Create New Report

Select Reports from the view menu

and then right-click on User Defined Reports and select New Report... from the context menu.

In the new window type ashtop in the name field, change the style to Script, copy the following script and paste it into the SQL field:

And press Apply.

Step 3 – Set Defaults for Bind Variables

We have defined 5 bind variables in this report. :grouping_cols, :filters, :fromtime, :totime and :tptdir. They are converted to SQL*Plus substitution variables and then passed to the ashtop.sql script. We can execute the report now, but NULL is the default value of all bind variables. This is not very convenient. Hence, we are going to change that.

Right-click on the ashtop report and select Edit... from the context menu.

Click on Binds and set the values according the following screenshot. Important is the Default column.

And press Apply.

Step 4 – Save Report

To save the report you have to Select Save All from the File menu. The report is then saved in your UserReports.xml file in your ${ide.pref.dir} directory. You find the value of this variable in the Properties tab of the About Oracle SQL Developer dialog.

Step 5 – Run Report

Click on the ashtop report. Select a connection from this dialog and press OK.

Then you can optionally change the values of the bind variables in this dialog.

Press Apply and then the script is executed and the result is shown in a new tab.

Step 6 – Save Report as XML File

Right-click on the ashtop report and select Save As... from the context menu.

And then save the report in a directory of your choice.

Step 7 – Configure Report as User-defined Extension

You can configure the previously saved report in the SQL Developer’s preferences as user-defined extension as shown here:

After restarting SQL Developer the configured report is shown under Shared Reports:

Summary

SQL Developer reports can be based on SQL but also on SQL*Plus scripts. Calling external scripts has the advantage that I only have to maintain the interface to the SQL*Plus script within SQL Developer. This way I can install new versions of the scripts, for example by fetching updates from a Git repository and these new script versions are used the next time I run a report from SQL Developer.

Updated on 2019-10-25, new screenshot in step 2, formatted code, mentioned that the style needs to be changed to Script. Thanks Dani Schnider for your feedback.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.