How to update an change set ???

General Discussion
1

Hi,

I found a tricky situation.

  1. I create a change set with an sql statement like following.

   <changeSet id=“1” author=“Stephane Clinckart”  dbms=“oracle”>
     
      DECLARE
 table_name     VARCHAR2(30);
 column_name    VARCHAR2(30);
 sequence_start NUMBER;
 stmt VARCHAR2(2000);
BEGIN
 table_name :=‘alf_transaction’;
 column_name  :=‘id’;
 stmt := ‘select nvl(max(’ || column_name || ‘),0)+1 from ’ || table_name;
 dbms_output.put_line(‘Executing ‘’’ || stmt || ‘’’’);
 EXECUTE immediate stmt INTO sequence_start;
 stmt := ‘create sequence ’ || table_name || ‘_seq ’ ||’ start with ’  || to_char(sequence_start) ;
 dbms_output.put_line(‘Executing ‘’’ || stmt || ‘’’’);
 EXECUTE immediate stmt;
END;
   
   

This is working correctly in most of the situation (let say on different envirronment).

But… now I found some new envirronement where this sql script can’t run like it is written.
The correct script should be:
   <changeSet id=“1” author=“Stephane Clinckart”  dbms=“oracle”>

DECLARE
table_name VARCHAR2(30);
column_name VARCHAR2(30);
sequence_start NUMBER :=1;
stmt VARCHAR2(2000);
table_exist INTEGER;
BEGIN
table_name :=‘alf_transaction’;
column_name  :=‘id’;
stmt := ‘select nvl(max(’ || column_name || ‘),0)+1 from ’ || table_name;
dbms_output.put_line(‘Executing ‘’’ || stmt || ‘’’’);
EXECUTE immediate ‘select count(*) from user_tables where table_name = ‘’’ || table_name || ‘’’’    INTO table_exist;
IF(table_exist > 0)
THEN
EXECUTE immediate stmt INTO sequence_start;
END IF;
stmt := ‘create sequence ’ || table_name || ‘_seq ’ ||’ start with ’  || to_char(sequence_start) ;
dbms_output.put_line(‘Executing ‘’’ || stmt || ‘’’’);
EXECUTE immediate stmt;
END;

In fact, I try to “correct” the script himself… who is running in same situation and not in another one in the original release.

But, if I try to run this correct script on an existing system, I will get a checksum error.
So, How to handle this tricky situation?

Thanks a lot for help

Stephane Clinckart

2

You definitely don’t want to modify the changeset after it has gone live.  The point of the checksum is to make sure that the changeset exactly matches the state of the database, so that later changesets can trust that the database is in proper working order.

I’d suggest using the context feature to identify that particular changesets should only run in certain environments.

3

That is very true.  Changing changeSets that have already ran can be very dangerous and lead to inconsistancies between databases.

However, if you do run into a point where you DO need to change an existing checksum, you can use the tag on the changeSet to list the old checksums.  This allows you to avoid the checksum error. 

Nathan