Best practices when using native executors

endDelimiter and splitStatements

When executing with JDBC it may be necessary to specify endDelimiter to indicate where a SQL command ends. This is important for JDBC to know because there could be non-standard end delimiters, especially when authoring changesets containing stored logic objects such as procedures, triggers, and views.

Also with JDBC, there may be a need to use splitStatements="true" to allow Liquibase to split SQL statements on semicolons (";") and "GO" commands.

Using native executors eliminates the need to use these properties and has the built-in capability to handle scripts consisting of more than one SQL command.

Prevent Timeouts

There are instances when SQL commands will take a long time to execute or just hang due to bad syntax. To avoid this, use timeout settings that can be configured for each executor.

SQLPlus

Timeout for SQLPlus can be configured with Liquibase in several ways:

Create the following property in a config file called liquibase.sqlplus.conf:

• liquibase.sqlplus.timeout=nn where nn is the time in seconds to wait before stopping the process.

As an environment variable:

• LIQUIBASE_SQLPLUS_TIMEOUT=nn where nn is the time in seconds to wait before stopping the process.

SQLCMD

Timeout for SQLCMD can be configured with Liquibase in several ways:

Create the following property in a config file called liquibase.sqlcmd.conf:

• liquibase.sqlcmd.timeout=nn where nn is the time in seconds to wait before stopping the process.

As an environment variable:

• LIQUIBASE_SQLCMD_TIMEOUT=nn where nn is the time in seconds to wait before stopping the process.

Timeout for PSQL can be configured with Liquibase in several ways:

Create the following property in a config file called liquibase.psql.conf:

• liquibase.psql.timeout=nn where nn is the time in seconds to wait before stopping the process.

As an environment variable:

• LIQUIBASE_PSQL_TIMEOUT=nn where nn is the time in seconds to wait before stopping the process.

Prevent User Prompts

SQL developers may write scripts that pause mid-execution and prompts the user for input. If prompts are left in SQL scripts, automation will appear to hang with no way for the user to provide input. User prompts need to be removed from SQL scripts.

For example, a user may be accustomed to writing scripts using PROMPT and ACCEPT commands in Oracle: -- Begin script PROMPT Altering to PP_DEV container ALTER SESSION SET CONTAINER=PP_DEV; PROMPT Provide table name ... ACCEPT table_name CHAR PROMPT '> ' CREATE TABLE DATICAL_USER.&table_name (ID int); -- End script

Executing this type of script will pause and wait for typed user input, followed by pressing the RETURN key to resume execution. These prompts are not automation-friendly and need to be eliminated.

Temp Files

Liquibase Secure creates temporary files during a native executor run. The *.keep.temp.name property saves the wrapper script that Liquibase uses to execute runWith changesets. Liquibase Secure allows for this by using the following properties:

In the liquibase.sqlplus.conf config files, the properties are:

• liquibase.sqlplus.keep.temp=true (default: false)

• liquibase.sqlplus.keep.temp.name=<temp file name>

• liquibase.sqlplus.keep.temp.path=<path to directory>

In the liquibase.sqlmd.conf config file, the properties are:

• liquibase.sqlcmd.keep.temp=true (default: false)

• liquibase.sqlcmd.keep.temp.name=<temp file name>

• liquibase.sqlcmd.keep.temp.path=<path to directory>

When configured to save the temp file without specifying the keep.temp.path, Liquibase will save temp files in the temp folder such as /tmp on Linux.

Corresponding Environment Variables for SQLPlus are:

• LIQUIBASE_SQLPLUS_KEEP_TEMP=true (or false)

• LIQUIBASE_SQLPLUS_KEEP_TEMP_NAME=<temp file name>

• LIQUIBASE_SQLPLUS_KEEP_TEMP_PATH=<path to directory)

Corresponding Environment Variables for SQLCMD are:

• LIQUIBASE_SQLCMD_KEEP_TEMP=true (or false)

• LIQUIBASE_SQLCMD_KEEP_TEMP_NAME=<temp file name>

• LIQUIBASE_SQLPLUS_KEEP_TEMP_PATH=<path to directory>