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…. Continue reading
