Prevent error when dropping not existing sequences, creating existing users

Question

I have a bunch of sql scripts that create / drop sequences, users and other objects. I'm running these scripts through liquibase, but they fail because oracle complains when I try to drop a non existing sequence, or create an existing user.

Is there an oracle way to prevent errors from happening?

Something of the sort

Create User / Sequence if not exists

Drop User/ Secuence if exists

As far as I know, I have these options:

Write a plsql script
Use liquibase contexts.
Use liquibase preconditions, but this would mean too much work.

Any thoughts / ideas will be greatly appreciated.

score 20 · Accepted Answer · edited Jul 03 '15 at 09:02

20

Liquibase has a failOnError attribute you can set to false on changeSets that include a call that could fail.

<changeSet failOnError="false">
   <createSequence sequenceName="new_sequence"/>
</changeSet>

This allows you to have simple create user, create sequence, drop user, and drop sequence changeSets and if the statement throws an error because they users/sequences exist/don't exist they will still be marked as ran and the update will continue.

The downside of this approach is that it will also mark them as ran and continue if they error for some other reason (bad permissions, connection failure, invalid SQL, etc.) The more accurate approach is to use preconditions, like this:

<changeSet>
   <preconditions onFail="MARK_RAN"><not><sequenceExists/></not></preconditions>
   <createSequence name="new_sequence"/>
</changeSet>

There is no userExists precondition currently, but you can create custom preconditions or fall back to the precondition. See http://www.liquibase.org/documentation/preconditions.html for documentation

edited Jul 03 '15 at 09:02

arulraj.net

4,579
3
36
37

answered Oct 26 '09 at 21:48

Nathan Voxland

15,453
2
48
64

I considered preconditions, but one doubt remains: If i have N create sequence statements, should I create equal number of changesets? – Tom Oct 27 '09 at 11:32
2

Yes you should. ChangeSets attempt to be transactional, but databases often auto-commit on DDL calls like create sequence. The databasechangelog table that liquibase uses to track what changeSets ran just tracks entire changesets that ran, so if you have 15 create sequence calls in a single changeSet and for some reason the 8th one throws an error, the liquibase update will fail on the first run because of that error, and from then on the changeSet will always fail because the first sequence already exists. Therefore, the best approach is to have a single DDL call per changeSet. – Nathan Voxland Oct 30 '09 at 15:59
syntax for liquibase 3.4.2 has changed, the name attribute is now "sequenceName" – chrismarx Jul 18 '16 at 19:18
And now the tag is ` – mkczyk Jan 25 '22 at 17:19

score 5 · Answer 2 · answered Oct 26 '09 at 15:50

Write a function do_ddl similar to this and catch all exceptions you want to catch:

DECLARE
   allready_null EXCEPTION;
   PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
   execute immediate 'ALTER TABLE TAB MODIFY(COL  NULL)';
EXCEPTION
   WHEN allready_null THEN
      null; -- handle the error
END;
/

score 2 · Answer 3 · answered Oct 26 '09 at 18:51

I'd just use a PL/SQL anonymous block.

begin
   for x in (select sequence_name
             from   user_sequences
              where sequence_name in ('SEQ1','SEQ2' ... 'SEQn'))
   loop
      execute immediate 'drop sequence '||x.sequence_name;
   end loop;
end;
/

score 2 · Answer 4 · answered Dec 26 '18 at 16:10

2

liquibase preconditions didn't check for existing sequences for me. So, after multiple attempts, I tried simple <changeSet id="test-id"><sql> DROP SEQUENCE IF EXISTS "TABLENAME_ID_seq"; </sql></changeSet>

answered Dec 26 '18 at 16:10

Ramz_the_dev

49
7

score 1 · Answer 5 · answered Jul 23 '16 at 19:51

In my experience, based on Liquibase 3.5.1 behaviour, when using failOnError="false", the changeSet does not get recorded as 'RAN' if the operation failed. To me this seems a bug and Nathan's answer does not seem to be correct?

The downside of this approach is that it will also mark them as ran and continue if they error for some other reason (bad permissions, connection failure, invalid SQL, etc.) The more accurate approach is to use preconditions, like this:

I.e.: it does not mark them as ran!

Prevent error when dropping not existing sequences, creating existing users

5 Answers5

Linked