Could I use variable constant in my script sql and test it with flyway test ?

[Yiao]

version and edition of Flyway: 5.2.4 version and edition of Flyway test: 5.2.4 Database (type & version): Oracle DB 12.1.0.2.0 PL/SQL 12.1.0.2

I want to use variable constant plsql to replace some texts in flyway but I got syntaxe error

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

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

[FlorianGWE]

First of all flyway-test is only a wrapper of Flyway and call Flyway function in a specific test phase. If it works in Flyway it should also work in flyway-test,

I have only expirience with placeholder usage as descibed at https://flywaydb.org/documentation/database/oracle. First part of the description plain SQL part.

I have never tried PLSQL specific part. I think than you need Flyway Pro or Flyway Enterprise, but I have never used it.

[FlorianGWE]

What should your test feature do in the database? Insert a "test name" in a specific table?

[Yiao]

First of all, thank you very much for your response. I insert some default values in my table, I just try to test if I can get them with a findAll()

[FlorianGWE]

For this use case exist more than one solution and you need not really PLSQL:

1. provide a specific testdata resource with your SQL scripts to insert data see a spring-boot example https://jonas-havers.de/articles/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 examples.
2. you can combine flyway-test together with DbUnit for this case
3. If you using Spring and Spring-Test you also can use SqlScriptsTestExecutionListener - it works also together with @ FlywayTest see example FlywayTestWithSqlScriptsTestExecutionListenerTest

[FlorianGWE]

my recommendation for question will be https://stackoverflow.com/

[Yiao]

Yeh, I will ask it in StackOverflow, thanks a lot ;)