Prevent error when deleting non-existing sequences by creating existing users

Question

I have a bunch of sql scripts that create / drop sequences, users and other objects. I run these scripts through Liquibase, but they fail because the oracle complains when I try to discard the existing sequence or create an existing user.

Is there any way oracle can prevent errors?

Something like

Create user / sequence if it does not exist

Disable user / secuence if exists

As far as I know, I have the following options:

• Write a PLSQL script
• Use the contexts of the educational program.
• Use the prerequisites of linibase, but that would mean too much work.

Any thoughts / ideas would be highly appreciated.

Answer 1

Liquibase has a failOnError attribute, which you can set to false in changeSets, which include a call that may fail.

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

This allows you to simply create a user, create a sequence, abandon user sequences and change the sequence of changing sequences, and if the operator gives an error because they / they exist / do not exist, they will still be marked as running, and the update will continue.

The disadvantage of this approach is that it will also mark them as running and continue if they are erroneous for some reason (poor permissions, connection failure, invalid SQL, etc.). A more accurate approach is to use preconditions like

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

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

Answer 2

Write a do_ddl function similar to this and catch all the 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; / 

Answer 3

I would just use an anonymous PL / SQL 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; / 

Answer 4

In my experience, based on the behavior of Liquibase 3.5.1, when using failOnError = "false", changeSet is not written as "RAN" if the operation failed. Does this seem like a mistake to me, and doesn't Nathan's answer seem right?

The disadvantage of this approach is that it will also mark them as running and continue if they are mistaken for any other reason (poor permissions, connection failure, invalid SQL, etc.). A more accurate approach for using preconditions, for example:

Ie: it is not marked as an escape!