oracle - 如何将 SQL 触发器转换为 liquibase

Question

我正在尝试迁移我们当前的数据库以使用 liquibase，但我不明白如何将以下内容转换为 liquibase 可以使用的内容。

--liquibase formatted sql

--changeset usr:33 splitStatements:false dbms:oracle failOnError:true
create trigger some_trigger_id before insert
on table1 for each row
declare
    seq_val number;
begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual;
    else
      begin
        select seq_myseq.currval
        into myid
        from dual;
      exception
        when others then
          raise_application_error(-20000, 'Illegal!! ...');
      end;

      if :new.myid > seq_val then
        raise_application_error(-20000, 'Illegal!! ...');
      end if;
    end if;
end;
/  

我曾尝试将此代码放在一个单独的文件中并将其包含在 <sqlFile> 中，但它失败并显示消息。

"Encountered the symbol end of file when when expecting one of the following ;"

当我检查 oracle 时，创建了一个触发器，但代码被删除，如下所示:

create or replace trigger some_trigger_id before insert
on table1 for each row
declare
    seq_val number

代码已被截断为;，就好像属性splitStatements被忽略了，如果我替换;也没关系。用其他一些符号我得到同样的错误？？？

更新:这是我得到的确切错误。

SEVERE 2015-06-25 08:21: liquibase: src/main/resources/db-schema.xml: src/main/resources/db-schema.xml::1435144676490-15::a187001: Change Set src/main/resources/d
b-schema.xml::1435144676490-15::a187001 failed.  Error: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;
 [Failed SQL: begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual]
liquibase.exception.DatabaseException: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;
 [Failed SQL: begin
    if :new.myid is null then
      select seq_myseq.nextval
      into :new.myid
      from dual]
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:316)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:122)
        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1243)
        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1226)
        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:548)
        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73)
        at liquibase.Liquibase.update(Liquibase.java:208)
        at liquibase.Liquibase.update(Liquibase.java:188)
        at liquibase.Liquibase.update(Liquibase.java:323)
        at org.liquibase.maven.plugins.LiquibaseUpdate.doUpdate(LiquibaseUpdate.java:33)
        at org.liquibase.maven.plugins.AbstractLiquibaseUpdateMojo.performLiquibaseTask(AbstractLiquibaseUpdateMojo.java:30)
        at org.liquibase.maven.plugins.AbstractLiquibaseMojo.execute(AbstractLiquibaseMojo.java:394)
        at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:133)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:208)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
        at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:108)
        at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:76)
        at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51)
        at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:116)
        at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:361)
        at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:155)
        at org.apache.maven.cli.MavenCli.execute(MavenCli.java:584)
        at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:213)
        at org.apache.maven.cli.MavenCli.main(MavenCli.java:157)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289)
        at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229)
        at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415)
        at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356)
Caused by: java.sql.SQLException: ORA-06550: line 5, column 15:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:194)
        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1882)
        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1847)
        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:301)
        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:314)
        ... 34 more

解决方案:
解决方案是明确说明 endDelimiter\n;

Answer 1

使用自定义 endDelimiter(默认为分号，因此 liquibase 试图将您的代码拆分为不正确的部分)。

<changeSet id="1" author="me">
    <sql endDelimiter="/">
        create trigger some_trigger_id before insert
        on table1 for each row
        declare
            seq_val number;
        begin
            if :new.myid is null then
              select seq_myseq.nextval
              into :new.myid
              from dual;
            else
              begin
                select seq_myseq.currval
                into myid
                from dual;
              exception
                when others then
                  raise_application_error(-20000, 'Illegal!! ...');
              end;

              if :new.myid > seq_val then
                raise_application_error(-20000, 'Illegal!! ...');
              end if;
            end if;
        end;
        / 
    </sql>
</changeSet>