Unexpected error running Liquibase: Invalid SQL type: sqlKind = UNINITIALIZED :new.INSERT_DT := SYSDATE]

Error running liquibase update ( liquibase version 3.5.4 - Oracle 12.1.0.2)

Below sql fails with the error, Invalid SQL type, its validated to execute without any errors in pl/sql, sqlplus.

–liquibase formatted sql

–changeset 1063:error_log_trigger  dbms:oracle

CREATE OR REPLACE TRIGGER Error_Log_trg

           BEFORE INSERT OR UPDATE

                 ON ERROR_LOG

              FOR EACH ROW

            BEGIN

               IF INSERTING THEN

                :new.INSERT_USR    := USER;

            –    :new.INSERT_DT     := SYSDATE;

              –  :new.MODIFY_USR    := null;

              –  :new.MODIFY_DT     := null;

              ELSIF UPDATING THEN

                :new.INSERT_USR    := :old.INSERT_USR;

                :new.INSERT_DT    := :old.INSERT_DT;

                :new.MODIFY_USR    := USER;

             –   :new.MODIFY_DT     := SYSDATE;

              END IF;

           END;

The main thing is that liquibase connects to databases using the appropriate JDBC driver. This limits the SQL that can be used. It isn’t always clear, but some ‘SQL’ is only valid when sent from a vendor tool like SQL*Plus, and will not work in JDBC. In this example, I don’t think you can reference SYSDATE in your SQL. 

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

I have also tried it with the latest version Liquibase 3.6.2, Appreciate any inputs, we are already in production and stuck with deployment 

C:\mine\myprojects\liquibase\liquibase-3.6.2-bin>liquibase --logLevel=debug upda

te

Starting Liquibase at Mon, 06 Aug 2018 10:26:34 CDT (version 3.6.2 built at 2018

-07-03 11:28:09)

Unexpected error running Liquibase: Invalid SQL type: sqlKind = UNINITIALIZED [F

ailed SQL: :new.INSERT_DT     := SYSDATE]

liquibase.exception.MigrationFailedException: Migration failed for change set if

s/trigger_test.sql::ifs_error_log_trigger::d1063:

     Reason: liquibase.exception.DatabaseException: Invalid SQL type: sqlKind =

UNINITIALIZED [Failed SQL: :new.INSERT_DT     := SYSDATE]

        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:637)

        at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53

)

        at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:78)

        at liquibase.Liquibase.update(Liquibase.java:202)

        at liquibase.Liquibase.update(Liquibase.java:179)

        at liquibase.integration.commandline.Main.doMigration(Main.java:1205)

        at liquibase.integration.commandline.Main.run(Main.java:191)

        at liquibase.integration.commandline.Main.main(Main.java:129)

Caused by: liquibase.exception.DatabaseException: Invalid SQL type: sqlKind = UN

INITIALIZED [Failed SQL: :new.INSERT_DT     := SYSDATE]

        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStat

ement(JdbcExecutor.java:356)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:57)

        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:125)

        at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.

java:1229)

        at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdb

cDatabase.java:1211)

        at liquibase.changelog.ChangeSet.execute(ChangeSet.java:600)

        … 7 common frames omitted

Caused by: java.sql.SQLException: Invalid SQL type: sqlKind = UNINITIALIZED

        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:79)

        at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)

        at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:896)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStateme

nt.java:1119)

        at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.ja

va:1737)

        at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1692)

        at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrap

per.java:300)

        at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStat

ement(JdbcExecutor.java:352)

        … 12 common frames omitted

Thank you for a quick response Steve.

Apparently , the issue was to do with the endDelimiter.

I was able to run this successfully with the following changes

  1. Remove the liquibase format tags from sqlfile , end each trigger with a "/ " in a new line.

  2. Add the following to changelog.xml

 

 <sqlFile path=“ifs/trigger_test.sql”  endDelimiter="/" splitStatements=“true” />

 

  1. Run liquibase update

Thanks

Lavanya