The Anti-Kyte

Oracle – for when it was like that when you got there

Main menu

Skip to primary content
Skip to secondary content
  • Home
  • About

Tag Archives: utl_file.fflush

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

Posted on September 8, 2012 by mikesmithers
2

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 →

Advertisements
Posted in Oracle, PL/SQL, SQL | Tagged create or replace directory, dbms_lob, dbms_lob.write, dbms_lob.writeappend, DBMS_METADATA, dbms_metadata.set_transform_param, empty_clob, UTL_FILE, utl_file.fflush, utl_file.put | 2 Replies

Search

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 263 other followers

Popular Ramblings

  • Solved – The Mystery of SQLDeveloper and the Missing ocijdbc11
  • CREATE USER and ALTER USER - changing passwords and a New Year's Resolution
  • Getting a File Listing from a Directory in PL/SQL
  • Configuring Django with Apache on a Raspberry Pi
  • SQLCL - The New SQL*Plus

Ramblings by Category

Past Ramblings

Pages

  • About

Blogroll

  • Connor McDonald
  • Grassroots Oracle
  • Jeff Kemp
  • Jeff Smith
  • Red Database Security Blog
Advertisements
Create a free website or blog at WordPress.com.