Liquibase跨库脚本Oracle端PLS-00103错误排查与修正请求

问题根源在于Liquibase的默认SQL语句分割机制：它会把PL/SQL块内的分号识别为单条SQL的结束符，导致PL/SQL代码被提前截断，只执行了DECLARE synonym_exists NUMBER部分，触发语法错误。

修复方案1：禁用SQL语句分割 #

给Oracle对应的<sql>标签添加splitStatements="false"属性，让Liquibase完整执行整个PL/SQL块，不再按分号分割：

<changeSet author="root" id="CUSTOMER_SYNONYM" runOnChange="true">
    <preConditions onFail="MARK_RAN">
        <or>
            <dbms type="oracle"/>
            <dbms type="mssql"/>
        </or>
    </preConditions>
    <sql dbms="mssql">
        <![CDATA[
            IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = 'CUSTOMER_SYNONYM')
            BEGIN
            EXEC('CREATE SYNONYM CUSTOMER_SYNONYM FOR PLT.CUSTOMER');
            END;
        ]]>
    </sql>
    <sql dbms="oracle" splitStatements="false">
        <![CDATA[
            DECLARE
                synonym_exists NUMBER;
            BEGIN
                SELECT COUNT(*)
                INTO synonym_exists
                FROM all_synonyms
                WHERE synonym_name = 'CUSTOMER_SYNONYM' AND owner = 'PLT';

                IF synonym_exists = 0 THEN
                    EXECUTE IMMEDIATE 'CREATE SYNONYM CUSTOMER_SYNONYM FOR PLT.CUSTOMER';
                END IF;
            END;
        ]]>
    </sql>
</changeSet>

修复方案2：使用Liquibase内置同义词创建标签 #

更简洁可靠的方式是用Liquibase原生的<createSynonym>标签，无需手写数据库特定SQL，自动适配Oracle和MSSQL：

<changeSet author="root" id="CUSTOMER_SYNONYM" runOnChange="true">
    <preConditions onFail="MARK_RAN">
        <or>
            <dbms type="oracle"/>
            <dbms type="mssql"/>
        </or>
        <!-- 仅当同义词不存在时执行 -->
        <not>
            <synonymExists synonymName="CUSTOMER_SYNONYM" schemaName="PLT"/>
        </not>
    </preConditions>
    <createSynonym synonymName="CUSTOMER_SYNONYM" baseTableSchemaName="PLT" baseTableName="CUSTOMER"/>
</changeSet>

该方案由Liquibase自动生成对应数据库的正确语句，避免手写PL/SQL的语法陷阱。

内容的提问来源于stack exchange，提问作者Hisham Gundagi