runWith changeset attribute
SQL Plus integration and custom executors using
Devs, DBAs, and many others use Liquibase to track their database changes in SQL, XML, JSON or YAML changelogs, almost always processed over traditional JDBC connections. While this works for most people most of the time, there are occasions when the JDBC connector has trouble processing highly-specialized or variable-rich SQL.
For Liquibase Community and Liquibase Pro users, the optional changeset attribute,
runWith="<executor>" allows you to specify an executor to run your SQL. This capability works with changesets in Formatted SQL changelogs, and in changesets which are in XML, JSON, YAML changelogs which call inline SQL or
sqlFile tagged changesets.
SQL Plus integration via
runWith for Liquibase Pro users
For Liquibase Pro users, SQL Plus integration is built-in.
- Make sure you have the latest version of Liquibase (download Liquibase 3.10.0 or higher).
- Have SQL Plus in your PATH (or configure it in the accompanying
runWith="sqlplus"attribute to a changeset.
Formatted SQL changeset using
--liquibase formatted sql --changeset myauthorname:2314 runwith:sqlplus DECLARE l_emp_name VARCHAR2(250); l_emp_no NUMBER; l_salary NUMBER; l_manager VARCHAR2(250); BEGIN INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA'); ...[and so on]... END; /
XML changelog with inline SQL changeset using
<changeSet id="2" author="myauthorname" runWith="sqlplus"> <sql> DECLARE l_emp_name VARCHAR2(250); l_emp_no NUMBER; l_salary NUMBER; l_manager VARCHAR2(250); BEGIN INSERT INTO emp(emp_name,emp_no,salary,manager) VALUES('BBB',1000,25000,'AAA'); ...[and so on]... END; / </sql> </changeSet>
XML/YAML/JSON changelog pointing to an SQL file with raw SQL in it
<changeSet id="2315" author="myauthorname" runWith="sqlplus"> <sqlFile path="my/path/file.sql" ...etc etc... > </changeSet>
For Liquibase Pro users, there is a
liquibase.sqlplus.conf file which lives alongside your
liquibase.properties, where you can optionally specify some useful key-value pairs for configuring your executor, such as:
- Configuration to keep the temporary SQL file generated when using a native executor. For
For example, when the user encounters an error in the native execution, it stops. We throw a ERROR telling them user execution has stopped, and if they would like to know more, they should set
- Configuring the length of timeout, or period to wait for some response from SQL Plus, set:
liquibase.sqlplus.timeout=<number in seconds>
- "–1" disables the timeout & lets you hang yourself
- “0” returns an error
- default is 1800 seconds (30 minutes!)
Open and custom executor extensibility
True to open-source and open-extensibility, Liquibase Community and Liquibase Pro users can write their own classes to integrate their own executor. Say, for example, you need an encrypted JDBC tool, which you want to call as “
ejdbc” from your changeset? We’ve included an example executor with the 3.10.0 release files so you can see exactly how to do the following:
- Write and configure the classes to call your tool.
- Optionally, set up a
.conffile (in this example, it would be named
runWith="ejdbc"to select changesets to process them with your custom executor.