Oracle XE – Speeding up Startup and getting Nofications on the Desktop in Ubuntu

Oh the tension. 2-0 up at half-time. Visions of last-day drama and an eleventh-hour escape floating tantilisingly before my eyes. Then, grim reality. A second-half collapse and the prize is snatched away once more. So, this is what it must be like to be an Arsenal fan.
I know that they say misery loves company, but it’s not really any consolation. Yes, my beloved West Ham have been relegated from the Premiership.

In an effort to rouse myself from the resultant depression, I decided to have a look at addressing one of those minor annoyances that I’m always intending to get around to but somehow never quite do. In this case, it’s how to get confirmation that my Oracle XE database has started before trying to connect to it ( and that it’s shut down before I turn off my computer).

Database Design – Denormalization, Codd and the Copa America

Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America.

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.

Oracle Instant Client on Ubuntu…with added Aliens

“This is the voice of the Mysterons…have you got any Lemsip ?”
Yep, I’ve caught Deb’s cold and now sound like the alien menace from Captain Scarlet.
This provides a somewhat tenuous link to the subject at hand – namely installing Oracle Instant Client on Ubuntu.
I think I’d better explain. As you probably know, Ubuntu – being a Debian based Distro – uses the Debian packaging mechanism. Oracle, on the other hand, provides Instant Client for Linux in rpm ( RPM Package Manager) format. In order to bridge this divide, we’re going to need to use the alien utility. Look, I did say it was tenuous OK.

I’m doing this on a 32-bit Ubuntu installation ( 10.04, since you ask). If you’re running 64-bit, you’ll need to download the appropriate equivalent files.

More Oracle Dating Tips – High Days and Holidays

Following on from my post about Dates the other week, I’ve been looking around (afer all, there’s no harm in looking). There’s a fair number of clever date manipulation routines out there, calculating a business week, the tax year etc.
Work, work, work. What would be really useful is something that can work out when the Public Holidays are this year.

Reverse Engineering a Crud Matrix complete with SQLDeveloper Extension – Version 2

A couple of years ago, I wrote an application to reverse-engineer a CRUD matrix for tables in an Oracle database.
I’ve since used it quite a lot for impact analysis and have refined it a fair amount. I’m now happy enough with the new version to let it take it’s first steps into the wider world….where doubtless people will be able to find some of the bugs that I’ve missed.
At this point, if you’re wondering what a CRUD matrix is, you can have a look at the original post here.

ANSI SQL Huh, What is is good for ? Er, Outer Joins Actually

When Oracle first introduced ANSI syntax to become ANSI SQL 92 compliant, the general idea was that the traditional Oracle syntax would do exactly the same as this new fangled ANSI stuff.
You wouldn’t have to start coding LEFT INNER JOINs everywhere and you could pretty much go on your way unmolested by so much syntactic furniture.

This was the approach I’d followed quite happily for many years. Sure, I had moved away from DECODE toward CASE as I think the code tends to be more easy to follow, But all that extra typing to code an ANSI Join – a waste of valuable typing molecules….or so I thought.

Recently, I came across a situation where I needed to outer join a table to two other tables.
Now, in honour of the man who brought this to my attention – a Business Analyst…and an Irishman…on the day that Ireland beat England, yes, at cricket…(thanks Gavan)…

Nesting Quotes in SQL

Recently, I came across one of those situations where you need to get some data from a Production instance and transform it in some way. The catch is that you only have read access on the database in question. All of the usual methods of extracting data are blocked by either an extremely long and tedious battle to get the required access or are prohibited by various security policies.
I will need to do this quite regularly, so it looks like I’ll have to write some slave SQL to extract the data and then load it into a non-production database, where I have more privileges.
Once again, I’m going to have to struggle to remember the new Oracle Q quote syntax.

But before all that, a brief history of quotes.

In the beginning, there was the escape character. In SQL*Plus, this happens to be a single quote …

ORA-1810 and Other Dating Disasters

During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).

The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query

Installing PL/SQLDeveloper under Wine in Ubuntu

Tantalus – that Greek bloke who was doomed for all eternity to eternal thirst and hunger despite having food and water within reach. Had he been a Geek, rather than a Greek, he would’ve worked in a place where you can choose which OS to use…but be denied by the fact that, as a database developer, he had to use PL/SQLDeveloper – a Windows only IDE.

Oh the bitter irony. Well, unlike Tantalus, I’ve decided that, as with so many other problems in life, this particular dilemma can be resolved by alcohol – in this case, a glass of Wine.

WINE – formerly WINdows Emulator, now re-christened Wine Is Not an Emulator. It’s open source so, on this occasion, we don’t have to beware Geeks bearing gifts ( sorry).

For this particular exercise, I’m using Ubuntu 10.04.
PL/SQLDeveloper is that “other” PL/SQL IDE – this one being published by Allround Automations.