I am writing a liquibase script for MS SQL and Oracle database.
<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">
<![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>
I am getting the following error:
ORA-06550: line 2, column 26:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
:= . ( @ % ; not null range default character
[Failed SQL: (6550) DECLARE
synonym_exists NUMBER]
I tried running the same SQL in DBeaver and it worked.
I don’t understand what’s wrong here. Please correct me.
Exactly, that code is OK as far as Oracle is concerned.
However, as of Liquibase,
how about setting endDelimiter="/"
and terminate that PL/SQL block with a slash (i.e. the endDelimiter character)?
Simplified:
declare
...
begin
...
end;
/ --> this
3