OracleParser.java
/*-
* ========================LICENSE_START=================================
* flyway-database-oracle
* ========================================================================
* Copyright (C) 2010 - 2026 Red Gate Software Ltd
* ========================================================================
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* =========================LICENSE_END==================================
*/
package org.flywaydb.database.oracle;
import static org.flywaydb.core.internal.util.FlywayDbWebsiteLinks.ORACLE_DATABASE;
import org.flywaydb.core.api.configuration.Configuration;
import org.flywaydb.core.extensibility.LicenseGuard;
import org.flywaydb.core.extensibility.Tier;
import org.flywaydb.core.internal.parser.Parser;
import org.flywaydb.core.internal.parser.ParserContext;
import org.flywaydb.core.internal.parser.ParsingContext;
import org.flywaydb.core.internal.parser.PeekingReader;
import org.flywaydb.core.internal.parser.Recorder;
import org.flywaydb.core.internal.parser.StatementType;
import org.flywaydb.core.internal.parser.Token;
import org.flywaydb.core.internal.parser.TokenType;
import org.flywaydb.core.internal.util.FlywayDbWebsiteLinks;
import org.flywaydb.core.internal.sqlscript.Delimiter;
import org.flywaydb.core.internal.sqlscript.ParsedSqlStatement;
import org.flywaydb.core.internal.util.StringUtils;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Pattern;
public class OracleParser extends Parser {
/**
* Delimiter of PL/SQL blocks and statements.
*/
private static final Delimiter PLSQL_DELIMITER = new Delimiter("/", true, null);
// accessible by ( keyword<space>optionalidentifier )
private static final String ACCESSIBLE_BY_REGEX = "ACCESSIBLE\\sBY\\s\\(?(((FUNCTION|PROCEDURE|PACKAGE|TRIGGER|TYPE)\\s)?[^\\s]\\s?+)*\\)?";
private static final Pattern PLSQL_TYPE_BODY_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\sTYPE\\sBODY\\s([^\\s]*\\s)?(IS|AS)");
private static final Pattern PLSQL_PACKAGE_BODY_REGEX = Pattern.compile(
"^CREATE(\\s*OR\\s*REPLACE)?(\\s*(NON)?EDITIONABLE)?\\s*PACKAGE\\s*BODY\\s*([^\\s]*\\s)?(IS|AS)");
private static final StatementType PLSQL_PACKAGE_BODY_STATEMENT = new StatementType();
private static final Pattern PLSQL_PACKAGE_DEFINITION_REGEX = Pattern.compile(
"^CREATE(\\s*OR\\s*REPLACE)?(\\s*(NON)?EDITIONABLE)?\\s*PACKAGE\\s([^\\s*]*\\s*)?(AUTHID\\s*[^\\s*]*\\s*|"
+ ACCESSIBLE_BY_REGEX
+ ")*(IS|AS)");
private static final Pattern PLSQL_VIEW_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?((\\sNO)?\\sFORCE)?(\\s(NON)?EDITIONABLE)?\\sVIEW\\s([^\\s]*\\s)?AS\\sWITH\\s(PROCEDURE|FUNCTION)");
private static final StatementType PLSQL_VIEW_STATEMENT = new StatementType();
private static final Pattern PLSQL_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\s(FUNCTION(\\s\\S*)|PROCEDURE|TYPE|TRIGGER)");
private static final Pattern DECLARE_BEGIN_REGEX = Pattern.compile("^DECLARE|BEGIN|WITH");
private static final StatementType PLSQL_STATEMENT = new StatementType();
private static final Pattern JAVA_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\sAND\\s(RESOLVE|COMPILE))?(\\sNOFORCE)?\\sJAVA\\s(SOURCE|RESOURCE|CLASS)");
private static final StatementType PLSQL_JAVA_STATEMENT = new StatementType();
private static final Pattern PLSQL_PACKAGE_BODY_WRAPPED_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\sPACKAGE\\sBODY(\\s\\S*)?\\sWRAPPED(\\s\\S*)*");
private static final Pattern PLSQL_PACKAGE_DEFINITION_WRAPPED_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\sPACKAGE(\\s\\S*)?\\sWRAPPED(\\s\\S*)*");
private static final Pattern PLSQL_WRAPPED_REGEX = Pattern.compile(
"^CREATE(\\sOR\\sREPLACE)?(\\s(NON)?EDITIONABLE)?\\s(FUNCTION|PROCEDURE|TYPE)(\\s\\S*)?\\sWRAPPED(\\s\\S*)*");
private static final Pattern CREATE_IF_NOT_EXISTS = Pattern.compile(".*CREATE\\s(\\S+\\s){0,2}IF\\sNOT\\sEXISTS");
private static final Pattern DROP_IF_EXISTS = Pattern.compile(".*DROP\\s(\\S+\\s){0,2}IF\\sEXISTS");
private static final StatementType PLSQL_WRAPPED_STATEMENT = new StatementType();
private int initialWrappedBlockDepth = -1;
protected static Pattern toRegex(String... commands) {
return Pattern.compile(toRegexPattern(commands));
}
private static String toRegexPattern(String... commands) {
return "^(" + StringUtils.arrayToDelimitedString("|", commands) + ")";
}
public OracleParser(final Configuration configuration, final ParsingContext parsingContext) {
super(configuration, parsingContext, 3);
}
@Override
protected ParsedSqlStatement createStatement(final PeekingReader reader,
final Recorder recorder,
final int statementPos,
final int statementLine,
final int statementCol,
final int nonCommentPartPos,
final int nonCommentPartLine,
final int nonCommentPartCol,
final StatementType statementType,
final boolean canExecuteInTransaction,
final Delimiter delimiter,
String sql,
final List<Token> tokens,
final boolean batchable) throws IOException {
if (PLSQL_VIEW_STATEMENT == statementType) {
sql = sql.trim();
// Strip extra semicolon to avoid issues with WITH statements containing PL/SQL
if (sql.endsWith(";")) {
sql = sql.substring(0, sql.length() - 1);
}
}
return super.createStatement(reader,
recorder,
statementPos,
statementLine,
statementCol,
nonCommentPartPos,
nonCommentPartLine,
nonCommentPartCol,
statementType,
canExecuteInTransaction,
delimiter,
sql,
tokens,
batchable);
}
@Override
protected StatementType detectStatementType(final String simplifiedStatement,
final ParserContext context,
final PeekingReader reader) {
if (PLSQL_PACKAGE_BODY_WRAPPED_REGEX.matcher(simplifiedStatement).matches()
|| PLSQL_PACKAGE_DEFINITION_WRAPPED_REGEX.matcher(simplifiedStatement).matches()
|| PLSQL_WRAPPED_REGEX.matcher(simplifiedStatement).matches()) {
if (initialWrappedBlockDepth == -1) {
initialWrappedBlockDepth = context.getBlockDepth();
}
return PLSQL_WRAPPED_STATEMENT;
}
if (PLSQL_PACKAGE_BODY_REGEX.matcher(simplifiedStatement).matches()) {
return PLSQL_PACKAGE_BODY_STATEMENT;
}
if (PLSQL_REGEX.matcher(simplifiedStatement).matches() || PLSQL_PACKAGE_DEFINITION_REGEX.matcher(
simplifiedStatement).matches() || DECLARE_BEGIN_REGEX.matcher(simplifiedStatement).matches()) {
try {
final String wrappedKeyword = " WRAPPED";
if (!reader.peek(wrappedKeyword.length()).equalsIgnoreCase(wrappedKeyword)) {
return PLSQL_STATEMENT;
}
} catch (IOException e) {
return PLSQL_STATEMENT;
}
}
if (JAVA_REGEX.matcher(simplifiedStatement).matches()) {
return PLSQL_JAVA_STATEMENT;
}
if (PLSQL_VIEW_REGEX.matcher(simplifiedStatement).matches()) {
return PLSQL_VIEW_STATEMENT;
}
return super.detectStatementType(simplifiedStatement, context, reader);
}
@Override
protected boolean shouldDiscard(final Token token, final boolean nonCommentPartSeen) {
// Discard dangling PL/SQL '/' delimiters
return ("/".equals(token.getText()) && !nonCommentPartSeen) || super.shouldDiscard(token, nonCommentPartSeen);
}
@Override
protected void adjustDelimiter(final ParserContext context, final StatementType statementType) {
if (statementType == PLSQL_STATEMENT
|| statementType == PLSQL_VIEW_STATEMENT
|| statementType == PLSQL_JAVA_STATEMENT
|| statementType == PLSQL_PACKAGE_BODY_STATEMENT) {
context.setDelimiter(PLSQL_DELIMITER);
} else {
context.setDelimiter(Delimiter.SEMICOLON);
}
}
@Override
protected boolean shouldAdjustBlockDepth(final ParserContext context, final List<Token> tokens, final Token token) {
// Package bodies can have an unbalanced BEGIN without END in the initialisation section.
final TokenType tokenType = token.getType();
if (context.getStatementType() == PLSQL_PACKAGE_BODY_STATEMENT && (TokenType.EOF == tokenType
|| TokenType.DELIMITER == tokenType)) {
return true;
}
// Handle wrapped SQL on these token types to ensure it gets treated as one block
if (context.getStatementType() == PLSQL_WRAPPED_STATEMENT && (TokenType.EOF == tokenType
|| TokenType.DELIMITER == tokenType)) {
return true;
}
// In Oracle, symbols { } affect the block depth in embedded Java code
if (token.getType() == TokenType.SYMBOL && context.getStatementType() == PLSQL_JAVA_STATEMENT) {
return true;
}
final Token previousToken = getPreviousToken(tokens, token.getParensDepth());
if (previousToken != null && "CASE".equals(token.getText()) && "FROM".equals(previousToken.getText())) {
return false;
}
return super.shouldAdjustBlockDepth(context, tokens, token);
}
// These words increase the block depth - unless preceded by END (in which case the END will decrease the block depth)
private static final List<String> CONTROL_FLOW_KEYWORDS = Arrays.asList("IF", "LOOP", "CASE");
@Override
protected void adjustBlockDepth(final ParserContext context,
final List<Token> tokens,
final Token keyword,
final PeekingReader reader) {
final TokenType tokenType = keyword.getType();
final String keywordText = keyword.getText();
final int parensDepth = keyword.getParensDepth();
if (lastTokenIs(tokens, parensDepth, "GOTO")) {
return;
}
if (context.getStatementType() == PLSQL_WRAPPED_STATEMENT) {
// ensure wrapped SQL has an increased block depth so it gets treated as one statement
if (context.getBlockDepth() == initialWrappedBlockDepth) {
context.increaseBlockDepth("WRAPPED");
}
// decrease block depth at the end to step out of a wrapped SQL block
if ((TokenType.EOF == tokenType || (TokenType.DELIMITER == tokenType && "/".equals(keywordText)))
&& context.getBlockDepth() > 0) {
context.decreaseBlockDepth();
}
// return early as we don't need to parse the contents of wrapped SQL - it's all one statement anyways
return;
} else {
// decrease block depth when wrapped SQL ends to step out of wrapped SQL block
if (context.getBlockDepth() > initialWrappedBlockDepth && context.getBlockInitiator().equals("WRAPPED")) {
initialWrappedBlockDepth = -1;
context.decreaseBlockDepth();
}
}
// In embedded Java code we judge the end of a class definition by the depth of braces.
// We ignore normal SQL keywords as Java code can contain arbitrary identifiers.
if (context.getStatementType() == PLSQL_JAVA_STATEMENT) {
if ("{".equals(keywordText)) {
context.increaseBlockDepth("PLSQL_JAVA_STATEMENT");
} else if ("}".equals(keywordText)) {
context.decreaseBlockDepth();
}
return;
}
if ("BEGIN".equals(keywordText) || (CONTROL_FLOW_KEYWORDS.contains(keywordText)
&& !precedingEndAttachesToThisKeyword(tokens, parensDepth, context, keyword)) || ("TRIGGER".equals(
keywordText) && lastTokenIs(tokens, parensDepth, "COMPOUND")) || (context.getBlockDepth() == 0 && (
doTokensMatchPattern(tokens, keyword, PLSQL_PACKAGE_BODY_REGEX)
|| doTokensMatchPattern(tokens, keyword, PLSQL_PACKAGE_DEFINITION_REGEX)
|| doTokensMatchPattern(tokens, keyword, PLSQL_TYPE_BODY_REGEX)))) {
context.increaseBlockDepth(keywordText);
} else if ("END".equals(keywordText)
|| doTokensMatchPattern(tokens, keyword, CREATE_IF_NOT_EXISTS)
|| doTokensMatchPattern(tokens, keyword, DROP_IF_EXISTS)) {
context.decreaseBlockDepth();
}
// Package bodies can have an unbalanced BEGIN without END in the initialisation section. This allows us
// to exit the package even though we are still at block depth 1 due to the BEGIN.
if (context.getStatementType() == PLSQL_PACKAGE_BODY_STATEMENT && (TokenType.EOF == tokenType
|| TokenType.DELIMITER == tokenType) && context.getBlockDepth() == 1) {
context.decreaseBlockDepth();
}
}
private boolean precedingEndAttachesToThisKeyword(final List<Token> tokens,
final int parensDepth,
final ParserContext context,
final Token keyword) {
// Normally IF, LOOP and CASE all pair up with END IF, END LOOP, END CASE
// However, CASE ... END is valid in expressions, so in code such as
// FOR i IN 1 .. CASE WHEN foo THEN 5 ELSE 6 END
// LOOP
// ...
// END LOOP
// the first END does *not* attach to the subsequent LOOP. The same is possible with $IF ... $END constructions
return lastTokenIs(tokens, parensDepth, "END")
&& lastTokenIsOnLine(tokens, parensDepth, keyword.getLine())
&& keyword.getText().equals(context.getLastClosedBlockInitiator());
}
@Override
protected boolean doTokensMatchPattern(final List<Token> previousTokens, final Token current, final Pattern regex) {
if (regex == PLSQL_PACKAGE_DEFINITION_REGEX && previousTokens.stream()
.anyMatch(t -> t.getType() == TokenType.KEYWORD && t.getText().equalsIgnoreCase("ACCESSIBLE"))) {
final ArrayList<String> tokenStrings = new ArrayList<>();
tokenStrings.add(current.getText());
for (int i = previousTokens.size() - 1; i >= 0; i--) {
final Token prevToken = previousTokens.get(i);
if (prevToken.getType() == TokenType.KEYWORD) {
tokenStrings.add(prevToken.getText());
}
}
final StringBuilder builder = new StringBuilder();
for (int i = tokenStrings.size() - 1; i >= 0; i--) {
builder.append(tokenStrings.get(i));
if (i != 0) {
builder.append(" ");
}
}
return regex.matcher(builder.toString()).matches() || super.doTokensMatchPattern(previousTokens,
current,
regex);
}
return super.doTokensMatchPattern(previousTokens, current, regex);
}
@Override
protected boolean isDelimiter(final String peek,
final ParserContext context,
final int col,
final int colIgnoringWhitespace) {
final Delimiter delimiter = context.getDelimiter();
if (peek.startsWith(delimiter.getEscape() + delimiter.getDelimiter())) {
return true;
}
if (delimiter.shouldBeAloneOnLine()) {
// Only consider alone-on-line delimiters (such as "/" for PL/SQL) if
// it's the first character on the line
return colIgnoringWhitespace == 1 && peek.trim().equals(delimiter.getDelimiter());
} else {
if (colIgnoringWhitespace == 1 && "/".equals(peek.trim())) {
return true;
}
}
return super.isDelimiter(peek, context, col, colIgnoringWhitespace);
}
@Override
protected Token handleMultilineComment(final PeekingReader reader,
final ParserContext context,
final int pos,
final int line,
final int col) throws IOException {
reader.swallow("/*".length());
final String text = reader.readUntilExcluding("*/");
reader.swallow("*/".length());
return new Token(TokenType.COMMENT, pos, line, col, text, text, context.getParensDepth());
}
@Override
protected Token handleDelimiter(final PeekingReader reader,
final ParserContext context,
final int pos,
final int line,
final int col) throws IOException {
if (reader.peek('/')) {
reader.swallow(1);
return new Token(TokenType.DELIMITER, pos, line, col, "/", "/", context.getParensDepth());
}
return super.handleDelimiter(reader, context, pos, line, col);
}
@Override
protected boolean isAlternativeStringLiteral(final String peek) {
if (peek.length() < 3) {
return false;
}
// Oracle's quoted-literal syntax is introduced by q (case-insensitive) followed by a literal surrounded by
// any of !!, [], {}, (), <> provided the selected pair do not appear in the literal string; the others may do.
final char firstChar = peek.charAt(0);
return (firstChar == 'q' || firstChar == 'Q') && peek.charAt(1) == '\'';
}
@Override
protected Token handleAlternativeStringLiteral(final PeekingReader reader,
final ParserContext context,
final int pos,
final int line,
final int col) throws IOException {
reader.swallow(2);
final String closeQuote = computeAlternativeCloseQuote((char) reader.read());
reader.swallowUntilExcluding(closeQuote);
reader.swallow(closeQuote.length());
return new Token(TokenType.STRING, pos, line, col, null, null, context.getParensDepth());
}
private String computeAlternativeCloseQuote(final char specialChar) {
return switch (specialChar) {
case '!' -> "!'";
case '[' -> "]'";
case '(' -> ")'";
case '{' -> "}'";
case '<' -> ">'";
default -> specialChar + "'";
};
}
@Override
protected String getAdditionalParsingErrorInfo() {
String parsingError = "For Oracle-specific information about syntax and limitations, see "
+ ORACLE_DATABASE
+ ".";
if (LicenseGuard.isLicensed(configuration, Tier.PREMIUM)) {
parsingError += "\nFlyway Native Connectors may be able to help with parsing Oracle. "
+ "Set FLYWAY_NATIVE_CONNECTORS=true to enable, and see this blog post "
+ FlywayDbWebsiteLinks.ORACLE_BLOG
+ ". ";
}
return parsingError;
}
}