`runWith` changeset attribute

Using Liquibase to track database changes in SQL, XML, JSON, or YAML changelogs is almost always processed over traditional JDBC connections. However, there might be times when the JDBC connection (or executor) has trouble processing highly specialized or variable-rich SQL, and you might want to use a different executor.

For Liquibase Community and Liquibase Pro users, the optional changeset attribute runWith="<executor>" allows you to specify a custom executor to run your SQL. The runWith="<executor>" capability works with changesets in formatted SQL changelogs and with XML, JSON, YAML changelogs that call inline SQL or sqlFile tagged changesets.

SQL Plus integration and `runWith` attribute for Liquibase Pro users

For Liquibase Pro users, SQL Plus integration is built in. To use the full functionality of SQL Plus integration, you need to:

Use Liquibase 3.10 and later.
Specify how Liquibase can find SQL Plus by adding SQL Plus to your PATH. Alternatively, you can pass its location either in the liquibase.sqlplus.conf file or on the command line during runtime.

Note: Liquibase searches the SQL Plus location in the following order: runtime arguments, .conf file values, your PATH.

Add the runWith:sqlplus attribute to a changeset if you use an SQL changelog or runWith="sqlplus" for an XML changelog. You can also add "runWith": "sqlplus" for JSON and runWith: sqlplus for YAML changelogs. See runWith examples.

Using the `.conf` file with SQL Plus

For Liquibase Pro users, there is the liquibase.sqlplus.conf file, which is used to pass arguments to your sqlplus executor when running Liquibase Pro. In this file, you can specify key-value pairs for configuring your executor.

Note: You can use the liquibase.sqlplus.conf file along with your liquibase.properties file. If you use Liquibase Pro and SQL Plus in automation, you can set and pass arguments in the liquibase.sqlplus.conf file instead of the command line.

To configure values in your liquibase.sqlplus.conf file and use it, follow these steps:

Create a new property to be stored in liquibase.sqlplus.conf to pass along the arguments. For example, you can include liquibase.sqlplus.path=/path/to/sqlplus property and others. See SQL Plus arguments examples.
Add the runWith:sqlplus attribute to a changeset if you use an SQL changelog or runWith="sqlplus" for an XML changelog. You can also add "runWith": "sqlplus" for JSON and runWith: sqlplus for YAML changelogs. See runWith examples.
Run a Liquibase command. For example:

liquibase --changeLogFile=SQLPLUS_SCRIPT.sql update

Note: If the command fails, you will receive an error message; however, if you add the property that is not used in Liquibase to the liquibase.sqlplus.conf file, no error will occur. The application will only ignore it.

Using the CLI with SQL Plus

Ensure the SQL Plus path is set to a location in the PATH System variable or configured in the liquibase.sqlplus.conf file.
Add the runWith:sqlplus attribute to a changeset if you use an SQL changelog or runWith="sqlplus" for an XML changelog. You can also add "runWith": "sqlplus" for JSON and runWith: sqlplus for YAML changelogs. See runWith examples.
Pass your liquibase.sqlplus.<option> on the CLI as java system properties (JAVA_OPTS) and run a Liquibase command. For example:

export JAVA_OPTS=-Dliquibase.sqlplus.path=/path/to/sqlplus && liquibase --changeLogFile=SQLPLUS_SCRIPT.sql update

SQL Plus arguments examples

To include the path for your sqlplus executor, configure the following:

liquibase.sqlplus.path=/path/to/sqlplus

To keep the temporary SQL file generated when using a native executor for the sqlplus executor, configure the following:

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

Note: If you encounter an error in the native execution, it stops, and Liquibase sends an error message. If you want to know more information about the error, set your liquibase.sqlplus.keep.temp to true:

liquibase.sqlplus.keep.temp=true

To configure the length of timeout, or period to wait for the response from SQL Plus, set the following:

liquibase.sqlplus.timeout=<number in seconds>

"–1" disables the timeout
"0" returns an error
1800 seconds (30 minutes) is the default value

runWith examples

Formatted SQL changeset using the 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 the inline SQL changeset using the runWith attribute

<?xml version="1.0" encoding="UTF-8"?>

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

<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 changelog pointing to an SQL file with raw SQL in it

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"	
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd
    http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.4.xsd ">						

<changeSet id="2315" author="myauthorname" runWith="sqlplus">  
	<sqlFile
	path="my/path/file.sql"
...etc etc... > 
</changeSet>

YAML changelog pointing to an SQL file with raw SQL in it

databaseChangeLog:
- changeSet:
	id: 1-yaml
	author: myauthorname
	runWith: sqlplus
	changes:
	- sqlFile:
		path: person.sql
		relativeToChangelogFile: true

JSON changelog pointing to an SQL file with raw SQL in it

{
	"databaseChangeLog": [
	   {
		"changeSet": {
		  "id": "1-json",
		  "author": "myauthorname",
		  "runWith": "sqlplus",
		  "changes": [
			{
			      "sqlFile":{
				      "path": "person.sql",
				      "relativeToChangelogFile": "true"
				}
			 }
		   ]
	     }
       }

Open and custom executor extensibility

Using Liquibase Community and Liquibase Pro, you can write your classes to integrate your custom executor. For example, you might need an encrypted JDBC tool, which you want to call ejdbc from your changeset. Starting from version 3.10.0, there is an example executor with the release files so you can see exactly how to do the following:

Write and configure the classes to call your tool.
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.

SQL Plus best practices

Do not set the endDelimiter property on SQL Plus changesets. SQL Plus handles this natively.
Do not set the splitStaments=true property on SQL Plus changesets. SQL Plus handles statement splitting.
Prevent queries from hanging indefinitely by configuring an SQL Plus timeout. In your liquibase.sqlplus.config file, add liquibase.sqlplus.timeout=nn, where nn is time in seconds to wait before stopping the process.
Save the output of your SQL Plus spool files to your temp directory by adding liquibase.sqlplus.keep.temp=true to your liquibase.sqlplus.config file.

runWith changeset attribute

SQL Plus integration and runWith attribute for Liquibase Pro users