PL/SQL and SQL script in one sqlFile with liquibase for Oracle

[arjaxs]

Hello Team,

We are trying to run a Stored procedure and a SQL statement in the same liquibase changeset using Liquibase 3.8.0 and getting below error.

change set

SQL FILE
DECLARE
e_error EXCEPTION;
PRAGMA exception_init ( e_error,-00942 );
BEGIN
EXECUTE IMMEDIATE 'drop table XXXX.AD_GROUP_ROLE_MAPPING';

EXCEPTION
WHEN e_error THEN
NULL;
END;

/
CREATE TABLE XXXX.AD_GROUP_ROLE_MAPPING
(
AD_GROUP_NAME VARCHAR2(200) NOT NULL,
ROLE_ID NUMBER,
PRIMARY KEY (AD_GROUP_NAME)
);
Error
Unexpected error running Liquibase: ORA-06550: line 12, column 2:
PLS-00103: Encountered the symbol "/"
[Failed SQL: (6550) DECLARE
e_error EXCEPTION;
PRAGMA exception_init ( e_error,-00942 );
BEGIN
EXECUTE IMMEDIATE 'drop table XXXX.AD_GROUP_ROLE_MAPPING';

EXCEPTION
WHEN e_error THEN
NULL;
END;

/
Could you please help?

Thanks & Regards
Arif

┆Issue is synchronized with this Jira Bug by Unito

[Shuumi]

Hello,

It worked for me after a few changes. Here is what it looks like:

Changelog

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet id="1"
               author="krzysd">
        <sqlFile path="testSql.sql" relativeToChangelogFile="true" endDelimiter="/"/>
    </changeSet>

</databaseChangeLog>

SQL File

DECLARE
e_error EXCEPTION;
PRAGMA exception_init ( e_error,-00942 );
BEGIN
EXECUTE IMMEDIATE 'drop table AD_GROUP_ROLE_MAPPING';

EXCEPTION
WHEN e_error THEN
NULL;
END;

/
CREATE TABLE AD_GROUP_ROLE_MAPPING
(
AD_GROUP_NAME VARCHAR2(200) NOT NULL,
ROLE_ID NUMBER,
PRIMARY KEY (AD_GROUP_NAME)
)

There are two important changes.
First in the changelog. I set the "endDelimiter" to "/" because that is what you use to split the statements. After this it will stop whining about the unexpected symbol.
Second I removed the semicolon in the SQL File after "CREATE TABLE ..." statement. This is needed because semicolon is not the end delimiter and liquibase expects pure SQL statements to be written without the semicolon.