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

My body is a temple

Alright, it’s a place for the worship of Belgian beer, chips, and whatever local culinary or alcoholic delicacy is available. Yes, it takes years of dedication to achieve a body like mine !

In database terms then….

CREATE TABLE wreck( 
    food_stuff VARCHAR2(30), 
    calories NUMBER) 
/

And now to add a few rows …

INSERT INTO wreck( food_stuff, calories) 
VALUES( 'CHIPS', 500) 
/ 

INSERT INTO wreck( food_stuff, calories) 
VALUES( 'BEER', 250) 
/ 

INSERT INTO wreck( food_stuff, calories) 
VALUES( 'MORE BEER', 250) 
/

And a trigger, to represent my iron self-control …

CREATE OR REPLACE TRIGGER wreck_bi_trg 
    BEFORE INSERT ON WRECK 
    FOR EACH ROW 
DECLARE  
    e_too_porky EXCEPTION; 
BEGIN 
    IF :new.calories > 500 THEN 
        RAISE e_too_porky; 
    END IF; 
EXCEPTION 
    WHEN e_too_porky THEN 
        RAISE_APPLICATION_ERROR(-20000, 'I think the keg is full'); 
END; 
/

Deb has now taken matters into her own hands and decided to change things a bit. Project Diet has resulted in this :

CREATE OR REPLACE TRIGGER wreck_bi_trg 
    BEFORE INSERT ON WRECK 
    FOR EACH ROW 
DECLARE  
    e_too_porky EXCEPTION; 
BEGIN 
    IF :new.calories > 100 THEN 
        RAISE e_too_porky; 
    END IF; 
EXCEPTION 
    WHEN e_too_porky THEN 
        RAISE_APPLICATION_ERROR(-20000, q'[You can't have that you're on a diet]'); 
END; 
/

Murphy’s Second Law

At this point, you’re probably wondering where I’m going with this. After all, it’s a simple matter of running this script to implement the change. If you want to roll it back, you can always go back to the source code repository for the old version, can’t you ?
Well, that’s true…sort of. However, we need to think about the risk here.
If the release goes wrong (i.e. Murphy’s Law is invoked) then there is a pretty good chance that Murphy’s Second Law will also come into play.
This Law states that, once one thing goes wrong, other things will follow in a domino-effect.
In other words, the point at which you need the old version of the code is the point at which you will find that, for some reason, it’s not in the Version Control repository.
Now imagine that you’re doing a major release with several stored program unit changes, not just the one and you can see the potential for trouble.

Dumping your program to an OS file

Fortunately, we have DBMS_METADATA and UTL_FILE to help us out.
Using these packages we can run a script immediately before the release to copy the current version of our package to a file.

First of all, we need to associate an OS directory with a directory object in the database. Start by creating the directory :

su oracle
cd /u01/app/oracle
mkdir pre_release
ls -ld pre_release
drwxr-xr-x 2 oracle dba 4096 2012-07-21 16:20 pre_release

Now for the directory object in the database and grant the appropriate permissions

CREATE OR REPLACE DIRECTORY pre_release AS '/u01/app/oracle/pre_release/' 
/ 

Now we can use the directory object to write the file.
NOTE – just before you do this, you need to make sure that you have EXECUTE permissions on UTL_FILE.

DECLARE 
    l_clob CLOB; 
    l_buffer VARCHAR2(32767); 
    l_location all_directories.directory_name%TYPE := 'PRE_RELEASE'; 
    l_filename VARCHAR2(100) := 'wreck_bi_trg_rb.sql'; 
    l_fh UTL_FILE.FILE_TYPE;    
    
    
    l_pos PLS_INTEGER := 1; 
    l_length PLS_INTEGER; 

 
BEGIN 
    -- 
    -- Tell DBMS_METADATA to add a slash after any SQL statements 
    -- to make them executable 
    -- 
    DBMS_METADATA.SET_TRANSFORM_PARAM( 
        transform_handle => DBMS_METADATA.SESSION_TRANSFORM, 
        name => 'SQLTERMINATOR', 
        value => TRUE); 
    -- 
    -- Get the source code of the stored program from the database 
    -- 
    l_clob := DBMS_METADATA.GET_DDL( 
        object_type => 'TRIGGER', 
        name => 'WRECK_BI_TRG'); 
    -- 
    -- Now open a file to write the object to 
    -- 
    l_fh := UTL_FILE.FOPEN( 
        location => l_location, 
		filename => l_filename, 
		open_mode => 'W'); 
	-- 
	-- Writing to the file can only be done using a VARCHAR2 so we 
	-- need to chunk the clob in case the total amount of data is 
	-- greater than the maximum size of a VARCHAR2 - i.e. 32767 
	-- 
	l_length := LENGTH(l_clob); 
	LOOP 
	    EXIT WHEN l_pos > l_length; 
	    l_buffer := SUBSTR( l_clob, l_pos, 32767); 
	    UTL_FILE.PUT(l_fh, l_buffer); 
	    UTL_FILE.FFLUSH( l_fh); 
	    -- 
	    -- Now wash your hands...and set the starting position 
	    -- for the next iteration of the loop 
	    -- 
	    l_pos := l_pos + LENGTH(l_buffer); 
    END LOOP; 
    -- 
    -- Close the file 
    -- 
    UTL_FILE.FCLOSE(l_fh); 
END; 
/

A few points to note here.
First off, DBMS_METADATA is a bit precious about accessing objects in schemas other than the user you are running it as. In order to do this unhindered, you neet to have the SELECT_CATALOG_ROLE role.

UTL_FILE has it’s own little quirks.
Firstly, remember what your mother told you and always remember to FFLUSH
Secondly, the “/” added at the bottom of the file is to make it runnable in SQL*PLUS. Using UTL_FILE.PUT_LINE ( or even UTL_FILE.PUT) doesn’t quite do the trick as it seems to insist on prefixing the “/” character with a space. This results in SQL*Plus not recognising it as an execution directive but treating it as part of the object definition statement.
PUTF is a bit better behaved. However, the most reliable way to accomplish this is to use the features of DBMS_METADATA itself. The SET_TRANSFORM_PARAM procedure has lots of options for various objects. Have a look at the Oracle documentation for more details.

After running this, we should have the wreck_bi_trg_rb.sql file in the directory containing the following :

  CREATE OR REPLACE TRIGGER "MIKE"."WRECK_BI_TRG" 
    BEFORE INSERT ON WRECK 
    FOR EACH ROW 
DECLARE 
    e_too_porky EXCEPTION; 
BEGIN 
    IF :new.calories > 500 THEN 
        RAISE e_too_porky; 
    END IF; 
EXCEPTION 
    WHEN e_too_porky THEN 
        RAISE_APPLICATION_ERROR(-20000, 'I think the keg is full'); 
END; 
/ 
ALTER TRIGGER "MIKE"."WRECK_BI_TRG" ENABLE;

You can now copy the file somewhere safe and proceed with the release, secure in the knowledge that you have the old version to go back to should you need it.

Sometimes, the need to roll back a change is not immediately apparent. In those circumstances, it’s probably useful to have something a bit more permanent than a file sitting on the OS. If only we had somewhere to store persistent data which would enable us to access it easily. Hold on, I’m sure I left a database around here somewhere…

Storing the old version in the database

Once again, Editions based re-definition is probably worth a mention at this point. If that’s not really going to be suitable for you because you’re on 10g, or you’re worried about tripping over the issue with database links, or even if you’re just looking for an example of how to insert a CLOB into a table…
What we’re looking to do here is simply to store the existing source code of the object we are changing in a table as a CLOB. First of all, we need a table :

CREATE TABLE code_backups( 
    object_owner VARCHAR2(30), 
    object_name VARCHAR2(128), 
    object_type VARCHAR2(19), 
    object_version VARCHAR2(30), 
    source_code CLOB) 
/ 

Just before we do the release, we need some code to insert into our table.
Remember, inserting a CLOB can be a bit tricky…

DECLARE
	l_owner code_backups.object_owner%TYPE;
	l_type code_backups.object_type%TYPE;
	l_name code_backups.object_name%TYPE;
	l_locator CLOB;
	l_source CLOB;
	
	l_buffer VARCHAR2(32767);
	l_pos PLS_INTEGER;
	l_length PLS_INTEGER;
	lc_max_size CONSTANT PLS_INTEGER := 32767;
BEGIN
	l_owner := 'MIKE';
	l_type := 'TRIGGER';
	l_name := 'WRECK_BI_TRG';
	--
	-- Get the DDL as before...
	--
	l_source := DBMS_METADATA.GET_DDL( object_type => l_type, name => l_name, schema => l_owner);
	--
	-- Insert the non-CLOB data and get a pointer to the CLOB
	--
	INSERT INTO code_backups( 
	    object_owner, object_name, object_type, 
	    object_version, source_code)
	VALUES( l_owner, l_name, l_type, 
	    'The Old Me', EMPTY_CLOB())
	RETURNING source_code INTO l_locator;
	--
	-- AS DBMS_LOB.WRITE and DBMS_LOB.WRITEAPPEND only accept a VARCHAR2 buffer and not a CLOB
	-- we need to chunk the CLOB to avoid any issues with it being too big
	--
	l_pos := 1;
	l_buffer := NULL;
	l_length := LENGTH(l_source);
	
	l_buffer := SUBSTR( l_source, l_pos, lc_max_size);
	DBMS_LOB.WRITE( 
		lob_loc => l_locator,
		amount => LENGTH(l_buffer),
		offset => 1,
		buffer => l_buffer);
	IF l_length > lc_max_size THEN
		--
		-- Need to use DBMS_LOB.WRITEAPPEND to load the rest of the LOB
		-- if it's more than 32767 characters.
		--
		l_pos := l_pos + LENGTH(l_buffer);
		LOOP
			EXIT WHEN l_pos > l_length;
			l_buffer := SUBSTR( l_source, l_pos, lc_max_size);
			DBMS_LOB.WRITEAPPEND(
				lob_loc => l_locator,
				amount => LENGTH(l_buffer),
				buffer => l_buffer);
			l_pos := l_pos + LENGTH(l_buffer);
		END LOOP;
	END IF;
	COMMIT;
END;
/

We can now retrieve the source code any time :

set long 5000
set heading off
SELECT source_code
FROM code_backups
WHERE object_owner = 'MIKE'
AND object_type = 'TRIGGER'
AND object_name = 'WRECK_BI_TRG'
AND object_version = 'The Old Me'
/

  CREATE OR REPLACE TRIGGER "MIKE"."WRECK_BI_TRG"
    BEFORE INSERT ON WRECK
    FOR EACH ROW
DECLARE
    e_too_porky EXCEPTION;
BEGIN
    IF :new.calories > 500 THEN
	RAISE e_too_porky;
    END IF;
EXCEPTION
    WHEN e_too_porky THEN
	RAISE_APPLICATION_ERROR(-20000, 'I think the keg
 is full');
END;
ALTER TRIGGER "MIKE"."WRECK_BI_TRG" ENABLE



SQL> 

It’s probably worth remembering that this table is going to use a fair amount of space. So, it’s probably a good idea to delete records that are no longer required on a regular basis.

I’ve deliberately kept these examples fairly simple, but it’s easy to see how they could be used as the basis for a fairly robust framework to insure against those times where you’re source control repository doesn’t quite contain what you think it does.

Deb has decided to abandon project diet as it was unlikely to achieve it’s business objectives.
I’m not sure what she’s working on now but I must confess that Project Nil By Mouth has me a little worried.

Advertisements