CODEWITHSUNDEEP
javaoracle-databaseflyway
Juri Pro
Juri Pro

Reputation: 11

Flyway - oracle PL/SQL procedures migration

What would be the preferable way to update schema_version table and execute modified PL/SQL packages/procedures in flyway without code duplication?

My example would require a class file be created for each PL/SQL code modicaition

public class V2_1__update_scripts extends AbstractMigration {
    // update package and procedures
}

AbstractMigration class executes the files in db/update folder:

public abstract class AbstractMigration implements SpringJdbcMigration {
    private static final Logger log = LoggerFactory.getLogger(AbstractMigration.class);

    @Override
    public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
        Resource packageFolder = new ClassPathResource("db/update");
        Collection<File> files = FileUtils.listFiles(packageFolder.getFile(), new String[]{"sql"}, true);
        for (File file : files ) {
            log.info("Executing [{}]", file.getAbsolutePath());
            String fileContents = FileUtils.readFileToString(file);
            jdbcTemplate.execute(fileContents);
        }
    }
}

Is there any better way of executing PL/SQL code?

Upvotes: 1

Views: 3198

Answers (3)

Funky Monkey
Funky Monkey

Reputation: 1

The version of Flyway current at the time of this writing (v4.2.0) supports the notion of repeatable scripts designed specifically for such situations. Basically any script with a "Create or replace" semantic is a candidate.

Simply name your script as R__mypackage_body.sql or whatever prefix you wish for repeatable scripts. Please see Sql-based migrations and Repeatable migrations for further information.

Upvotes: 0

jhodges
jhodges

Reputation: 3135

I wonder if it's better to duplicate the code into the standard migrations folder. It seems like with the given example you wouldn't then be able to migrate up to version N of the db, as some prior version would execute all the current version of the pl/sql. I'd be interested to see if you settled on a solution for this.

Upvotes: 1

Axel Fontaine
Axel Fontaine

Reputation: 35169

There is no built-in support or other command you have missed.

Of the top of my head, I would think about either the way you presented here or using a generator to produce new migration sql files after an SCM commit.

Let's see if someone else found a better solution.

Upvotes: 0

Related Questions