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.
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: The liquibase.sqlplus.conf
file can be used along with your liquibase.properties
file.
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 includeliquibase.sqlplus.path=/path/to/sqlplus
property and others. See SQL Plus arguments examples. - Add the
runWith="sqlplus"
attribute to a changeset. 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. 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
<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>
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 namedliquibase.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, addliquibase.sqlplus.timeout=nn
, wherenn
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.tmp=true
to yourliquibase.sqlplus.config
file.