19.3
18.3
12.2
12.1
Flyway Teams 11.2
Contact us 11.1
Contact us All editions are supported, including XE.
Compatible | ✅ |
---|---|
Certified | ✅ |
Guaranteed | ✅ Flyway Teams |
Support Level determines the degree of support available for this database (learn more).
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:19.6.0.0 |
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
;
DECLARE
or BEGIN
and finishing with END; /
/* 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');
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.
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 FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
The short form of these commands is also supported.
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.
When SET SERVEROUTPUT ON
is invoked, output produced by DBMS_OUTPUT.PUT_LINE
will be shown in the console.
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.
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.
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.
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
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
.
sdo_geom_metadata
can only be cleaned for the user currently logged inNot all SQL*Plus commands are supported by Flyway. Unsupported commands are gracefully ignored with a warning message.
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 to SHO ERR
, but not SHOW 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.
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.
Implementing a compatible solution to some problems isn’t always possible, so we document those problems and the valid workarounds.
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: