Re: If EXISTS before insert clause

From: G Quesnel <dbaguy_ott_at_yahoo.com>
Date: 7 Feb 2005 08:21:01 -0800
Message-ID: <1107793261.078724.307970@c13g2000cwb.googlegroups.com>

Create, Drop, Alter are DDLs, and are typically submitted with an execute immediate 'cmd'; statement when used inside a PL/SQL block/script. We use the execute immediate structure inside anonymous blocks to add intelligence to our release scripts. For example, let say that in release x.y, we want to add a column and an index to table t1, we would use a script that would contain the following logic;
Declare

   vobj number; -- count used to determine if an object exist    vtblspname varchar2(30); -- tablespace name begin
  select count(*) into vobj
   from user_tab_columns
  where table_name='T1' and column_name='C1';   if vobj < 1 then

     execute immediate 'Alter table T1 add C1 number';   end if;
  Select count(*) into vobj
   from user_indexes
  where index_name='AK_T1_C1' and table_name='T1';   if vobj < 1 then

     Select tablespace_name into vtblspname
      from user_indexes
     where index_name='PK_T1';
     execute immediate 'Create index 'AK_T1_C1 on T1 (C1)'||
                                 ' tablespace '||vtblspname;

  end if;
end;

Note that the amount of control you have over the schema will dictate the complexity of your scripts.
For example, using an index name may not be a good ideal. We use the table name, with ..._ind_columns, since the production dba may have rebuilt the index under a different name (out of our control). This method buys you a lot of flexibility, so that if your schema upgrade scripts need to run in multiple environments, you can have the object sized automatically calculated, reducing the need for manual intervention.
This aproach also allows you to maintain only one schema upgrade script for one release, and since it is rerunable, you don't have to worry about which delta may have been run in which database. You will need to find your happy middle of the road point, between the complexity of predicting every possibilities and the benefits of it being handled automatically (at some point, the people implementing the schema upgrade sripts have to take some responsibilities ! - sorry for the vent) Received on Mon Feb 07 2005 - 10:21:01 CST