Flyway callbacks with Oracle compile

Question

I try to add before migration and after migration scripts as callbacks to flyway for compiling my views, procedures, functions etc. Is there a possibility to stop it before a migration process or have a rollback when before or after scripts fail (or rather return a warning)?

Cause the only thing I see right now is I receive warnings like this

[WARNING] DB: Warning: execution completed with warning (SQL State: 99999 - Error Code: 17110)

and it goes on, without stopping. I thought about FlywayCallback interface and it's implementation but I'm not entirely sure how it should be done with compiling.

I'm using Spring Boot 1.2.5 with the newest Flyway.

Answer 1

I have also same error. SQL State: 99999 - Error Code: 17110. i found this solution. check which version under this warning and that version under sql script check have Trigger or any procedure which not closed properly.

close trigger or any procedure if oracle DB / end of trigger.

ex:

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;
/

Answer 2

Flyway 5.0 now comes with a feature called Error Handlers that lets you do exactly this. You can now create an error handler that turns that warning into an error as simply as

import org.flywaydb.core.api.FlywayException;
import org.flywaydb.core.api.errorhandler.Context;
import org.flywaydb.core.api.errorhandler.ErrorHandler;
import org.flywaydb.core.api.errorhandler.Warning;

public class OracleProcedureFailFastErrorHandler implements ErrorHandler {
    @Override
    public boolean handle(Context context) {
        for (Warning warning : context.getWarnings()) {
            if ("99999".equals(warning.getState()) && warning.getCode() == 17110) {
                throw new FlywayException("Compilation failed");
            }
        }
        return false;
    }
}

More info in the docs: https://flywaydb.org/documentation/errorhandlers