How to Parse Oracle PL/SQL in Java

To parse Oracle PL/SQL in Java, create a TGSqlParser with EDbVendor.dbvoracle, call parse(), and cast each result to its PL/SQL statement class — TPlsqlCreateProcedure, TPlsqlCreateFunction, TPlsqlCreatePackage, or TPlsqlCreateTrigger. Each stored-program statement exposes getBodyStatements() for the executable section and getDeclareStatements() for declarations, so you can walk every SQL statement embedded in procedure code — including dynamic SQL issued via EXECUTE IMMEDIATE.

This guide covers the full workflow: parsing a procedure, enumerating the members of a CREATE PACKAGE BODY, extracting the plain DML (SELECT/INSERT/UPDATE/DELETE) buried inside procedure bodies, and handling dynamic SQL.

Prerequisites

• GSP installed per the Quick Start
• Familiarity with basic SQL parsing

Parse a PL/SQL Procedure

EDbVendor.dbvoracle handles both plain Oracle SQL and full PL/SQL — there is no separate "PL/SQL mode". A successful parse() returns 0, and each top-level statement is available from getSqlstatements().

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;

public class ParseProcedure {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext =
            "CREATE OR REPLACE PROCEDURE raise_salary (p_emp_id NUMBER, p_pct NUMBER) IS\n" +
            "  v_old_salary NUMBER;\n" +
            "BEGIN\n" +
            "  SELECT salary INTO v_old_salary FROM employees WHERE employee_id = p_emp_id;\n" +
            "  UPDATE employees SET salary = salary * (1 + p_pct / 100)\n" +
            "   WHERE employee_id = p_emp_id;\n" +
            "  INSERT INTO salary_audit (emp_id, old_salary, changed_on)\n" +
            "  VALUES (p_emp_id, v_old_salary, SYSDATE);\n" +
            "END;";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TPlsqlCreateProcedure proc =
            (TPlsqlCreateProcedure) parser.getSqlstatements().get(0);

        System.out.println("Procedure: " + proc.getProcedureName());
        System.out.println("Parameters: " + proc.getParameterDeclarations().size());
        System.out.println("Body statements: " + proc.getBodyStatements().size());

        for (TCustomSqlStatement stmt : proc.getBodyStatements()) {
            System.out.println("  " + stmt.sqlstatementtype);
        }
    }
}

Output:

Procedure: raise_salary
Parameters: 2
Body statements: 3
  sstselect
  sstupdate
  sstinsert

Key PL/SQL statement classes

All classes live in gudusoft.gsqlparser.stmt.oracle and extend TCommonStoredProcedureSqlStatement:

Class	sqlstatementtype	Name getter
TPlsqlCreateProcedure	sstplsql_createprocedure	getProcedureName()
TPlsqlCreateFunction	sstplsql_createfunction	getFunctionName(), plus getReturnDataType()
TPlsqlCreatePackage	sstplsql_createpackage	getPackageName() — spec and body, distinguished by getKind()
TPlsqlCreateTrigger	sstplsql_createtrigger	getTriggerName(), plus getTriggerBody()

All of them inherit three important accessors:

• getParameterDeclarations() — a TParameterDeclarationList of formal parameters
• getDeclareStatements() — a TStatementList for the declaration section
• getBodyStatements() — a TStatementList for the BEGIN ... END executable section

Anonymous DECLARE ... BEGIN ... END; blocks parse with statement type ESqlStatementType.sst_plsql_block and expose the same getDeclareStatements() / getBodyStatements() pair.

Parse a CREATE PACKAGE Body and Enumerate Its Members

A package's procedures and functions are declarations of the package, so they appear in getDeclareStatements() — not in getBodyStatements(), which holds only the optional package initialization block.

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateProcedure;
import gudusoft.gsqlparser.stmt.oracle.TPlsqlCreateFunction;

public class ParsePackage {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext =
            "CREATE OR REPLACE PACKAGE BODY emp_mgmt AS\n" +
            "  FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2) RETURN NUMBER IS\n" +
            "    new_empno NUMBER(6);\n" +
            "  BEGIN\n" +
            "    SELECT employees_seq.NEXTVAL INTO new_empno FROM DUAL;\n" +
            "    INSERT INTO employees (employee_id, last_name, job_id)\n" +
            "    VALUES (new_empno, last_name, job_id);\n" +
            "    RETURN new_empno;\n" +
            "  END;\n" +
            "  PROCEDURE remove_emp (p_employee_id NUMBER) IS\n" +
            "  BEGIN\n" +
            "    DELETE FROM employees WHERE employee_id = p_employee_id;\n" +
            "  END;\n" +
            "END emp_mgmt;";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        TPlsqlCreatePackage pkg =
            (TPlsqlCreatePackage) parser.getSqlstatements().get(0);
        System.out.println("Package: " + pkg.getPackageName());

        // Procedures/functions defined in the package live in getDeclareStatements()
        for (TCustomSqlStatement member : pkg.getDeclareStatements()) {
            if (member instanceof TPlsqlCreateProcedure) {
                TPlsqlCreateProcedure p = (TPlsqlCreateProcedure) member;
                System.out.println("  PROCEDURE " + p.getProcedureName()
                    + " (" + p.getBodyStatements().size() + " body statements)");
            } else if (member instanceof TPlsqlCreateFunction) {
                TPlsqlCreateFunction f = (TPlsqlCreateFunction) member;
                System.out.println("  FUNCTION " + f.getFunctionName()
                    + " RETURN " + f.getReturnDataType());
            }
        }
    }
}

Extract All SQL Statements from Procedure Bodies

Real PL/SQL nests statements arbitrarily deep — an UPDATE inside an IF inside a LOOP inside a procedure inside a package. Rather than special-casing every container, recurse through getStatements(), the generic child-statement list every TCustomSqlStatement exposes:

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TCustomSqlStatement;
import java.util.ArrayList;
import java.util.List;

public class ExtractEmbeddedSql {

    static void collectDml(TCustomSqlStatement stmt, List<TCustomSqlStatement> found) {
        switch (stmt.sqlstatementtype) {
            case sstselect:
            case sstinsert:
            case sstupdate:
            case sstdelete:
            case sstmerge:
                found.add(stmt);
                break;
            default:
                // container statement (procedure, block, IF, LOOP, ...)
        }
        for (int i = 0; i < stmt.getStatements().size(); i++) {
            collectDml(stmt.getStatements().get(i), found);
        }
    }

    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext = "..."; // any PL/SQL script

        if (parser.parse() == 0) {
            List<TCustomSqlStatement> dml = new ArrayList<>();
            for (int i = 0; i < parser.getSqlstatements().size(); i++) {
                collectDml(parser.getSqlstatements().get(i), dml);
            }
            for (TCustomSqlStatement s : dml) {
                System.out.println(s.sqlstatementtype + " -> " + s.toString());
            }
        }
    }
}

Two lists that are easy to confuse:

• getBodyStatements() — the syntactic BEGIN ... END section of a block statement
• getStatements() — the generic child list on every statement; use it for recursive traversal

stmt.toString() reconstructs the original SQL text of a statement from its token range, and stmt.getStartToken().lineNo gives its position in the source — useful when reporting findings back against the original file.

This recursive pattern is the foundation for table and column extraction, data lineage, and security auditing over stored-procedure code.

Handle Dynamic SQL: EXECUTE IMMEDIATE

EXECUTE IMMEDIATE parses to TExecImmeStmt (package gudusoft.gsqlparser.stmt, statement type sstplsql_execimmestmt). GSP exposes the dynamic string expression and — when the string is a literal the parser can resolve — the parsed inner statements as well:

import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.ESqlStatementType;
import gudusoft.gsqlparser.stmt.TExecImmeStmt;

public class DynamicSqlDemo {
    public static void main(String[] args) {
        TGSqlParser parser = new TGSqlParser(EDbVendor.dbvoracle);
        parser.sqltext =
            "EXECUTE IMMEDIATE 'DELETE FROM order_staging WHERE load_date < SYSDATE - 30';";

        if (parser.parse() != 0) {
            System.err.println(parser.getErrormessage());
            return;
        }

        if (parser.getSqlstatements().get(0).sqlstatementtype
                == ESqlStatementType.sstplsql_execimmestmt) {
            TExecImmeStmt exec = (TExecImmeStmt) parser.getSqlstatements().get(0);
            System.out.println("Dynamic expr:  " + exec.getDynamicStringExpr());
            System.out.println("Resolved SQL:  " + exec.getDynamicSQL());
            System.out.println("Inner parsed:  " + exec.getDynamicStatements().size());
        }
    }
}

Inside procedure bodies, find TExecImmeStmt nodes with the same recursive getStatements() walk shown above. Useful accessors:

• getDynamicStringExpr() — the TExpression after EXECUTE IMMEDIATE
• getDynamicSQL() — the resolved SQL string when the expression is a literal
• getDynamicStatements() — a TStatementList of the parsed inner statement(s)
• getIntoVariables() / getBindArguments() — INTO targets and USING bind arguments

When the dynamic string is assembled at runtime (concatenated from variables), no static parser can know the final SQL. Treat getDynamicStringExpr() as the analysis boundary in that case: flag the statement for manual review, or walk the concatenation expression to extract the literal fragments and approximate the tables involved.

Common Pitfalls

• Package members are in getDeclareStatements(). getBodyStatements() on TPlsqlCreatePackage holds only the initialization block.
• Check the parse result before casting. Only cast statements after parse() returns 0; see Error Handling for diagnosing failures.
• One vendor per parser. PL/SQL requires EDbVendor.dbvoracle; feeding the same text to dbvmssql or dbvpostgresql produces syntax errors, not a best-effort tree.
• Large codebases: parse files independently and reuse parser instances — see Performance Optimization.

Related Pages

• Handle SQL Server T-SQL — the equivalent guide for T-SQL batches and procedures
• Error Handling — syntax errors, line/column locations, batch validation
• Oracle SQL syntax support — coverage reference
• SQL Parser Use Cases — lineage, impact analysis, auditing built on these APIs