PLS-00364 – And you never buy me flowers !

Nestled deep in the heart of the Oracle RDBMS lies DIANA – the ADA pre-compiler which gives all your PL/SQL the once-over before sending it out into the world.
As I’ve mentioned before, DIANA can be a capricious girl, and if you upset her, she’s likely to complain about all sorts of things, some of them entirely spurious.
One such error that she tends to throw out is “PLS-00364 : loop index variable [ some cursor record variable ] use is invalid”. Continue reading

Zen and the Art of Shell Scripting

You know what it’s like with a little kid at his birthday party. They get all excited, eat far too many sugary foods and then run around behaving badly.
Oracle Openworld has ended for another year, and not a minute too soon. Hopefully, Larry will have an early night and stop being so excitable and upsetting all those jolly nice open source types he had round.
Meanwhile, in the comparative calm of the backwater that is this blog, I’ve been getting all Zen. Well, zenity, to be precise. Yep, I’ve decided that some my batch scripts needed to get all GUI with Gnome and zenity looks to be the tool to do it.
What follows is an account of my first steps with zenity followed by a demonstration of some of it’s capabilities. Continue reading

UTL_FILE in PL/SQL – I/O, I/O, it’s off to work we go

Back in the mists of time, when Broadband was a way of describing a group of fat blokes with guitars, PL/SQL blinked it’s way into the world. It’s purpose was ( and largely remains) to provide the facility to apply 3GL program structures to SQL from within the database ( hence – Procedural Language / SQL).
As an integral part of the Oracle RDBMS, most PL/SQL I/O activities are on database tables. The ability to read and write OS files didn’t arrive until much later.
Meanwhile, back in the present, things are somewhat better on the File Handling front. So, if you just have to generate that flat-file and would rather not muck about with a pre-compiler (or a Java Stored Procedure), PL/SQL will do the job. Continue reading

Ripping Yarns – Music, Samba, Ubuntu and Various Discworld Characters

Yes, I know this is supposed to be a blog about Oracle stuff. It’s just that, well, Larry’s been busy this week upsetting large chunks of the Open Source Community – MySQL; OpenSolaris; even James Gosling has had T-shirts printed up urging Oracle to “Let Java Go”. Suffice to say that, given all of this furore, I’ve concluded that I could do with improving my Open Source Karma a bit.
Fortunately, I’ve been busy this week, loading all of my newly inherited music collection onto by Ubuntu Server to enable playback from any other machine on the network. What follows is an account of my adventures.
It was a simple plan – rip all of the CDs to an existing Samba Share on the server and then find software that can read the format and allow playback on both Windows and Linux. Continue reading

Setting the Windows Path Variable for Cygwin…when you’re not allowed to

Several years ago, whilst working in an organisation that thought that organising functions into silos was an outstanding idea, I needed to have a UTL_FILE_DIR added to the init.ora ( this was back on Oracle 8i, since you ask).
Not having sufficient access to be able to implement this change myself I had to request it from the DBA group….based in Madrid.
The request was to enable us to write to a directory on the same server as the Database. Not being involved in the physical configuration of the database, I left it to the DBAs to pick which directory to use.
This change, which would’ve taken me 2 minutes, disappeared into the system. The DBAs had to refer it to the Linux Admins( Poland), who had to then discuss the matter with the Storage Team (Switzerland).
End result : six weeks later I get an automated mail saying that the call has been resolved…and I end up with a unix environment variable called $UTL_FILE_DIR. Oh, how we laughed. Continue reading

Getting output from Ref Cursors in PL/SQL

A colleague of mine (Martin, you know who you are), remarked the other week that he wasn’t overly interested in the contents of the blogosphere. He said that it usually put him in mind of the cartoon of the tag-cloud consisting solely of the word “me”. This got me to thinking, why do I do this ?
Let’s put my ego to one side for a moment ( pause to sounds of straining, followed by a dull thud). That was heavier than it looked.

One of the reasons for maintaining this blog is that I’ve got a quick reference to look at if I come across something I did a while ago and need a quick reminder of syntax etc. Also, my Mum likes to know what I’m up to.
The starting point for this entry was to attempt to drag together all the basic bits about Ref Cursors in PL/SQL – specifically, accessing them from within PL/SQL itself.

Whilst I was writing this, it was pointed out to me that SQLDeveloper doesn’t handle Ref Cursors quite as nicely as Toad. The specific issue was the difficulty in dumping the results into a grid, from whence it can be transferred to Open Office Spreadsheet ( or Excel).

For the most part, Ref Cursors are used to transfer data from the database to a web application. So, why would you need to start fiddling about with getting results back in PL/SQL ?
There are probably several answers to this question. However, for me, it’s mainly a case of having to trace problems raised in various support calls. Knowing what data results from each of the calls in a process usually helps a bit. Continue reading

Windows Bashing…with Cygwin

I was in Wales last week, land of my girlfriend ( yes, I have got one, try not to look so shocked).
Wales, land of story and legend….where the rain goes for it’s summer holidays.

Stepping gingerly between the puddles in picturesque ( albeit, soggy) Laugharne, we spent an instructive ( and mainly dry) hour or so at the boathouse once occupied by Dylan Thomas. In the course of this cultural interlude, I learned that the Great Man’s last words were “ I’ve had 18 straight whiskys. I think that might be the record.”

Hmmm, I wonder if he’d been trying to write a Windows batch script ?

Whatever the merits of Windows in terms of it’s ubiquity, one undeniable fact is that the facilities provided for batch scripting on the command line are stone-age compared to those in Unix.
This is something I’ve often reflected on, usually when confronted with a problem that requires a bit more than a simple for loop.

Help is at hand however, in the form of Cygwin – a toolset which enables you to more-or-less run a bash shell on Windows. Sounds good to me. Let’s have a look…. Continue reading