Custom Reports in PL/SQL Developer

Having played around with PL/SQL Developer, one of the frustrations I’ve found is that the extensibility of the tool relies on you being able to create dlls.
Not being a Microsofty, I’ve found it a bit limiting when compared with SQLDeveloper, which allows some fairly significant add-ons by the simple application of a bit of XML.

Don’t get me wrong, I quite like PL/SQL Developer, not least because it allows you to run SQL*Plus scripts pretty much unedited.

Now, I wanted a way of displaying the information held in my CRUD application without having to go to the trouble of typing the statement in each time.
The answer to my problem – the PL/SQL Developer custom report.

Creating the Report

In PL/SQL Developer, go to File / New/ Report Window.
This is where you type in your SQL statement.

As with SQL*Plus, any runtime parameters are prefixed by an ‘&’.
The way you name and define these parameters is a little different however.
In my case, I want to give my variables a name that will show up when the user is prompted to enter them at runtime. Both of the variables are mandatory, and both should be converted to uppercase.

The end result is a query that looks like this :

SELECT object_owner, object_name, object_type,
       create_flag, read_flag, update_flag, delete_flag
FROM crud_owner.db_crud
WHERE table_owner = '&<name="Table Owner" required="yes" uppercase="yes">'
AND table_name = '&<name="Table Name" required="yes" uppercase="yes">'
ORDER BY 1,2,3

There are a (bewildering) number of configuration options for the report, but I’m quite happy with the default output so I simply have to save the report in a file with a .rep extension.

Adding the Report to the Menu

Back at the main menu, select Tools / Configure Reports…
In the Configure Reports dialog box, click on the yellow folder icon and navigate to where you saved your .rep file.
Make sure that the Report as main menu item checkbox is checked then click OK.

When you next open the Reports menu, you should see your new report at the bottom of the list.

I don’t think I’ll ever learn to love PL/SQL Developer ( or any other Oracle IDE for that matter), but at least this sort of thing makes life a little more bearable.