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:
  3. Run a Liquibase command:
  4. 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 and flow file), camelCase (properties file and JAVA_OPTS), and MACRO_CASE (environment variable).

Syntax (--cli, propertiesFile, ENV_VAR) Type Description
--sqlplus-args
liquibase.sqlplus.args
LIQUIBASE_SQLPLUS_ARGS
String

Defines extra arguments to pass to the sqlplus executable. For more information, see SQL Plus documentation.

Note: The delimiter for arguments is a space " ". Do not use a comma "," or semicolon ";".

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

If true, create an Oracle spool file. Default: true.

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

Indicates whether or not to keep a temporary SQL file after the execution of SQL Plus. If true, the file is not deleted. Default: false.

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

Indicates the name of a temporary SQL file after the execution of SQL Plus. If no file name is specified, a name is automatically generated. In Oracle, if no file extension is specified, the file is saved as .lst.

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

Overwrites any files in the specified directory with the same name. Default: true.

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

Specify the path in which to store the temporary files after the execution of SQL Plus. If not specified, the files will be stored in the system's temp directory.

--sqlplus-path
liquibase.sqlplus.path
LIQUIBASE_SQLPLUS_PATH
String

Path to sqlplus executable.

--sqlplus-sqlerror
liquibase.sqlplus.sqlerror
LIQUIBASE_SQLPLUS_SQLERROR
String

Control the outcome when Liquibase returns a SQL error. The error clause is passed directly to Oracle. Default: WHENEVER SQLERROR EXIT FAILURE. Accepted values include:

  • WHENEVER SQLERROR EXIT FAILURE
  • WHENEVER SQLERROR EXIT ROLLBACK
  • WHENEVER SQLERROR EXIT ERROR
  • WHENEVER SQLERROR EXIT WARNING
  • WHENEVER SQLERROR EXIT SUCCESS
--sqlplus-timeout
liquibase.sqlplus.timeout
LIQUIBASE_SQLPLUS_TIMEOUT
Integer

Indicates seconds to wait for the sqlplus timeout. -1 disables the timeout. 0 returns an error. Default: 1800 (30 minutes).

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