Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

plsql works in Sql Developer, but not in a liquibase change ORA-06550 PLS-00103

I have the following code that has no issue running in SQL Developer, but when I put inside a liquibase change and run it I get an error.

DECLARE
  seqval NUMBER;
BEGIN
  SELECT MAX(id) + 1 INTO seqval FROM T_SLS_ITEMS;
  execute immediate('CREATE SEQUENCE SEQ_SLS_ITEMS MINVALUE '||seqval||'');
END;

and the changeset for it:

     <changeSet author="Cristian Marian (cmarian)" id="2019-05-24-171101 Fix Items sequence - creting">
        <sql>
          DECLARE
            seqval NUMBER;
          BEGIN
            SELECT MAX(id) + 1 INTO seqval FROM T_SLS_ITEMS;
            execute immediate('CREATE SEQUENCE SEQ_SLS_ITEMS MINVALUE '||seqval||'');
          END;
        </sql>
    </changeSet>

The error looks like this:

Reason: liquibase.exception.DatabaseException: ORA-06550: line 2, column 27:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character
 [Failed SQL: DECLARE
              seqval NUMBER] (Liquibase Update failed.)
like image 636
Cristian Marian Avatar asked May 27 '19 07:05

Cristian Marian


1 Answers

Liquibase will try to split up the "script" inside the <sql> tag by the default statement delimiter which is ;. For a PL/SQL block this is obviously wrong, as the whole block needs to be treated as single statement.

To achieve that, use splitStatements attribute in the <sql> tag:

<sql splitStatements="false">
  DECLARE 
   .... 
  END;
</sql>
like image 74
a_horse_with_no_name Avatar answered Oct 03 '22 14:10

a_horse_with_no_name