Oracle Database
Published 14 N 2023
Oracle
- Verified Versions: 11.1, 21
- Maintainer: Redgate
All editions are supported, including XE.
Supported Versions and Support Levels
- For information regarding the supported version and support levels available, please see Supported Databases for Flyway
- For information regarding the Flyway features available, please see Flyway feature summary
Driver
Item | Details |
---|---|
URL format | jdbc:oracle:thin:@//host:port/service jdbc:oracle:thin:@tns_entry * |
Ships with Flyway Command-line | Yes |
Maven Central coordinates | com.oracle.database.jdbc:ojdbc8 |
Supported versions | 18.3.0.0 and later |
Default Java class | oracle.jdbc.OracleDriver |
* TNS_ADMIN
environment variable must point to the directory of where tnsnames.ora
resides
Java Usage
Oracle support is a separate dependency for Flyway and will need to be added to your Java project to access these features.
Maven
Redgate
<dependency>
<groupId>com.redgate.flyway</groupId>
<artifactId>flyway-database-oracle</artifactId>
</dependency>
Open Source
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-database-oracle</artifactId>
</dependency>
Gradle
Redgate
dependencies {
implementation "com.redgate.flyway:flyway-database-oracle"
}
Open Source
dependencies {
implementation "org.flywaydb:flyway-database-oracle"
}
SQL Script Syntax
- Standard SQL statements ending with the semicolon (
;
) delimiter are handled as normal. - PL/SQL blocks and SQL*Plus statements must be delimited by
/
on a new line. PL/SQL includes:- Types
- Packages
- Functions
- Procedures
- Views with functions/procedures
- Triggers
- Java sources
Compatibility
- DDL exported by Oracle can be used unchanged in a Flyway migration
- Any Oracle SQL script executed by Flyway can be executed by SQL*Plus and other Oracle-compatible tools (after the placeholders have been replaced)
Example
/* Single line comment */ CREATE TABLE test_user ( name VARCHAR(25) NOT NULL, PRIMARY KEY(name) ); /* Multi-line comment */ -- PL/SQL block CREATE TRIGGER test_trig AFTER insert ON test_user BEGIN UPDATE test_user SET name = CONCAT(name, ' triggered'); END; / -- Placeholder INSERT INTO ${tableName} (name) VALUES ('Mr. T');
SQL*Plus commands
In addition to the regular Oracle SQL syntax, Flyway Teams also comes with support for many Oracle SQL*Plus commands.
This support is disabled by default and must be activated using the oracle.sqlplus
flag.
The SQL*Plus capability within Flyway is a re-implementation so may not behave exactly as native SQL*Plus does. If a feature you're looking for doesn't work as expected then we recommend using script migrations to invoke the SQL*Plus command-line tool.
Supported commands
The following commands are fully supported and can be used just like any regular command within your SQL migrations:
@
(only files, no URLs)@@
(only files, no URLs)DEFINE
EXECUTE
PROMPT
REMARK
SET DEFINE
SET ECHO
SET ESCAPE
SET FEEDBACK
SET FLAGGER
SET HEADING
SET LINESIZE
(DBMS_OUTPUT only)SET NULL
SET SCAN
SET SERVEROUTPUT
SET SUFFIX
SET TERMOUT
SET TIME
SET TIMING
SET VERIFY
SHOW CON_ID
SHOW DEFINE
SHOW ECHO
SHOW EDITION
SHOW ERRORS
SHOW ESCAPE
SHOW FEEDBACK
SHOW HEADING
SHOW LINESIZE
SHOW NULL
SHOW RELEASE
SHOW SCAN
SHOW SERVEROUTPUT
SHOW SUFFIX
SHOW TERMOUT
SHOW TIME
SHOW TIMING
SHOW USER
SHOW VERIFY
SPOOL
START
(only files, no URLs)UNDEFINE
WHENEVER SQLERROR CONTINUE
WHENEVER SQLERROR EXIT
WHENEVER SQLERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
The short form of these commands is also supported.
Site Profiles (glogin.sql
) & User Profiles (login.sql
)
This feature allows you to set up your SQL*Plus environment to use the same settings with each session. It allows you to execute statements before every script run, and is typically used to configure
the session in a consistent manner by calling SQL*Plus commands such as SET FEEDBACK
and SET DEFINE
.
Flyway will look for login.sql
in all the valid migration locations, and load it if present. glogin.sql
will be loaded from $ORACLE_HOME/sqlplus/admin/glogin.sql
in UNIX, and ORACLE_HOME\sqlplus\admin\glogin.sql
otherwise.
Profiles are only loaded when oracle.sqlplus
is enabled.
Output
When SET SERVEROUTPUT ON
is invoked, output produced by DBMS_OUTPUT.PUT_LINE
will be shown in the console.
Variable substitution
By default SQL*Plus variable substitution support is enabled. &VAR
-style variables will automatically be replaced
with the matching value supplied by either Flyway's regular placeholder configuration or a DEFINE
command.
Use of these variables can be disabled in the usual way using the SET DEFINE OFF
command.
While SQL*Plus is interactive and will prompt for missing variable values, Flyway does not; it is an error not to provide a value for all variables that need to be substituted.
Statements which contain a &VAR
-style expression which is not intended to be substituted, such as in a
literal string, will either require SET DEFINE OFF
beforehand, or some alternative construct to avoid use of
the ampersand.
For more information, see the SQL*Plus documentation.
Authentication
JDBC
Oracle supports user and password being provided in the JDBC URL, in the form
jdbc:oracle:thin:<user>/<password>@//<host>:<port>/<database>
In this case, they do not need to be passed separately in configuration and the Flyway commandline will not prompt for them.
Oracle Wallet
Flyway can connect to your databases using credentials in your Oracle Wallet.
First you need to ensure you have set the environment variable TNS_ADMIN
to point to the location containing your tnsnames.ora
file. Then you will need to configure the flyway.oracle.walletLocation
parameter to point to the location of your Oracle wallet. Lastly your URL should be provided as specified in tnsnames.ora
i.e. if it is using an alias then connect with the jdbc:oracle:thin:@db_alias
syntax.
With that configuration you will be able to connect to your database without providing any credentials in config.
Kerberos
You can authenticate using Kerberos by specifying the location of the local Kerberos configuration file (which contains
details such as the locations of Kerberos Key Distribution Centers), and optionally the local credential cache, to
Flyway. For example, in flyway.conf
:
flyway.oracle.kerberosConfigFile=/etc/krb5.conf
flyway.oracle.kerberosCacheFile=/tmp/krb5cc_123
Proxy Authentication
Flyway allows you to proxy through other users during migrations. You can read about how to enable proxying for users here.
To configure Flyway to use a proxy connection, you need to add to jdbcProperties a key PROXY_USER_NAME
whose value is the name of the user you are trying to proxy as. For example, if you connect as user A
to Flyway (i.e. flyway.user=A
) and you want to proxy as user B
for migrations, you need to add flyway.jdbcproperties.PROXY_USER_NAME=B
.
Limitations
- SPATIAL EXTENSIONS:
sdo_geom_metadata
can only be cleaned for the user currently logged in
SQL*Plus
Unsupported commands
Not all SQL*Plus commands are supported by Flyway. Unsupported commands are gracefully ignored with a warning message.
Behavior parity
As much as possible, Flyway aims to emulate the behavior of the SQL*Plus client in Oracle SQL Developer. However, there are some edge cases where Flyway isn't able to emulate the behavior exactly. Known cases are detailed below:
-
Abbreviations: Flyway is limited by JDBC support for particular commands, and this is more strict than the SQL*Plus client; in general abbreviations are supported by Flyway as documented here, so for example
SHOW ERRORS
can be abbreviated toSHO ERR
, but notSHOW ERROR
(which is accepted by the client). -
SQL*Plus is known to replace CRLF pairs in string literals with single LFs. Flyway will not do this - instead it preserves scripts as they are written
If you encounter a discrepancy between the Oracle SQL*Plus client and Flyway, let us know via the official support email.
Referenced scripts and checksums
Flyway includes any referenced scripts when calculating checksums. This also extends to login.sql
and glogin.sql
since their contents can affect the reproducibility of a migration and can differ in different environments.
Known issues and workarounds
Implementing a compatible solution to some problems isn't always possible, so we document those problems and the valid workarounds.
A default schema different to the current user's causes remote links to fail
Flyway alters the current schema to the specified default schema as this is where the schema history table should reside. This causes remote links to fail in migrations that expect the current schema to be the user's. The workarounds for this are:
- Create the remote link via dynamic SQL in a stored procedure that resides in the correct schema. Stored procedures execute as the schema owner, so the remote link is created in the correct schema
- Use beforeEachMigrate and afterEachMigrate callbacks to alter the current schema as needed