Improvements to SQLcl Engine

Liquibase is an open-source database independent library for tracking, managing, and applying database schema changes.
 
Oracle SQLcl (SQL Developer Command Line) is a small, lightweight, Java-based command-line interface for Oracle Database. SQLcl provides inline editing, statement completion, command recall, and also supports existing SQL*Plus scripts. You can download SQLcl from oracle.com and it is installed in the OCI Cloud Shell by default.
 
The Liquibase feature in SQLcl enables you to execute commands to generate a changelog for a single object or for a full schema (changeset and changelogs). We also add Oracle specific features and enhancements to Liquibase in SQLcl.
 
When your changelog got a set of SQL or PL/SQL command
 
demo@PDB1> $ type tell_me_hello.sql
begin
 dbms_output.put_line('Hello_world');
end;
/
 
demo@PDB1> $ type controller.xml
<?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"
                xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
 
        <changeSet author="liquibase-docs" id="tell_me_hello"
                                runOnChange="true" runAlways="true" failOnError="true" >
                <sqlFile  dbms="oracle"
                                  encoding="utf8"
                                  endDelimiter="/"
                                  path="tell_me_hello.sql"
                                  relativeToChangelogFile="true"
                                  splitStatements="true"
                                  stripComments="true"
                />
        </changeSet>
</databaseChangeLog>
 
demo@PDB1>
 
the execution of changesets goes fine like this.
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:02:27.864180300 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Liquibase: Update has been successful. Rows affected: 1
 
 
Operation completed successfully.
 
Hello_world
 
 
But the real challenge arises, when your changeset got references to SQLcl specific commands in the scripts.
 
begin
 dbms_output.put_line('Hello_world');
end;
/
info EMP
 
then the execution of changesets, will fail like this
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:06:47.741351200 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
2024-11-26 13:06:49.602 SEVERE liquibase.changelog ChangeSet controller.xml::tell_me_hello::liquibase-docs encountered an exception.
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
An error has occurred:
liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset controller.xml::tell_me_hello::liquibase-docs:
     Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement
 
https://docs.oracle.com/error-help/db/ora-00900/ [Failed SQL: (900) info EMP]
ORA-00900: invalid SQL statement
 
Hello_world
 
 
And this is where the recent enhancement to SQLcl will help us.
 
demo@PDB1> lb show
--Starting Liquibase at 2024-11-26T13:09:21.619566200 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Liquibase stored parameters
FILE_ENCODING                 UTF-8
SKIP_ROLLBACK                 OFF
SAVE_CAPTURE                  OFF
ENGINE                        JDBC
SHORTEN_SPLIT                 OFF
SCAN                          OFF
SQLBLANKLINES                 ON
 
 
Operation completed successfully.
 
 
 
By default, we execute the SQL using the standard JDBC and it doesn’t know what INFO command does.
 
When we flip the ENGINE to SQLcl, that will execute all the commands in the changesets using SQLcl engine.
 
demo@PDB1> lb set engine SQLCL
--Starting Liquibase at 2024-11-26T13:43:49.884092600 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
 
 
Operation completed successfully.
 
demo@PDB1> lb show
--Starting Liquibase at 2024-11-26T13:43:55.302563700 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Liquibase stored parameters
FILE_ENCODING                 UTF-8
SKIP_ROLLBACK                 OFF
SAVE_CAPTURE                  OFF
ENGINE                        SQLCL
SHORTEN_SPLIT                 OFF
SCAN                          OFF
SQLBLANKLINES                 ON
 
 
Operation completed successfully.
 
demo@PDB1>
 
demo@PDB1> lb up -chf controller.xml
--Starting Liquibase at 2024-11-26T13:45:16.097107100 (version 4.25.0.305.0400 #0 built at 2024-10-31 21:25+0000)
Running Changeset: controller.xml::tell_me_hello::liquibase-docs
Hello_world
 
 
PL/SQL procedure successfully completed.
 
 
TABLE: EMP
         LAST ANALYZED:2024-10-30 10:02:45.0
         ROWS         :14
         SAMPLE SIZE  :14
         INMEMORY     :DISABLED
         COMMENTS     :
 
Columns
NAME         DATA TYPE           NULL  DEFAULT    COMMENTS
 EMPNO       NUMBER(4,0)         Yes
 ENAME       VARCHAR2(10 BYTE)   Yes
 JOB         VARCHAR2(9 BYTE)    Yes
 MGR         NUMBER(4,0)         Yes
 HIREDATE    DATE                Yes
 SAL         NUMBER(7,2)         Yes
 COMM        NUMBER(7,2)         Yes
 DEPTNO      NUMBER(2,0)         Yes
 
 
 
 
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
 
Liquibase: Update has been successful. Rows affected: 1
 
 
Operation completed successfully.
 
demo@PDB1>