在删除不存在的序列、创建现有用户时防止出错

2021-12-24 00:00:00 sql oracle liquibase plsql

我有一堆用于创建/删除序列、用户和其他对象的 sql 脚本.我正在通过 liquibase 运行这些脚本，但它们失败了，因为当我尝试删除不存在的序列或创建现有用户时，oracle 会抱怨.

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.

有没有oracle方法来防止错误发生?

Is there an oracle way to prevent errors from happening?

类似的东西

如果不存在则创建用户/序列

Create User / Sequence if not exists

删除用户/安全如果存在

Drop User/ Secuence if exists

据我所知，我有以下选择:

As far as I know, I have these options:

  • 编写 plsql 脚本
  • 使用 liquibase 上下文.
  • 使用 liquibase 前提条件，但这意味着工作量太大.

任何想法/想法将不胜感激.

Any thoughts / ideas will be greatly appreciated.

推荐答案

Liquibase 有一个 failOnError 属性，您可以在包含可能失败的调用的 changeSet 上将其设置为 false.

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.

这种方法的缺点是它也会将它们标记为已运行并在它们由于其他原因(错误权限、连接失败、无效 SQL 等)出错时继续.更准确的方法是使用前提条件，像这样:

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>

当前没有 userExists 前提条件，但您可以创建自定义前提条件或回退到前提条件.有关文档，请参阅http://www.liquibase.org/documentation/preconditions.html

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

相关文章