Troubleshoot Running Raw SQL Using SQLPlus
Issues sometimes arise when running raw SQL with SQLPlus. This is caused by SQLPlus behavior that occurs when a SQL script is configured incorrectly. Learn how to troubleshoot this issue in the two examples below. Learn more about the rules for SQLPlus on Oracle’s documentation here.
Case 1: Liquibase update hangs when deploying a raw SQL script using SQLPlus.
Troubleshoot
Verify the SQL script has a closing /. This character indicates to SQLPlus that the PL/SQL block is complete and is ready to be committed to the database. If you do not have a closing /, Liquibase will hang until the value in liquibase.sqlplus.timeout is reached.
Note: Learn more here: Use Native Executors with Oracle Database
Verify the SQL Plus timeout configured for Liquibase is long enough for your Oracle environment. The default is 10 seconds. In larger environments or when many rows are impacted during update, 10 seconds for a response may not be long enough. You can provide a custom setting for Liquibase SQLPlus timeout property liquibase.sqlplus.timeout.
Case 2: Liquibase update fails with "object already exists" during a SQLPlus execution of a raw SQL script.
Troubleshoot
Verify the SQL script is not duplicating logic to create an object that already exists on the database. Depending on your version of Oracle and the object impacted, create or replace syntax can be provided to overcome an "object already exists" error.
Verify the SQL script does not include a / after non-PL/SQL blocks. SQLPlus does not support use of the / with non-PL/SQL blocks. For example, a SQL script containing a CREATE TABLE and a CREATE TRIGGER should not include the / character at the end of the create table SQL because CREATE TABLE is not PL/SQL. Including / after CREATE TABLE causes Liquibase to exit with "object already exists."
