Re: whenever sqlerror is an unknown statement?

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Sat, 28 Jan 2006 10:20:03 +0000
Message-ID: <3ZnIkZsTV02DFwgu@jimsmith.demon.co.uk>

In message <1138389236.752751.191880_at_g14g2000cwa.googlegroups.com>, jjsavage_at_gmail.com writes
>Upgrade procedures? That sounds promising indeed. Where can I find
>out about them?

Upgrade procedures are procedures that you write to do an upgrade. My preference would also be to upgrade the database before running the upgraded software but if your customers have no technical staff then embedding the upgrade in the application is probably acceptable.

Ignoring all the shouting in here about whether it is good practice to create tables on the fly (it isn't), it is necessary in some rare circumstances like yours.

The best way for individual tables is probably

declare
tname varchar2(30);

begin
   select table_name
   into tname
   from user_tables
   where table_name = 'NEWTABLE';
exception
   when no_data_found then
     execute immediate 'create...';
end;

However, this approach has a number of problems

it assumes the application is connected to the database as the schema owner
it is impractical if there are a lot of new tables/objects
it is impractical for altered tables (ie new of changed columns)
if it fails for any reason it leaves the application in limbo and possible broken.

A better approach is to maintain a db version table and use it to drive an upgrade procedure.

declare
  required_version constant number(10,0):=987;   actual_version number(10);
begin
  select db_version
  into actual_version
  from version_info;
exception
when others ....
<snip>
end;
if actual_version < require_version then    do_upgrade;

end if;

procedure do_upgrade is -- very crudely
begin
begin

  execute immediate 'create...';
  execute immediate 'drop...';
  execute immediate 'alter..';

  update version_info
  set db_version=3;
exception
   when others -- ;
end;
end;

Note:

The do_upgrade needs to be run as the schema owner. Again, if this is run as part of the application, failure could leave the databases in a broken state.

-- 
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

Received on Sat Jan 28 2006 - 04:20:03 CST