Liquibase vs SQLPLUS for SPOOL output.

General Discussion
1

Hello,

I am currently migrate from oracle sqlplus standard usage to liquibase. But during this migration, my ‘.sql’ file have some declaration off spool file and liquibase don’t like them:

I did a simple test sql file like

SQL\03.05\57878\pa_ng_123389_BBA_001_rtg_57878.sql

SPOOL my_spool.txt

select sysdate from DUAL;

SPOOL off

without success :(.

Error message

SEVERE 18/01/17 09:11: liquibase: SQL/03.05/57878/afpl_db_changelog_57878.xml::5

7878::rtg: ORA-00900: instruction SQL non valide

 [Failed SQL: SPOOL my_spool.txt

select sysdate from DUAL]

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

L/03.05/57878/afpl_db_changelog_57878.xml::57878::rtg:

     Reason: liquibase.exception.DatabaseException: ORA-00900: instruction SQL n

on valide

 [Failed SQL: SPOOL my_spool.txt

select sysdate from DUAL]

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

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

)

Some one can help me ?

If spool is not available with liquibase, maybe the option ‘–logLevel’ can help me to have sql execution output ?

My liquibase commande is:

java -jar liquibase.jar </div>

      --driver=oracle.jdbc.OracleDriver </div>

      --changeLogFile=SQL/03.05/57878/afpl_db_changelog_57878.xml </div>

      --url=“jdbc:oracle:thin:@plvuatdb01:1522:PLVH03” </div>

      --classpath=ojdbc6.jar </div>

      --username=xxx</div>

      --password=xxx</div>

       --logLevel=info </div>

      update

SQL/03.05/57878/afpl_db_changelog_57878.xml

<databaseChangeLog

    xmlns=“http://www.liquibase.org/xml/ns/dbchangelog”

    xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”

    xmlns:ext=“http://www.liquibase.org/xml/ns/dbchangelog-ext”

    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd

    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

 

 

   

 

Regards,

2

Are you able to solve this problem? If then, please share me the steps

3

When using Liquibase keep in mind, that it is executing all sql commands directly via JDBC. So all non JDBC conform commands will throw a error.

I know many DBMS have command line tools with special command sets (sqlplus or db2 cli are examples) but their extensions are not valid sql and can not be executed by liquibase. You either have to rewrite the sql files or change them in some kind of preprocessing (search and replace the suspect lines).