Applies to:
- Liquibase Pro
- Liquibase Open Source (Community)
Conditions:
- Executing raw SQL with SQLPlus
Issue Summary:
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.
Troubleshooting:
Case 1: Update hangs when deploying a raw SQL script using SQLPlus.
- Verify the SQL script has a closing '/'. This character indicates to SQLPlus that the PLSQL block is complete and is ready to be committed to the database. If you do not have a closing '/', the deployment will hang until the value in 'liquibase.sqlplus.timeout' is reached.
- Verify the SQL Plus timeout configured is long enough for your Oracle environment. The default is 10 seconds. In larger environments or when many rows are impacted during an update, 10 seconds for a response may not be long enough. You can provide a custom setting for SQLPlus timeout property 'liquibase.sqlplus.timeout'.
Case 2: Update fails with “object already exists” during a SQLPlus execution of a raw SQL script.
- 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 the 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 it to exit with “object already exists.”
Case 3: Issues with the /
In some cases, using the / as an end delimiter in a SQL script can interfere with other content in the SQL body, such as embedded HTML tags (</b> or </style>), leading to partial deployments or execution failures.
To prevent such issues, you can configure a unique and unlikely end delimiter (e.g., $$$$) for your SQL scripts.
Using a Custom End Delimiter:
- Specify the endDelimiter attribute in the script configuration as follows:
- <sqlFile path="your-script.sql" endDelimiter="$$$$"/>
-
Ensure the script ends with the custom delimiter ($$$$) instead of the default slash (/).
In the script, set the new end delimiter, which Liquibase will use to mark the end of the script.
SQL script with a custom delimiter
BEGIN
INSERT INTO my_table (column1, column2)
VALUES ('<b>bold text</b>', '</style>');
END;
$$$$
Comments
0 comments
Article is closed for comments.