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.

Sinister Synonyms and Dependencies in Oracle

It’s been quite an eventful week. Deb has got her results back and is now officially a lady of “Distinction” (two of them, no less). Even Wales’ narrow defeat to England in the Rugby hasn’t put a dent in her good mood.
I, on the other hand, found myself doing my Marvin-the-paranoid-android-as-a-DBA impression the other day….”Synonyms. Loathe them or hate them, you can’t ignore them”.
Now, whilst synonyms definitely have their uses, they can be something of a double-edged sword.
The cause of this particular downbeat assessment of their merits was the fact that I’d deployed my CRUD tool on a new database, but it had failed to pick up some dependencies.
Let’s have a closer look at this issue and see how Oracle’s own DBMS_UTILITY copes with these circumstances. Continue reading