Playing with CLOBs – and a simple way of rolling back database changes

Deb finally agreed to make an honest man of me and we tied the knot last month.
This happy event coincided with the Olympics, during which I noticed my new bride taking an unusual interest in various events…or possibly the competitors.
When she did remark on the six-pack of one particularly fine specimen of male athleticism, combined with a meaningful look, I felt compelled to expound
my theory of male fitness, which can be summarised thus :
I regard my body in the same way that I regard beer – why have a six-pack when you can have a keg ?
Yes, in terms of body-shape, I must admit that mine does not so much resemble the genius of a work by Michelangelo as it does the work of a cooper last thing on a Friday before heading off to the pub. However, there is a more pressing problem occupying my mind at the moment, namely, how best to rollback a release of code to an Oracle database.

Of course, there are a number of solutions to this particular problem. If your system needs to remain available during the release, Edition based re-definition is certainly worth a look. If you have the luxury of down-time whilst doing the release, then you have the option of a Point-in-time recovery should things not quite go according to plan. For a smallish release however, that does seem a bit like overkill.

Alternatively, if you’re on Enterprise Edition ( and once again, assuming you have the database to yourself), you might even consider Flashback.

The thing about stored program units is that, unlike other code, you don’t have a source file.
CREATE OR REPLACE overwrites the code you are changing so you need to make a conscious effort to back up what you’re changing if you might need to put it back.

What I’m going to cover here is

• How to deal with Murphy’s Second Law
• Using PL/SQL to backup specific stored program units prior to a release
• Storing old versions in the database as CLOBs

But first, we need an appropriate example….

PL/SQL Arrays – The Autumn Collection

I’ve spent some time recently playing with PL/SQL arrays in the context of uploading from flat-files.
In the course of this, it struck me that PL/SQL arrays come in a variety of shapes and sizes ( or in this case, small, medium and large).
So, if Sir – or Madam – would care to step into the fitting room, we’ll see if we can find something to suit.

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.