Want to deploy your JVM, Node.js and Go apps effortlessly to AWS? Try our service Boxfuse

Oracle

Supported Versions

All editions are supported, including XE.

Driver

URL format jdbc:oracle:thin:@//host:port/service
jdbc:oracle:thin:@tns_entry *
Ships with Flyway Command-line No
Download Download the latest version of both ojdbc8.jar and orai18n.jar from oracle.com and place them in Flyway's drivers directory
Supported versions 11.2 and later
Default Java class oracle.jdbc.OracleDriver

* TNS_ADMIN environment variable must point to the directory of where tnsnames.ora resides

SQL Script Syntax

  • Standard SQL syntax with statement delimiter ;
  • PL/SQL blocks starting with DECLARE or BEGIN and finishing with END; /

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

Flyway Pro

In addition to the regular Oracle SQL syntax, Flyway Pro and Flyway Enterprise also come with support for Oracle SQL*Plus commands.

This support is disabled by default and must be activated using the oracle.sqlplus flag.

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 ESCAPE
  • SET FLAGGER
  • SET HEADING
  • SET LINESIZE (DBMS_OUTPUT only)
  • SET NULL
  • SET SCAN
  • SET SERVEROUTPUT
  • SET SUFFIX
  • SHOW CON_ID
  • SHOW EDITION
  • SHOW ERRORS
  • SHOW LINESIZE
  • SHOW RELEASE
  • SHOW SERVEROUTPUT
  • SHOW SUFFIX
  • SHOW USER
  • START (only files, no URLs)
  • UNDEFINE
  • WHENEVER SQLERROR CONTINUE
  • WHENEVER SQLERROR EXIT FAILURE

The short form of these commands is also supported.

Output

When SET SERVEROUTPUT ON is invoked output produced by DBMS_OUTPUT.PUT_LINE will be shown in the console.

Placeholders

By default SQL*Plus placeholder support is enabled. &VAR-style placeholders will automatically be replaced with the matching value supplied by either Flyway’s regular placeholder configuration or a DEFINE command. Use of these placeholders can be disabled in the usual way using the SET DEFINE OFF command.

Unsupported commands

All other SQL*Plus commands are gracefully ignored with a warning message.

Limitations

  • SPATIAL EXTENSIONS: sdo_geom_metadata can only be cleaned for the user currently logged in

SQL Server