Liquibase Stored Procedure throws SQL syntax error

Question

I'm developping an app running stored procedures on an Oracle database. It works fine but now i'm trying to run procedures with Liquibase to have an independant environment and I can't get it to work. Even the simpliest 'Hello world' example gives me a SQL syntax error.

I'm just giving you the xml and error for now because i don't think it comes from the Spring part since it's working with the DB. The 'hello world' procedure itself works fine if I run it on SqlDeveloper.

Any help would be appreciated, I really don't have a clue what's wrong with this. Thank you !

The procedure :

<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">

    <changeSet id="PROC_PKG_ACCEPTOR_P_ACCEPTORMISCINIT-create"
               author="sth" runOnChange="true">
        <createProcedure dbms="h2, oracle"
                         encoding="UTF-8"
                         procedureName="TESTHELLO"
                         relativeToChangelogFile="true">
            CREATE OR REPLACE PROCEDURE TESTHELLO
            IS
            BEGIN
            DBMS_OUTPUT.PUT_LINE('Hello World!');
            END;
        </createProcedure>
    </changeSet>
</databaseChangeLog>

The error :

Reason: liquibase.exception.DatabaseException: Erreur de syntaxe dans linstruction SQL {0}; attendu {1}
Syntax error in SQL statement {0}; expected {1}; SQL statement:
CREATE OR REPLACE PROCEDURE TESTHELLO
            IS
            BEGIN
            DBMS_OUTPUT.PUT_LINE('Hello World!');
            END [42001-197] [Failed SQL: CREATE OR REPLACE PROCEDURE TESTHELLO
            IS
            BEGIN
            DBMS_OUTPUT.PUT_LINE('Hello World!');
            END]

That create procedure will not work with H2, so most probably that's where the error comes from. — a_horse_with_no_name
Ok thanks, do you know the correct syntax ? I'm not really familiar to SQL... — Sylvain Thibault
Notice that the correct syntax for stored procedures is CREATE OR ALTER rather than CREATE OR REPLACE that is used for tables — Milana

Sylvain Thibault Sylvain Thibault · Accepted Answer · 2019-12-02T10:22:40

http://www.h2database.com/html/features.html#user_defined_functions

So you can't do what I was trying at all with H2, seems the solution is to create an Alias for a Java function.

Thanks you guys

Liquibase Stored Procedure throws SQL syntax error

1 Answers