runWith changeset attribute

SQL Plus integration and custom executors using runWith attribute

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.

  1. Make sure you have the latest version of Liquibase (download Liquibase 3.10.0 or higher).
  2. Have SQL Plus in your PATH (or configure it in the accompanying liquibase.sqlplus.conf file).
  3. Add runWith="sqlplus" attribute to a changeset.

runWith examples:

Formatted SQL changeset using runWith attribute 


--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 runWith attribute 

<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>

The optional .conf file

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:

  1. Configuration to keep the temporary SQL file generated when using a native executor. For sqlplus:

liquibase.sqlplus.keep.temp=[true|false=default]

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

liquibase.sqlplus.keep.temp=true

  1. 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 .conf file (in this example, it would be named liquibase.ejdbc.conf).
  • Add runWith="ejdbc" to select changesets to process them with your custom executor.