Liquibase and PL/SQL with Oracle DB

If you have ever dreamed about Continuous Delivery (CD) you surely thought about deployment which is fully automated. It implies automated management of database scripts. With the help provided by Liquibase it is easy to add some automation to database change sets deployment. However, there is a small downside in Liquibase implementation that makes it a little bit trickier to execute PL/SQL pieces.
Some time ago we decided that automated management of DB scripts is the way we should follow. I had a task to incorporate Liquibase into daily development/deployment. Liquibase is provided in different flavors. You can execute it from a command line as a standalone application. You can also execute it by means of Maven plugin (you just need to add a dependency on that plugin to pom.xml) and that was the preferred way. The project had a whole bunch of SQL scripts which already existed. Rewriting them in order to use Liquibase XML format was not an option. The only format I could use was formatted SQL. The problems appeared when I tried to use PL/SQL code:

--liquibase formatted sql
--changeset myLogin:1.0-13032014-insert-new-xml
declare
xmlString xmltype := xmltype('<example/>');
begin
insert into SAMPLE_XML (ID, TYPE, TEMPLATE) VALUES (1, 'SAMPLE TEMPLATE', xmlString);
commit;
end;
--rollback delete from SAMPLE_XML where TYPE = 'SAMPLE TEMPLATE';

After executing:

mvn liquibase:update

I received an error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following…

It turned out that Liquibase uses ; sign as a statement delimiter by default and executes one statement after another.
Executing:

declare
xmlString xmltype := xmltype('<example/>');

without the rest makes no sense.
The simplest cure is to modify our change set definition:

--liquibase formatted sql
--changeset myLogin:1.0-13032014-insert-new-xml endDelimiter:--GO
declare
xmlString xmltype := xmltype('<example/>');
begin

insert into SAMPLE_XML (ID, TYPE, TEMPLATE) VALUES (1, 'SAMPLE TEMPLATE', xmlString);

commit;
end;
--GO
--rollback delete from SAMPLE_XML where TYPE = 'SAMPLE TEMPLATE';

With that approach we are fine. Liquibase is able to execute PL/SQL scripts against DB. We need to do nothing and this is perfectly fine in development environment. However, there are different requirements in different companies when it comes to certification/production database management. It is pretty common that PROD servers are managed by a different team – operations (OPS). You just deliver DB scripts and they execute them against DB. You need to provide them SQL updates in an incremental manner. If you want to generate SQL scripts rather than perform the real DB manipulation you can type:

mvn liquibase:updateSQL

and SQL file will be generated (databasechangelog table used by Liquibase will be scanned to check what was already executed). However, if the change set contains some PL/SQL fragments – generated file will contain a bug. Please note that the correct PL/SQL syntax is:

declare
…
begin
…
commit;
end;
/

/ sign was not included in the change set because Liquibase returns an error when it encounters that sign during script execution. On the other hand that sign is necessary for a proper script execution from SQL Developer or SQL Plus. There is a workaround. You can just replace:

end;

with:

end;
/

You can do it using maven-replacer-plugin (I include a full Maven configuration from pom.xml):

<build>
        <plugins>
            <plugin>
                <groupId>org.liquibase</groupId>
                <artifactId>liquibase-maven-plugin</artifactId>
                <version>3.1.1</version>
                <configuration>
                    <changeLogFile>/liquibase/changelog.xml</changeLogFile>
                    <driver>oracle.jdbc.OracleDriver</driver>
                    <url>${liquibase.db.url}</url>
                    <username>${liquibase.username}</username>
                    <password>${liquibase.password}</password>
                </configuration>
            </plugin>
        </plugins>
    </build>
<profiles>
        <profile>
            <id>generate-sql-migration-script</id>
            <build>
                <plugins>
                    <plugin>
                        <groupId>org.liquibase</groupId>
                        <artifactId>liquibase-maven-plugin</artifactId>
                        <version>3.1.1</version>
                        <executions>
                            <execution>
                                <phase>process-resources</phase>
                                <goals>
                                    <goal>updateSQL</goal>
                                </goals>
                            </execution>
                        </executions>
                    </plugin>

                    <plugin>
                        <groupId>com.google.code.maven-replacer-plugin</groupId>
                        <artifactId>replacer</artifactId>
                        <version>1.5.2</version>
                        <executions>
                            <execution>
                                <phase>process-resources</phase>
                                <goals>
                                    <goal>replace</goal>
                                </goals>
                            </execution>
                        </executions>

                        <configuration>
                            <file>${project.basedir}/target/liquibase/migrate.sql</file>
                            <replacements>
                                <replacement>
                                    <token>end;</token>
                                    <value>end;\n/\n</value>
                                    <unescape>true</unescape> <!-- Otherwise new line sign will be escaped -->
                                </replacement>
                            </replacements>
                        </configuration>
                    </plugin>
                </plugins>
            </build>
        </profile> 

Thanks to that configuration you can allow Liquibase to execute SQL script automatically (good for DEV deployments):

mvn clean install

as well as generate SQL scripts (good for PROD deployments):

mvn process-resources –P generate-sql-migration-script