Skip to content

Parse Oracle PL/SQL

Oracle's PL/SQL — anonymous blocks, stored procedures, functions, packages, triggers — is parsed by a dedicated parser inside TGSqlParser. You don't need to do anything special: when the parser sees a PL/SQL construct it dispatches to the PL/SQL grammar automatically.

Quick example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
using gudusoft.gsqlparser;

var parser = new TGSqlParser(EDbVendor.dbvoracle);
parser.sqltext = """
    CREATE OR REPLACE PROCEDURE raise_salary (
        emp_id    IN NUMBER,
        increment IN NUMBER
    ) AS
    BEGIN
        UPDATE employees
           SET salary = salary + increment
         WHERE employee_id = emp_id;

        IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20001, 'No such employee');
        END IF;
    END raise_salary;
    """;

if (parser.parse() == 0)
    Console.WriteLine($"Parsed: {parser.sqlstatements.get(0).sqlstatementtype}");
else
    Console.Error.WriteLine(parser.Errormessage);

What gets parsed

Construct Statement type
Anonymous block (BEGIN..END;) sstplsql_block
CREATE PROCEDURE sstcreate_procedure
CREATE FUNCTION sstcreate_function
CREATE PACKAGE / PACKAGE BODY sstcreate_package / sstcreate_package_body
CREATE TRIGGER sstcreate_trigger
CREATE TYPE sstcreate_type

The parsed statement is one of the gudusoft.gsqlparser.stmt.oracle.* subclasses — for example TPlsqlCreatePackage, TPlsqlCreateProcedure, TPlsqlCreateFunction. Cast the statement to access PL/SQL-specific properties.

1
2
3
4
5
6
7
8
using gudusoft.gsqlparser.stmt.oracle;

if (parser.sqlstatements.get(0) is TPlsqlCreatePackage pkg)
{
    Console.WriteLine($"Package: {pkg.PackageName}");
    foreach (var subprogram in pkg.Subprograms)
        Console.WriteLine($"  - {subprogram.Name}");
}

SQL*Plus commands

SQL*Plus commands (/, EXIT, SHOW PARAMETER, WHENEVER SQLERROR, …) are recognised as TSqlplusCmdStatement. They don't have a full AST — String returns the raw command text.

Tips

  • Use the Oracle vendor (EDbVendor.dbvoracle) for both ANSI SQL and PL/SQL — there is no separate "PL/SQL only" mode.
  • Comments inside PL/SQL blocks are preserved on the source-token list, so you can re-emit annotated SQL via TScriptGenerator.
  • Triggers (CREATE TRIGGER) and packages (CREATE PACKAGE BODY) often contain dozens of nested subprograms — walk them via the visitor pattern (see Advanced Features).