Code to break up oracle sql scripts into statements that can be run via jdbc. Handles normal sql (;) endings and plsql endings (END;---comment\n/) Strips off multi-line and single-line comments, and comments embedded in sql lines. Handles different variations of package declarations, functions (which can be both plsql and sql ended), triggers, p…

gistfile1.txt

package fc.db.oracle;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class OracleSqlScript {
    private static final Pattern PLSQL_CREATE_OR_REPLACE_FUNCTION = Pattern.compile("\\b(CREATE[\\s\\n]+OR[\\s\\n]+REPLACE[\\s\\n]+FUNCTION)\\b", Pattern.CASE_INSENSITIVE);
    
    static final Pattern PLSQL_BEGIN = Pattern.compile("\\b((DECLARE)|(IS)|(BEGIN))\\b", Pattern.CASE_INSENSITIVE);
    static final Pattern IS_NULL = Pattern.compile("\\b(IS\\s+NULL|IS\\s+NOT)\\b", Pattern.CASE_INSENSITIVE);
    static final Pattern PLSQL_END = Pattern.compile(";[\\s\\n]+/\\s*(--.*$)?", Pattern.MULTILINE);
    static final Pattern SQL_END = Pattern.compile(";\\s*");

    /** ignoring blank lines, lines with only comments */
    static final Pattern IGNORED_LINES = Pattern.compile(
            "(?im)((\\s*/.*$)|(\\s*-.*$)|(^\\s*\\n)|(^\\s*/\\s*$)|(\\s*REM.*$))*", Pattern.MULTILINE);

    // ignoring multi-line comments (/* .. */)
    static final Pattern STRIP_MULTILINE_COMMENTS = Pattern.compile("/\\*.*?\\*/",
            Pattern.DOTALL);

    /**
     * ignoring single line and embedded comments
     * 
     * <pre>
     *  -- here is a comment
     * insert into tbl values ('hello world'); -- and another comment
     * insert into tbl values ("hello--world"); -- but here is a value and comment
     * </pre>
     */
    private static final Pattern STRIP_SINGLE_LINE_COMMENTS_AND_COMMENTS_ON_SAME_LINE_AS_SQL = Pattern.compile(
            "(?:\"[^\"]+?\"|'[^']+?')|(--.*$)");

    private final List<String> statements;

    public OracleSqlScript(final File script) throws IOException {
        this(readFileToString(script));
    }

    public OracleSqlScript(final String script) {
        String txt = stripComments(script);
        this.statements = createSqlStatementsFromScript(txt);
    }

    public List<String> getStatements() {
        return this.statements;
    }

    private String stripComments(final String script) {
        // remove \r line delimiters
        String txt = script.replace("\\r", "");

        // strip "/*..*/" comments
        txt = STRIP_MULTILINE_COMMENTS.matcher(txt).replaceAll("");

        // could be made a lot cleaner
        // if not trying to handle -- embedded in sql quotation marks
        StringBuilder buf = new StringBuilder(txt.length());
        try (Scanner scanner = new Scanner(txt)) {
            scanner.skip(IGNORED_LINES);
            String line = null;
            while (scanner.hasNext() && (line = scanner.nextLine()) != null) {
                boolean matched = false;
                Matcher matcher = STRIP_SINGLE_LINE_COMMENTS_AND_COMMENTS_ON_SAME_LINE_AS_SQL.matcher(line);
                while (matcher.find()) {
                    String group = matcher.group(1);
                    if (group != null) {
                        matched = true;
                        int start = matcher.start(1);
                        line = line.substring(0, start) + line.substring(matcher.end(1));
                    }
                }
                if (!matched || !line.isEmpty()) {
                    buf.append(line).append("\n");
                }
            }
        }
        return buf.toString();
    }

    private static List<String> createSqlStatementsFromScript(String txt) {
        try (Scanner scanner = new Scanner(txt)) {
            scanner.skip(IGNORED_LINES);
            List<String> statements = new ArrayList<String>();
            scanner.useDelimiter(SQL_END);
            while (scanner.hasNext()) {
                StringBuilder buffer = new StringBuilder();
                scanner.skip(IGNORED_LINES);
                boolean isPlSql = false;
                String line;
                while (scanner.hasNext() && (line = scanner.nextLine()) != null) {

                    buffer.append(line).append("\n");
                    if (isStartOfPlSql(line)) {
                        scanner.useDelimiter(PLSQL_END);
                        isPlSql = true;
                        if (scanner.hasNext()) {
                            String next = scanner.next();
                            buffer.append(next);
                            String stmt = buffer.toString().replace(";\n/", "");
                            statements.add(stmt + ";");
                            buffer = new StringBuilder();
                            scanner.useDelimiter(SQL_END);
                            if (scanner.hasNext()) {
                                scanner.skip("[\\s]?;|[\\s]?/");
                            }
                            break;
                        } else {
                            scanner.useDelimiter(SQL_END);
                            break;
                        }
                    } else if (!isPlSql && isSqlEnd(line)) {
                        String stmt = buffer.toString().trim().replaceFirst(";.*$", "");
                        if(PLSQL_CREATE_OR_REPLACE_FUNCTION.matcher(stmt).find()){
                            stmt = stmt +";"; // sometimes plsql (such as functions with using clause) ends with ; and not END;\n/
                        }
                        statements.add(stmt);
                        
                        buffer = new StringBuilder();
                    }
                    scanner.skip(IGNORED_LINES);

                }

                if (buffer.length() > 1) {
                    statements.add(buffer.toString().trim());
                }

            }
            return statements;
        }
    }

    private static boolean isSqlEnd(String line) {
        return SQL_END.matcher(line).find();
    }

    private static boolean isStartOfPlSql(String line) {
        return PLSQL_BEGIN.matcher(line).find()  && !IS_NULL.matcher(line).find();
    }

    private static String readFileToString(File file) throws UnsupportedEncodingException, IOException {
        return new String(Files.readAllBytes(file.toPath()), "UTF-8");
    }

}