Using Edition Based Redefinition for Rolling Back Stored Program Unit Changes

We had a few days of warm, sunny weather in Milton Keynes recently and this induced Deb and I to purchase a Garden Umberella to provide some shade.
After a lifetime of Great British Summers we should have known better. The sun hasn’t been seen since.
As for the umbrella ? Well that does still serve a purpose – it keeps the rain off.

Rather like an umbrella Oracle’s Edition Based Redefinition feature can be utilized for purposes other than those for which it was designed.
Introducted in Oracle Database 11gR2, Edition Based Redefinition (EBR to it’s friends) is a mechanism for facilitating zero-downtime releases of application code.
It achieves this by separating the deployment of code to the database and that code being made visible in the application.

To fully retro-fit EBR to an application, you would need to create special views – Editioning Views – for each application table and then ensure that any application code referenced those views and not the underlying tables.
Even if you do have a full automated test suite to perform your regression tests, this is likely to be a major undertaking.
The other aspect of EBR, one which is of interest here, is the way it allows you to have multiple versions of the same stored program unit in the database concurrently.

Generally speaking, as a database application matures, the changes made to it tend to be in the code rather more than in the table structure.
So, rather than diving feet-first into a full EBR deployment, what I’m going to look at here is how we could use EBR to:

  • decouple the deployment and release of stored program units
  • speed up the process of rolling back the release of multiple stored program unit changes
  • create a simple mechanism to roll back individual stored program unit changes

There’s a very good introductory article to EBR on OracleBase.
Whilst you’re here though, forget any Cross-Edition Trigger or Editioning View complexity and let’s dive into… Continue reading

Advertisements

utPLSQL – We’re building the…Unit Tests!

You’ll be relieved to hear that I’m not going to talk about Brexit any more. It’s done and finished and it’s time everyone moved on.
Besides, from my current vantage point ( a small island off the coast of Europe), other people have their own problems.
In the USA for example, the nation is currently engrossed in what appears to be an exercise to find the second most unpopular person in the country…and make them President.
If the subject of this post were a Presidential Candidate, it would probably be nicknamed “The Comeback Kid”.
No, Bill Clinton hasn’t released a PL/SQL testing framework, but Steven Feuerstein has and – contrary to what you may have heard – utPLSQL is very much alive and kicking.
As the first Test framework written specifically for PL/SQL, utPLSQL is the perfect subject for my continuing testing odyssey.

To date, this has included

The political analogy is rather apposite when you consider that using utPLSQL largely revolves around making a number of assertions…which may or may not be true.

What we’ll be looking at is :

  • Installing the utPLSQL framework
  • Your first Test and how to run it
  • A quick recap of the Footie Application we’re testing with
  • The utPLSQL Assert API
  • Building and executing Test Suites
  • Retrieving utPLSQL test execution results programmatically

But first…. Continue reading

Native Dynamic SQL – Dodgy Code and DRS

Dynamic. That’s a positive word if ever there was one. Ascribing this adjective to anything would convey an image of energy and forward momentum.
On the question of Dynamic SQL, the images are rather more equivocal.
On the plus side, Native Dynamic SQL gives you the ability to :

  • execute DDL statements from within PL/SQL programs
  • code for instances where the required DML statement is not known ahead of time

On the flip side, it can also mean code that is:

  • difficult to read and maintain
  • prone to performance problems
  • insecure

To wander through this minefield, I have enlisted the support of a world where DRS does not stand for Dodgy Review System.
Yes, it’s the wacky and entertaining (not always intentionally so) world of Formula 1… Continue reading