Could I use plsql variable constant or variable with Flyway

Question

I want to use some constant in my init.sql file flyway to replace some duplicated texts.

But flyway throws me with migration failed:

SQL State  : 42000
Error Code : 42000
Message    : Erreur de syntaxe dans linstruction SQL {0}
Syntax error in SQL statement {0}; SQL statement:


    DECLARE
       myname_test CONSTANT VARCHAR2(10) := 'SOME TEXT';
    BEGIN
       INSERT INTO MYTABLE(ID,NAME) VALUES (1,myname_test);
    END;

By the way, I use Oracle Database.

So I wonder if flyway can read and execute PLSQL.

Answer 1

For filling the database with test data exist more than one solution and you need not really PLSQL.

Here a set of possible ways to do the job, I think it will exists some more:

• provide a specific testdata resource with your SQL scripts to insert data see a spring-boot example how-to-create-database-test-data-for-spring-boot-applications-with-flyway-db.
• It can also be done by flyway-test with additional locations. Take a look in the flyway-test examples.
• you can combine flyway-test together with DbUnit for this case
• If you using Spring and Spring-Test you also can use SqlScriptsTestExecutionListener - it works also together with @FlywayTest see example FlywayTestWithSqlScriptsTestExecutionListenerTest