What are we doing

The Oracle dialect of SQL (including SQL*plus) has resulted in one of the largest and most complex parts of the Flyway engine, the Oracle parser.

We often talk with customers who have large and complex scripts for their database that, whilst they can be deployed with Oracle's tooling, causes Flyway to fail on parsing the SQL.

Building on our Native Connecters architecture, we are integrating the ability to use Oracle's SQLcl tool directly within Flyway when you enable the Oracle SQL*plus extension.

This means that if you enable Flyway's oracle.sqlplus flag, Flyway will then use SQLcl to deploy your migrations rather than attempting to run them through the regular JDBC parser & driver.

Availability

This is available from Flyway CLI v11.7.0 as a preview feature

How to use this

To enable it:

• You need a version of Flyway CLI from v11.7.0 onwards
• You need to enable Flyway's oracle.sqlplus option for your project (This is a Flyway Teams feature).
• You need to have SQLcl available on your system.
  • Flyway will call sql to deploy your migrations when using Native Connectors for Oracle.
• You need to define the environment variable "FLYWAY_NATIVE_CONNECTORS=true".

When you run a Flyway migrate operation you will see something like this:

./flyway migrate -oracle.sqlplus="true"

Flyway Enterprise Edition 11.7.2 by Redgate

See release notes here: https://rd.gt/416ObMi

Flyway Pipelines are not active for this project. Learn more here: https://flyway.red-gate.com
-----------------------------------------------------------------------------
You are using a preview feature 'Native Connectors'.
Please report any issues you encounter to DatabaseDevOps@red-gate.com
-----------------------------------------------------------------------------
Database: jdbc:oracle:thin:@localhost:62064 (Oracle 21.0)
Schema history table "FLYWAY"."flyway_schema_history" does not exist yet
Successfully validated 1 migration (execution time 00:00.065s)
Creating Schema History table "FLYWAY"."flyway_schema_history" ...
Current version of schema "FLYWAY": << Empty Schema >>
Migrating schema "FLYWAY" to version "1 - first migration" [non-transactional]
Executing SQLcl
Session altered.

Table FIRST created.
Successfully applied 1 migration to schema "FLYWAY", now at version v1 (execution time 00:03.615s)

Feedback

Please let us know your thoughts on this feature: Feedback form (takes 1-2 minutes)

Constraints

SQL*plus

• The SQL*plus @@ operator won't work as you expect - in order to allow Flyway's placeholder replacement to work your migrations have to be copied to a temporary location and this was a compromise we've had to make.

Callbacks

• Not all Flyway callbacks are supported in Native Connectors. See here for a list of supported callbacks.
• Native Connectors also supports an afterConnect callback which will replace the initSql parameter.

Script Migrations

Script migrations are not currently supported. These are used to invoke a script rather than directly deploy changes to the database. Please let us know if this a significant constraint for you.

Locking the Schema History Table

This is required if you have multiple Flyway instances attempting to change your database simultaneously and is not currently supported.

This is typically an issue when using the Flyway API in a java application but Native Connectors is a Flyway CLI feature.

Flyway Deploy

Deploy is not currently supported in Native Connectors so it will not be able to use Oracle's SQLcl for your deployments. It will default back to using the JDBC driver

Under the hood

• Flyway does still use a JDBC connection to your database - this will be used for Flyway to do housekeeping (the schema history table) as well as any Oracle operations where the oracle.sqlplus flag is not enabled.
• SQLcl uses a JDBC connection string so how you connect to the database is consistent between JDBC and SQlcl