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:
Any thoughts / ideas will be greatly appreciated.
A table's triggers are dropped when you drop the table. A standalone sequence created with create sequence is not associated with any table, but an internal sequence that is part of an identity column is dropped with the table, so it depends which you mean.
Use the DROP SEQUENCE statement to remove a sequence from the database. You can also use this statement to restart a sequence by dropping and then re-creating it.
select 'drop table ' || table_name || ';' from user_tables; select 'drop sequence ' || sequence_name || ';' from user_sequences; This will generate the script to drop all the tables and sequences.
The syntax to a drop a sequence in Oracle is: DROP SEQUENCE sequence_name; sequence_name. The name of the sequence that you wish to drop.
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
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;
/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With