Oracle

Supported Versions

• 12.2
• 12.1
• 11.2 Flyway Enterprise
• 11.1 Flyway Enterprise
• 10.2 Flyway Enterprise
• 10.1 Flyway Enterprise

All editions are supported, including XE.

Driver

* 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.

The following commands are fully supported and can be used just like any regular command within your SQL migrations:

• EXECUTE
• PROMPT
• REMARK
• SET NULL
• SET SERVEROUTPUT OFF
• SET SERVEROUTPUT ON
• SHOW CON_ID
• SHOW EDITION
• SHOW ERRORS
• SHOW RELEASE
• SHOW USER
• WHENEVER SQLERROR CONTINUE
• WHENEVER SQLERROR EXIT FAILURE

The short form of these commands is also supported.

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

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
• No support for executing external scripts referenced with @other.script, as supported by SQL*Plus

SQL Server