Use SQL Plus and runWith on Oracle Database

This page describes how to use Liquibase with the SQL Plus native executor on an Oracle database.

Prerequisites

• Use Liquibase 3.10.0 or later.
• Add SQL Plus to your PATH environment variable. Alternatively, pass its location in the liquibase.sqlplus.conf file or from the command prompt during runtime. See liquibase.sqlplus.path in the SQL Plus integration arguments section.

Note: Liquibase searches the executor location in the following order: runtime arguments, .conf file values, and then your PATH.

Setup

1. Add the runWith attribute to the needed changesets in the changelog:
• SQL: runWith:sqlplus
• XML: runWith="sqlplus"
• YAML: runWith: sqlplus
• JSON: "runWith": "sqlplus"

Tip: For more information, see the runWith and sqlplus examples section.

2. Specify the SQL Plus integration arguments in one of the following ways:
• Pass the values at runtime on the command line
• Add the values to liquibase.sqlplus.conf or the Liquibase properties file.
• Set the values as environment variables
• Run the values as Java system properties (JAVA_OPTS) along with any command at the command prompt:
Windows

set JAVA_OPTS=-Dliquibase.sqlplus.<option>=<value> && liquibase <command> --changelog-file=my_script.sql

macOS/Linux

export JAVA_OPTS=-Dliquibase.sqlplus.<option>=<value> && liquibase <command> --changelog-file=my_script.sql

3. Run a Liquibase command:

Example: liquibase status --changelog-file=my_script.sql

Note: If the command fails, you will receive an error message. However, if you add a property that is not used in Liquibase to the liquibase.sqlplus.conf file, no error occurs. Liquibase only ignores it.

SQL Plus integration arguments

Note: Syntax for each parameter is specified in kebab-case (CLI), camelCase (properties file), and ENVAR_CASE (environment variable).

--sqlplus-args
liquibase.sqlplus.args
LIQUIBASE_SQLPLUS_ARGS

--sqlplus-create-spool
liquibase.sqlplus.createSpool
LIQUIBASE_SQLPLUS_CREATE_SPOOL

--sqlplus-keep-temp
liquibase.sqlplus.keep.temp
LIQUIBASE_SQLPLUS_KEEP_TEMP

--sqlplus-keep-temp-name
liquibase.sqlplus.keep.temp.name
LIQUIBASE_SQLPLUS_KEEP_TEMP_NAME

--sqlplus-keep-temp-overwrite
liquibase.sqlplus.keep.temp.overwrite
LIQUIBASE_SQLPLUS_KEEP_TEMP_OVERWRITE

--sqlplus-keep-temp-path
liquibase.sqlplus.keep.temp.path
LIQUIBASE_SQLPLUS_KEEP_TEMP_PATH

--sqlplus-path
liquibase.sqlplus.path
LIQUIBASE_SQLPLUS_PATH

--sqlplus-sqlerror
liquibase.sqlplus.sqlerror
LIQUIBASE_SQLPLUS_SQLERROR

--sqlplus-timeout
liquibase.sqlplus.timeout
LIQUIBASE_SQLPLUS_TIMEOUT

runWith and sqlplus 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');
	
	...	
	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:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    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-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.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');
	...
	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:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    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-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">					

<changeSet id="2315" author="myauthorname" runWith="sqlplus">  
	<sqlFile
	path="my/path/file.sql"
... > 
</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"
				}
			 }
		   ]
	     }
       }

Best practices

• Do not set the endDelimiter or splitStatements=true property on SQL Plus changesets. SQL Plus handles delimiters and statement splitting natively.
• Prevent hanging queries by configuring the 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 spool files to your temp directory by adding liquibase.sqlplus.keep.temp=true to the liquibase.sqlplus.config file.

Related links

• runWith
• modifyChangeSets
• Using Liquibase with Oracle Database
• Use SQL Plus and Oracle Proxy User
• Using Liquibase and Spring Boot with SQL Plus and Kerberos Authentication