package gudusoft.gsqlparser.parser;

import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TBaseType;
import gudusoft.gsqlparser.TCustomLexer;
import gudusoft.gsqlparser.TCustomParser;
import gudusoft.gsqlparser.TCustomSqlStatement;
import gudusoft.gsqlparser.TLexerOracle;
import gudusoft.gsqlparser.TParserOracleSql;
import gudusoft.gsqlparser.TParserOraclePLSql;
import gudusoft.gsqlparser.TSourceToken;
import gudusoft.gsqlparser.TSourceTokenList;
import gudusoft.gsqlparser.TStatementList;
import gudusoft.gsqlparser.TSyntaxError;
import gudusoft.gsqlparser.EFindSqlStateType;
import gudusoft.gsqlparser.ESqlPlusCmd;
import gudusoft.gsqlparser.ETokenType;
import gudusoft.gsqlparser.ETokenStatus;
import gudusoft.gsqlparser.ESqlStatementType;
import gudusoft.gsqlparser.EErrorType;
import gudusoft.gsqlparser.stmt.oracle.TSqlplusCmdStatement;
import gudusoft.gsqlparser.stmt.TUnknownSqlStatement;
import gudusoft.gsqlparser.sqlcmds.ISqlCmds;
import gudusoft.gsqlparser.sqlcmds.SqlCmdsFactory;
import gudusoft.gsqlparser.compiler.TContext;
import gudusoft.gsqlparser.sqlenv.TSQLEnv;
import gudusoft.gsqlparser.compiler.TGlobalScope;
import gudusoft.gsqlparser.compiler.TFrame;
import gudusoft.gsqlparser.resolver.TSQLResolver;
import gudusoft.gsqlparser.TLog;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.compiler.TASTEvaluator;
import java.util.Stack;

import java.io.BufferedReader;
import java.util.ArrayList;
import java.util.List;

/**
 * Oracle database SQL parser implementation.
 *
 * <p>This parser handles Oracle-specific SQL syntax including:
 * <ul>
 *   <li>PL/SQL blocks (procedures, functions, packages, triggers)</li>
 *   <li>SQL*Plus commands (spool, set, show, etc.)</li>
 *   <li>Oracle-specific DML/DDL (MERGE, flashback, etc.)</li>
 *   <li>Oracle analytical functions and extensions</li>
 *   <li>Special token handling (INNER, NOT DEFERRABLE, etc.)</li>
 * </ul>
 *
 * <p><b>Implementation Status:</b> PHASE 3 - IN PROGRESS
 * <ul>
 *   <li><b>Completed:</b> Oracle classes (TLexerOracle, TParserOracleSql, TParserOraclePLSql) are now PUBLIC</li>
 *   <li><b>Current:</b> Skeleton implementation delegates to legacy TGSqlParser</li>
 *   <li><b>Next:</b> Extract vendor-specific logic from TGSqlParser into this class</li>
 *   <li><b>Goal:</b> Fully self-contained Oracle parser using AbstractSqlParser template</li>
 * </ul>
 *
 * <p><b>Design Notes:</b>
 * <ul>
 *   <li>Implements {@link SqlParser} directly (will extend {@link AbstractSqlParser} in Phase 4)</li>
 *   <li>Can now directly instantiate: {@link TLexerOracle}, {@link TParserOracleSql}, {@link TParserOraclePLSql}</li>
 *   <li>Uses two parsers: TParserOracleSql (SQL) + TParserOraclePLSql (PL/SQL blocks)</li>
 *   <li>Handles SQL*Plus commands via special tokenization logic</li>
 *   <li>Delimiter character: '/' for PL/SQL blocks, ';' for SQL statements</li>
 * </ul>
 *
 * <p><b>Usage Example:</b>
 * <pre>
 * // Get Oracle parser from factory
 * SqlParser parser = SqlParserFactory.get(EDbVendor.dbvoracle);
 *
 * // Build context
 * ParserContext context = new ParserContext.Builder(EDbVendor.dbvoracle)
 *     .sqlText("SELECT * FROM emp WHERE deptno = 10")
 *     .build();
 *
 * // Parse
 * SqlParseResult result = parser.parse(context);
 *
 * // Access statements
 * TStatementList statements = result.getSqlStatements();
 * </pre>
 *
 * <p><b>Phase 3 Extraction Roadmap:</b>
 * <ol>
 *   <li>✅ DONE: Make TLexerOracle, TParserOracleSql, TParserOraclePLSql public</li>
 *   <li>⏳ TODO: Extract tokenization logic (~367 lines from TGSqlParser.dooraclesqltexttotokenlist())</li>
 *   <li>⏳ TODO: Extract raw statement logic (~200 lines from TGSqlParser.dooraclegetrawsqlstatements())</li>
 *   <li>⏳ TODO: Extract parsing orchestration (SQL vs PL/SQL parser selection)</li>
 *   <li>⏳ TODO: Extract helper methods (getanewsourcetoken, getprevsolidtoken, etc.)</li>
 *   <li>⏳ TODO: Extend AbstractSqlParser and use template method pattern fully</li>
 *   <li>⏳ TODO: Remove all delegation to TGSqlParser</li>
 * </ol>
 *
 * <p><b>Key Methods to Extract from TGSqlParser:</b>
 * <ul>
 *   <li>{@code dooraclesqltexttotokenlist()} - Oracle tokenization with SQL*Plus command detection</li>
 *   <li>{@code dooraclegetrawsqlstatements()} - Oracle raw statement boundaries (handles PL/SQL blocks)</li>
 *   <li>{@code getanewsourcetoken()} - Token iterator from lexer</li>
 *   <li>{@code getprevsolidtoken()} - Navigate token list backwards</li>
 *   <li>{@code IsValidPlaceForDivToSqlplusCmd()} - Slash vs divide operator disambiguation</li>
 *   <li>{@code countLines()} - Multi-line token handling</li>
 *   <li>{@code spaceAtTheEndOfReturnToken()} - SQL*Plus command validation</li>
 * </ul>
 *
 * @see SqlParser
 * @see AbstractSqlParser
 * @see TLexerOracle
 * @see TParserOracleSql
 * @see TParserOraclePLSql
 * @since 3.2.0.0
 */
public class OracleSqlParser extends AbstractSqlParser {

    /**
     * Construct Oracle SQL parser.
     * <p>
     * Configures the parser for Oracle database with default delimiters:
     * <ul>
     *   <li>SQL statements: semicolon (;)</li>
     *   <li>PL/SQL blocks: forward slash (/)</li>
     * </ul>
     * <p>
     * Following the original TGSqlParser pattern, the lexer and parsers are
     * created once in the constructor and reused for all parsing operations.
     * This avoids unnecessary object allocation overhead since the parser
     * is not thread-safe and designed for single-use per instance.
     */
    public OracleSqlParser() {
        super(EDbVendor.dbvoracle);
        this.delimiterChar = '/';  // PL/SQL delimiter
        this.defaultDelimiterStr = ";";  // SQL delimiter

        // Create lexer once - will be reused for all parsing operations
        // (matches original TGSqlParser constructor pattern at line 1033)
        this.flexer = new TLexerOracle();
        this.flexer.delimiterchar = this.delimiterChar;
        this.flexer.defaultDelimiterStr = this.defaultDelimiterStr;

        // Set parent's lexer reference for shared tokenization logic
        this.lexer = this.flexer;

        // Create parsers once - will be reused for all parsing operations
        // Token list will be set/updated when parsing begins
        // (matches original TGSqlParser constructor pattern at lines 1036-1040)
        this.fparser = new TParserOracleSql(null);
        this.fplsqlparser = new TParserOraclePLSql(null);
        this.fparser.lexer = this.flexer;
        this.fplsqlparser.lexer = this.flexer;

        // NOTE: sourcetokenlist and sqlstatements are initialized in AbstractSqlParser constructor
    }

    // ========== Tokenization State (used during tokenization) ==========
    // These instance variables are used during the tokenization process
    // and are set up at the beginning of tokenization

    /** The Oracle lexer used for tokenization */
    public TLexerOracle flexer;  // Package-accessible for TGSqlParser integration

    // NOTE: sourcetokenlist moved to AbstractSqlParser (inherited)

    /** Optional callback for token processing (can be null) */
    private Object tokenHandle;  // TTokenCallback interface - keeping as Object for now

    // State variables for tokenization (set during dooraclesqltexttotokenlist())
    private boolean continuesqlplusatnewline;
    private boolean waitingreturnforsemicolon;
    private boolean waitingreturnforfloatdiv;
    private boolean isvalidplace;
    private boolean insqlpluscmd;
    // Brace nesting inside CREATE JAVA SOURCE ... AS <java code>: the Java
    // body contains its own semicolons, which must not end the SQL statement.
    private int javaBraceNesting = 0;

    // ========== Statement Parsing State (used during statement parsing) ==========
    // These instance variables are used during the statement parsing process

    // NOTE: The following fields moved to AbstractSqlParser (inherited):
    //   - sqlcmds (ISqlCmds)
    //   - sqlstatements (TStatementList)
    //   - parserContext (ParserContext)

    /** Current statement being built */
    private TCustomSqlStatement gcurrentsqlstatement;

    /** SQL parser (for regular SQL statements) */
    private TParserOracleSql fparser;

    /** PL/SQL parser (for PL/SQL blocks) */
    private TParserOraclePLSql fplsqlparser;

    // Note: Global context and frame stack fields inherited from AbstractSqlParser:
    // - protected TContext globalContext
    // - protected TSQLEnv sqlEnv
    // - protected Stack<TFrame> frameStack
    // - protected TFrame globalFrame

    // ========== Enums for State Machine ==========
    // These enums are used by the dooraclegetrawsqlstatements state machine

    enum stored_procedure_status {start,is_as,body,bodyend,end, cursor_declare};
    enum stored_procedure_type {function,procedure,package_spec,package_body, block_with_begin,block_with_declare,
        create_trigger,create_library,cursor_in_package_spec,others};

    static final int stored_procedure_nested_level = 1024;

    // ========== AbstractSqlParser Abstract Methods Implementation ==========

    /**
     * Return the Oracle lexer instance.
     * <p>
     * The lexer is created once in the constructor and reused for all
     * parsing operations. This method simply returns the existing instance,
     * matching the original TGSqlParser pattern where the lexer is created
     * once and reset before each use.
     *
     * @param context parser context (not used, lexer already created)
     * @return the Oracle lexer instance created in constructor
     */
    @Override
    protected TCustomLexer getLexer(ParserContext context) {
        // Return existing lexer instance (created in constructor)
        // No need to create new instance - matches original TGSqlParser pattern
        return this.flexer;
    }

    /**
     * Return the Oracle SQL parser instance with updated token list.
     * <p>
     * The parser is created once in the constructor and reused for all
     * parsing operations. This method updates the token list and returns
     * the existing instance, matching the original TGSqlParser pattern.
     *
     * @param context parser context (not used, parser already created)
     * @param tokens source token list to parse
     * @return the Oracle SQL parser instance created in constructor
     */
    @Override
    protected TCustomParser getParser(ParserContext context, TSourceTokenList tokens) {
        // Update token list for reused parser instance
        this.fparser.sourcetokenlist = tokens;
        return this.fparser;
    }

    /**
     * Return the Oracle PL/SQL parser instance with updated token list.
     * <p>
     * Oracle needs a secondary parser (TParserOraclePLSql) for PL/SQL blocks
     * (procedures, functions, packages, triggers, anonymous blocks).
     * <p>
     * The parser is created once in the constructor and reused for all
     * parsing operations. This method updates the token list and returns
     * the existing instance, matching the original TGSqlParser pattern.
     *
     * @param context parser context (not used, parser already created)
     * @param tokens source token list to parse
     * @return the Oracle PL/SQL parser instance created in constructor
     */
    @Override
    protected TCustomParser getSecondaryParser(ParserContext context, TSourceTokenList tokens) {
        // Update token list for reused parser instance
        this.fplsqlparser.sourcetokenlist = tokens;
        return this.fplsqlparser;
    }

    /**
     * Call Oracle-specific tokenization logic.
     * <p>
     * Delegates to dooraclesqltexttotokenlist which handles Oracle's
     * specific keyword recognition, SQL*Plus commands, forward slash
     * disambiguation, and token generation.
     */
    @Override
    protected void tokenizeVendorSql() {
        dooraclesqltexttotokenlist();
        restoreSqlSetStatements();
    }

    /**
     * SET is both a SQL*Plus command (SET PAGESIZE ..., line-terminated) and
     * the head of the SQL statements SET CONSTRAINT[S] / SET ROLE /
     * SET TRANSACTION (semicolon-terminated). Tokenization cannot look ahead
     * (the token list is built incrementally), so every line-leading SET is
     * first marked sqlpluscmd; a multi-line SET CONSTRAINTS was then truncated
     * at the newline. This pass runs on the complete token list and restores
     * the SQL reading when the word after SET is one of the SQL forms: the
     * SET token gets its saved keyword code back and the rest of its line
     * loses the insqlpluscmd flag, so the statement splitter routes it through
     * the command table (sstoraclesetconstraint etc.) and runs to ';'.
     */
    private void restoreSqlSetStatements() {
        for (int i = 0; i < sourcetokenlist.size(); i++) {
            TSourceToken st = sourcetokenlist.get(i);
            if (st.tokencode != TBaseType.sqlpluscmd) continue;
            if (!st.toString().equalsIgnoreCase("SET")) continue;
            if (st.prevTokenCode != TBaseType.rrw_set) continue;
            TSourceToken nx = st.nextSolidToken();
            if (nx == null) continue;
            String w = nx.toString();
            if (!(w.equalsIgnoreCase("CONSTRAINT") || w.equalsIgnoreCase("CONSTRAINTS")
                    || w.equalsIgnoreCase("ROLE") || w.equalsIgnoreCase("TRANSACTION"))) continue;
            st.tokencode = st.prevTokenCode;
            st.tokentype = ETokenType.ttkeyword;
            st.insqlpluscmd = false;
            for (int j = i + 1; j < sourcetokenlist.size(); j++) {
                TSourceToken t = sourcetokenlist.get(j);
                if (t.tokencode == TBaseType.lexnewline) break;
                t.insqlpluscmd = false;
            }
        }
    }

    /**
     * Post-tokenization: merge ${...} template variable tokens into single IDENT tokens.
     * Template syntax like ${if(len(X) == 0, "", "...")} is used by BI tools.
     */
    @Override
    protected void doAfterTokenize(TSourceTokenList tokens) {
        super.doAfterTokenize(tokens);
        mergeTemplateVariableTokens(tokens);
    }

    private void mergeTemplateVariableTokens(TSourceTokenList tokens) {
        for (int i = 0; i < tokens.size() - 1; i++) {
            TSourceToken dollar = tokens.get(i);

            // Match either bare '$' (self-char) or '$IDENT' like $P, $X (identifier starting with $)
            boolean isDollarChar = (dollar.tokencode == '$');
            boolean isDollarIdent = (dollar.tokencode == TBaseType.ident
                    && dollar.astext != null && dollar.astext.startsWith("$"));
            if (!isDollarChar && !isDollarIdent) continue;

            // Find next non-whitespace token — for $IDENT pattern, require immediate '{' (no whitespace)
            int braceIdx = i + 1;
            if (isDollarChar) {
                while (braceIdx < tokens.size() && tokens.get(braceIdx).tokentype == ETokenType.ttwhitespace) {
                    braceIdx++;
                }
            }
            if (braceIdx >= tokens.size() || tokens.get(braceIdx).tokencode != '{') continue;

            // Found ${ pattern — find matching } with depth tracking
            int depth = 1;
            int endIdx = braceIdx + 1;
            boolean isComplex = false;
            while (endIdx < tokens.size() && depth > 0) {
                int code = tokens.get(endIdx).tokencode;
                if (code == '{') depth++;
                else if (code == '}') depth--;
                else if (code == '(' || code == ',' || code == '\'' || code == '"') isComplex = true;
                if (depth > 0) endIdx++;
            }
            if (depth != 0) continue; // unclosed

            // Build merged token text
            StringBuilder sb = new StringBuilder();
            for (int j = i; j <= endIdx; j++) {
                sb.append(tokens.get(j).astext);
            }

            if (isComplex && isDollarChar) {
                // Complex template starting with bare $ like ${if(len(X)==0,...)}
                // These expand to SQL fragments (e.g., AND clauses) at runtime,
                // so convert to whitespace to let parser skip them entirely.
                for (int j = i; j <= endIdx; j++) {
                    tokens.get(j).tokentype = ETokenType.ttwhitespace;
                    tokens.get(j).tokencode = TBaseType.lexspace;
                }
            } else {
                // Simple template like ${NAME}, or JasperReports $P{VAR}/$X{IN,COL,PARAM}
                // These expand to single values/expressions, so merge into IDENT placeholder.
                dollar.astext = sb.toString();
                dollar.tokencode = TBaseType.ident;
                dollar.tokentype = ETokenType.ttidentifier;
                // Convert remaining tokens to whitespace so parser skips them even if
                // tokenstatus is overwritten by statement splitter (tsignoredbygetrawstatement)
                for (int j = i + 1; j <= endIdx; j++) {
                    tokens.get(j).tokentype = ETokenType.ttwhitespace;
                    tokens.get(j).tokencode = TBaseType.lexspace;
                }
            }

            i = endIdx; // skip past merged tokens
        }
    }

    /**
     * Setup Oracle parsers for raw statement extraction.
     * <p>
     * Oracle uses dual parsers (SQL + PL/SQL), so we inject sqlcmds and
     * update token lists for both parsers.
     */
    @Override
    protected void setupVendorParsersForExtraction() {
        // Inject sqlcmds into BOTH parsers (SQL + PL/SQL)
        this.fparser.sqlcmds = this.sqlcmds;
        this.fplsqlparser.sqlcmds = this.sqlcmds;

        // Update token list for BOTH parsers
        this.fparser.sourcetokenlist = this.sourcetokenlist;
        this.fplsqlparser.sourcetokenlist = this.sourcetokenlist;
    }

    /**
     * Call Oracle-specific raw statement extraction logic.
     * <p>
     * Delegates to dooraclegetrawsqlstatements which handles Oracle's
     * statement delimiters (semicolon and forward slash).
     */
    @Override
    protected void extractVendorRawStatements(SqlParseResult.Builder builder) {
        dooraclegetrawsqlstatements(builder);
    }

    /**
     * Perform full parsing of statements with syntax checking.
     * <p>
     * This method orchestrates the parsing of all statements by:
     * <ul>
     *   <li>Using the raw statements passed from AbstractSqlParser.parse()</li>
     *   <li>Initializing SQL and PL/SQL parsers</li>
     *   <li>Creating global context and frame stack</li>
     *   <li>Looping through each raw statement</li>
     *   <li>Calling parsestatement() on each to build AST</li>
     *   <li>Handling error recovery for CREATE TABLE/INDEX</li>
     *   <li>Collecting syntax errors</li>
     * </ul>
     *
     * <p><b>Important:</b> This method does NOT extract raw statements - they are
     * passed in as a parameter already extracted by {@link #extractRawStatements}.
     * This eliminates duplicate extraction that was occurring in the old design.
     *
     * <p>Extracted from: TGSqlParser.doparse() lines 16903-17026
     *
     * @param context parser context
     * @param parser main SQL parser (TParserOracleSql)
     * @param secondaryParser PL/SQL parser (TParserOraclePLSql)
     * @param tokens source token list
     * @param rawStatements raw statements already extracted (never null)
     * @return list of fully parsed statements with AST built
     */
    @Override
    protected TStatementList performParsing(ParserContext context,
                                           TCustomParser parser,
                                           TCustomParser secondaryParser,
                                           TSourceTokenList tokens,
                                           TStatementList rawStatements) {
        // Store references
        this.fparser = (TParserOracleSql) parser;
        this.fplsqlparser = (TParserOraclePLSql) secondaryParser;
        this.sourcetokenlist = tokens;
        this.parserContext = context;

        // Use the raw statements passed from AbstractSqlParser.parse()
        // (already extracted - DO NOT re-extract to avoid duplication)
        this.sqlstatements = rawStatements;

        // Initialize statement parsing infrastructure
        this.sqlcmds = SqlCmdsFactory.get(vendor);

        // Inject sqlcmds into parsers (required for make_stmt and other methods)
        this.fparser.sqlcmds = this.sqlcmds;
        this.fplsqlparser.sqlcmds = this.sqlcmds;

        // Initialize global context for semantic analysis
        // CRITICAL: When delegated from TGSqlParser, use TGSqlParser's frameStack
        // so that variables set in statements can be found by other statements
        if (context != null && context.getGsqlparser() != null) {
            TGSqlParser gsqlparser = (TGSqlParser) context.getGsqlparser();
            this.frameStack = gsqlparser.getFrameStack();

            // CRITICAL: Set gsqlparser on the NodeFactory - matches TGSqlParser behavior
            // This is needed for proper AST node creation during parsing
            // Without this, expression traversal order may differ, causing
            // dataflow constant ordering issues
            this.fparser.getNf().setGsqlParser(gsqlparser);
            this.fplsqlparser.getNf().setGsqlParser(gsqlparser);

            // Create global context if needed
            this.globalContext = new TContext();
            this.sqlEnv = new TSQLEnv(this.vendor) {
                @Override
                public void initSQLEnv() {
                }
            };
            this.globalContext.setSqlEnv(this.sqlEnv, this.sqlstatements);
        } else {
            initializeGlobalContext();
        }

        // Parse each statement with exception handling for robustness
        for (int i = 0; i < sqlstatements.size(); i++) {
            TCustomSqlStatement stmt = sqlstatements.getRawSql(i);

            try {
                stmt.setFrameStack(frameStack);

                // Parse the statement
                int parseResult = stmt.parsestatement(null, false, context.isOnlyNeedRawParseTree());

                // Oracle-specific post-processing (overridden hook method)
                afterStatementParsed(stmt);

                // Handle error recovery for CREATE TABLE/INDEX
                boolean doRecover = TBaseType.ENABLE_ERROR_RECOVER_IN_CREATE_TABLE;
                if (doRecover && ((parseResult != 0) || (stmt.getErrorCount() > 0))) {
                    handleCreateTableErrorRecovery(stmt);
                }

                // Collect syntax errors
                if ((parseResult != 0) || (stmt.getErrorCount() > 0)) {
                    copyErrorsFromStatement(stmt);
                }

            } catch (Exception ex) {
                // Use inherited exception handler from AbstractSqlParser
                // This provides consistent error handling across all database parsers
                handleStatementParsingException(stmt, i, ex);
                continue;
            }
        }

        // Clean up frame stack
        if (globalFrame != null) {
            globalFrame.popMeFromStack(frameStack);
        }

        return this.sqlstatements;
    }

    // Note: initializeGlobalContext() inherited from AbstractSqlParser

    /**
     * Override to provide Oracle-specific post-processing after statement parsing.
     * <p>
     * For Oracle, we check if the statement is PL/SQL and recursively find syntax
     * errors in nested PL/SQL statements.
     */
    @Override
    protected void afterStatementParsed(TCustomSqlStatement stmt) {
        if (stmt.isoracleplsql()) {
            findAllSyntaxErrorsInPlsql(stmt);
        }
    }

    /**
     * Perform Oracle-specific semantic analysis using TSQLResolver.
     *
     * <p>This includes:
     * <ul>
     *   <li>Column-to-table resolution</li>
     *   <li>Dataflow analysis</li>
     *   <li>Reference resolution</li>
     *   <li>Scope resolution</li>
     * </ul>
     *
     * @param context the parser context
     * @param statements the parsed statements
     */
    @Override
    protected void performSemanticAnalysis(ParserContext context, TStatementList statements) {
        if (TBaseType.isEnableResolver() && getSyntaxErrors().isEmpty()) {
            TSQLResolver resolver = new TSQLResolver(globalContext, statements);
            resolver.resolve();
        }
    }

    /**
     * Perform Oracle-specific AST interpretation/evaluation using TASTEvaluator.
     *
     * <p>This executes simple SQL statements and evaluates expressions
     * for static analysis and constant folding.
     *
     * @param context the parser context
     * @param statements the parsed statements
     */
    @Override
    protected void performInterpreter(ParserContext context, TStatementList statements) {
        if (TBaseType.ENABLE_INTERPRETER && getSyntaxErrors().isEmpty()) {
            TLog.clearLogs();
            TGlobalScope interpreterScope = new TGlobalScope(sqlEnv);
            TLog.enableInterpreterLogOnly();
            TASTEvaluator astEvaluator = new TASTEvaluator(statements, interpreterScope);
            astEvaluator.eval();
        }
    }

    // ========== Raw Statement Extraction ==========
    // These methods extract raw SQL statements from tokens without full parsing
    // Extracted from TGSqlParser.dooraclegetrawsqlstatements() and related methods

    /**
     * Extract raw Oracle SQL statements from tokenized source.
     * <p>
     * This is the main Oracle statement extraction state machine that:
     * <ul>
     *   <li>Groups tokens into statement boundaries</li>
     *   <li>Identifies statement types (SQL vs PL/SQL, SQL*Plus commands)</li>
     *   <li>Handles nested PL/SQL blocks (procedures, functions, packages, triggers)</li>
     *   <li>Tracks BEGIN/END pairs and other block delimiters</li>
     *   <li>Detects statement terminators (semicolon, forward slash, period)</li>
     * </ul>
     *
     * <p><b>State Machine:</b> Uses 4 main states:
     * <ul>
     *   <li>{@code stnormal} - Between statements, looking for start of next statement</li>
     *   <li>{@code stsql} - Inside a SQL statement</li>
     *   <li>{@code stsqlplus} - Inside a SQL*Plus command</li>
     *   <li>{@code ststoredprocedure} - Inside a PL/SQL block (procedure/function/package/trigger)</li>
     *   <li>{@code sterror} - Error recovery mode</li>
     * </ul>
     *
     * <p><b>Extracted from:</b> TGSqlParser.dooraclegetrawsqlstatements() (lines 10071-10859)
     *
     * <p><b>Design Note:</b> This method now receives a builder to populate with results,
     * following Option A design where the vendor-specific method focuses on parsing logic
     * while extractRawStatements() handles result construction.
     *
     * @param builder the result builder to populate with statements and error information
     */
    private void dooraclegetrawsqlstatements(SqlParseResult.Builder builder) {
        int waitingEnds[] = new int[stored_procedure_nested_level];
        stored_procedure_type sptype[] = new stored_procedure_type[stored_procedure_nested_level];
        stored_procedure_status procedure_status[] = new stored_procedure_status[stored_procedure_nested_level];
        boolean endBySlashOnly = true;
        int nestedProcedures = 0, nestedParenthesis = 0;
        // Flag for CREATE MLE MODULE with AS clause - terminates with / not ;
        boolean mleModuleWithAs = false;
        // Flag for WITH FUNCTION/PROCEDURE - track BEGIN/END nesting to handle embedded semicolons
        boolean withPlsqlDefinition = false;
        int withPlsqlBeginEndNesting = 0;
        boolean withPlsqlFoundSelect = false;  // True when SELECT has been found after WITH FUNCTION
        // Track whether the current CTE statement's main SELECT has been found
        // (i.e., the SELECT after WITH name AS (...) at paren level 0)
        boolean cteMainSelectFound = false;

        if (TBaseType.assigned(sqlstatements)) sqlstatements.clear();
        if (!TBaseType.assigned(sourcetokenlist)) {
            // No tokens available - populate builder with error and return
            builder.errorCode(1);
            builder.errorMessage("No source token list available");
            builder.sqlStatements(new TStatementList());
            return;
        }

        gcurrentsqlstatement = null;
        EFindSqlStateType gst = EFindSqlStateType.stnormal;
        TSourceToken lcprevsolidtoken = null, ast = null;

        // Main tokenization loop
        for (int i = 0; i < sourcetokenlist.size(); i++) {

            if ((ast != null) && (ast.issolidtoken()))
                lcprevsolidtoken = ast;

            ast = sourcetokenlist.get(i);
            sourcetokenlist.curpos = i;

            // Token-specific keyword transformations for Oracle
            performRawStatementTokenTransformations(ast);

            // State machine processing
            switch (gst) {
                case sterror: {
                    if (ast.tokentype == ETokenType.ttsemicolon) {
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                        gst = EFindSqlStateType.stnormal;
                    } else {
                        appendToken(gcurrentsqlstatement, ast);
                    }
                    break;
                } //sterror

                case stnormal: {
                    if ((ast.tokencode == TBaseType.cmtdoublehyphen)
                            || (ast.tokencode == TBaseType.cmtslashstar)
                            || (ast.tokencode == TBaseType.lexspace)
                            || (ast.tokencode == TBaseType.lexnewline)
                            || (ast.tokentype == ETokenType.ttsemicolon)) {
                        if (gcurrentsqlstatement != null) {
                            appendToken(gcurrentsqlstatement, ast);
                        }

                        if ((lcprevsolidtoken != null) && (ast.tokentype == ETokenType.ttsemicolon)) {
                            if (lcprevsolidtoken.tokentype == ETokenType.ttsemicolon) {
                                // ;;;; continuous semicolon, treat it as comment
                                ast.tokentype = ETokenType.ttsimplecomment;
                                ast.tokencode = TBaseType.cmtdoublehyphen;
                            }
                        }

                        continue;
                    }

                    if (ast.tokencode == TBaseType.sqlpluscmd) {
                        gst = EFindSqlStateType.stsqlplus;
                        gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                        appendToken(gcurrentsqlstatement, ast);
                        continue;
                    }

                    // find a token to start sql or plsql mode
                    gcurrentsqlstatement = sqlcmds.issql(ast, gst, gcurrentsqlstatement);

                    if (gcurrentsqlstatement != null) {
                        if (gcurrentsqlstatement.isoracleplsql()) {
                            nestedProcedures = 0;
                            gst = EFindSqlStateType.ststoredprocedure;
                            appendToken(gcurrentsqlstatement, ast);

                            switch (gcurrentsqlstatement.sqlstatementtype) {
                                case sstplsql_createprocedure:
                                    sptype[nestedProcedures] = stored_procedure_type.procedure;
                                    break;
                                case sstplsql_createfunction:
                                    sptype[nestedProcedures] = stored_procedure_type.function;
                                    break;
                                case sstplsql_createpackage:
                                    sptype[nestedProcedures] = stored_procedure_type.package_spec;
                                    if (ast.searchToken(TBaseType.rrw_body, 5) != null) {
                                        sptype[nestedProcedures] = stored_procedure_type.package_body;
                                    }
                                    break;
                                case sst_plsql_block:
                                    sptype[nestedProcedures] = stored_procedure_type.block_with_declare;
                                    if (ast.tokencode == TBaseType.rrw_begin) {
                                        sptype[nestedProcedures] = stored_procedure_type.block_with_begin;
                                    }
                                    break;
                                case sstplsql_createtrigger:
                                    sptype[nestedProcedures] = stored_procedure_type.create_trigger;
                                    break;
                                case sstoraclecreatelibrary:
                                    sptype[nestedProcedures] = stored_procedure_type.create_library;
                                    break;
                                case sstplsql_createtype_placeholder:
                                    gst = EFindSqlStateType.stsql;
                                    break;
                                default:
                                    sptype[nestedProcedures] = stored_procedure_type.others;
                                    break;
                            }

                            if (sptype[0] == stored_procedure_type.block_with_declare) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.is_as;
                            } else if (sptype[0] == stored_procedure_type.block_with_begin) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.body;
                            } else if (sptype[0] == stored_procedure_type.procedure) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.function) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.package_spec) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.package_body) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.create_trigger) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.start;
                            } else if (sptype[0] == stored_procedure_type.create_library) {
                                endBySlashOnly = false;
                                procedure_status[0] = stored_procedure_status.bodyend;
                            } else {
                                endBySlashOnly = true;
                                procedure_status[0] = stored_procedure_status.bodyend;
                            }

                            if ((ast.tokencode == TBaseType.rrw_begin)
                                    || (ast.tokencode == TBaseType.rrw_package)
                                    || (ast.searchToken(TBaseType.rrw_package, 4) != null)) {
                                waitingEnds[nestedProcedures] = 1;
                            }
                        } else {
                            gst = EFindSqlStateType.stsql;
                            appendToken(gcurrentsqlstatement, ast);
                            nestedParenthesis = 0;
                            // Check if this is CREATE MLE MODULE with AS clause (JavaScript code)
                            // If AS is found after LANGUAGE JAVASCRIPT, it terminates with / not ;
                            if (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstoraclecreatemlemodule) {
                                // Look ahead to see if there's an AS keyword
                                TSourceToken asToken = ast.searchToken(TBaseType.rrw_as, 10);
                                mleModuleWithAs = (asToken != null);
                            } else {
                                mleModuleWithAs = false;
                            }

                            // Check if this is WITH FUNCTION/PROCEDURE (Oracle 12c inline PL/SQL)
                            // Need to track BEGIN/END nesting to handle embedded semicolons
                            if (ast.tokencode == TBaseType.rrw_with && gcurrentsqlstatement.isctequery) {
                                // Look ahead for FUNCTION or PROCEDURE keyword
                                TSourceToken nextSolid = ast.nextSolidToken();
                                if (nextSolid != null && (nextSolid.tokencode == TBaseType.rrw_function
                                        || nextSolid.tokencode == TBaseType.rrw_procedure)) {
                                    withPlsqlDefinition = true;
                                    withPlsqlBeginEndNesting = 0;
                                }
                            }
                        }
                    } else {
                        //error token found
                        this.syntaxErrors.add(new TSyntaxError(ast.getAstext(), ast.lineNo, (ast.columnNo < 0 ? 0 : ast.columnNo)
                                , "Error when tokenize", EErrorType.spwarning, TBaseType.MSG_WARNING_ERROR_WHEN_TOKENIZE, null, ast.posinlist));

                        ast.tokentype = ETokenType.tttokenlizererrortoken;
                        gst = EFindSqlStateType.sterror;

                        gcurrentsqlstatement = new TUnknownSqlStatement(vendor);
                        gcurrentsqlstatement.sqlstatementtype = ESqlStatementType.sstinvalid;
                        appendToken(gcurrentsqlstatement, ast);
                    }

                    break;
                } // stnormal

                case stsqlplus: {
                    if (ast.insqlpluscmd) {
                        appendToken(gcurrentsqlstatement, ast);
                    } else {
                        gst = EFindSqlStateType.stnormal; //this token must be newline,
                        appendToken(gcurrentsqlstatement, ast); // so add it here
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                    }

                    break;
                }//case stsqlplus

                case stsql: {
                    // For WITH FUNCTION/PROCEDURE, track BEGIN/END nesting and when SELECT is found
                    if (withPlsqlDefinition) {
                        if (ast.tokencode == TBaseType.rrw_begin) {
                            withPlsqlBeginEndNesting++;
                        } else if (ast.tokencode == TBaseType.rrw_end) {
                            withPlsqlBeginEndNesting--;
                            if (withPlsqlBeginEndNesting < 0) withPlsqlBeginEndNesting = 0;
                        } else if (ast.tokencode == TBaseType.rrw_select && withPlsqlBeginEndNesting == 0) {
                            // Found SELECT after all function definitions are done
                            withPlsqlFoundSelect = true;
                        }
                    }

                    // For CREATE MLE MODULE with AS clause, don't terminate on semicolon
                    // The JavaScript code may contain semicolons; wait for / to terminate
                    // For WITH FUNCTION/PROCEDURE, don't terminate on semicolon until SELECT is found
                    // (the semicolons in function body and after END are part of the function definition)
                    // CREATE JAVA SOURCE ... AS <java code>: semicolons inside
                    // the Java class body (brace depth > 0) are Java statement
                    // terminators, not SQL statement terminators.
                    if ((gcurrentsqlstatement != null)
                            && (gcurrentsqlstatement.sqlstatementtype == gudusoft.gsqlparser.ESqlStatementType.sstoraclecreatejava)) {
                        if ("{".equals(ast.toString())) {
                            javaBraceNesting++;
                        } else if ("}".equals(ast.toString()) && (javaBraceNesting > 0)) {
                            javaBraceNesting--;
                        }
                    }

                    boolean skipSemicolonTermination = mleModuleWithAs || (javaBraceNesting > 0) || (withPlsqlDefinition && !withPlsqlFoundSelect);
                    if (ast.tokentype == ETokenType.ttsemicolon && !skipSemicolonTermination) {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        gcurrentsqlstatement.semicolonended = ast;
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                        mleModuleWithAs = false; // Reset flag
                        javaBraceNesting = 0;
                        withPlsqlDefinition = false; // Reset WITH FUNCTION flag
                        withPlsqlBeginEndNesting = 0;
                        cteMainSelectFound = false;
                        withPlsqlFoundSelect = false;
                        continue;
                    }

                    if (sourcetokenlist.sqlplusaftercurtoken()) //most probably is / cmd
                    {
                        gst = EFindSqlStateType.stnormal;
                        appendToken(gcurrentsqlstatement, ast);
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                        mleModuleWithAs = false; // Reset flag
                        javaBraceNesting = 0;
                        continue;
                    }

                    if (ast.tokencode == '(') nestedParenthesis++;
                    if (ast.tokencode == ')') {
                        nestedParenthesis--;
                        if (nestedParenthesis < 0) nestedParenthesis = 0;
                    }

                    Boolean findNewStmt = false;
                    TCustomSqlStatement lcStmt = null;
                    // Check for new statement: CREATE TABLE (original), or SELECT inside a non-CTE SELECT
                    boolean shouldCheckNewStmt = false;
                    if ((nestedParenthesis == 0) && (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstcreatetable)) {
                        shouldCheckNewStmt = true;
                    } else if ((nestedParenthesis == 0) && (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstselect)
                            && (ast.tokencode == TBaseType.rrw_select || ast.tokencode == TBaseType.rrw_with)) {
                        // Check if current statement is a CTE (starts with WITH)
                        boolean isCteContext = false;
                        for (int si = 0; si < gcurrentsqlstatement.sourcetokenlist.size(); si++) {
                            TSourceToken st = gcurrentsqlstatement.sourcetokenlist.get(si);
                            if (st.tokentype == ETokenType.ttwhitespace || st.tokentype == ETokenType.ttreturn
                                    || st.tokencode == TBaseType.cmtdoublehyphen || st.tokencode == TBaseType.cmtslashstar) {
                                continue;
                            }
                            if (st.tokencode == TBaseType.rrw_with) {
                                isCteContext = true;
                            }
                            break;
                        }
                        // Don't split if previous token makes this SELECT part of current statement:
                        // - Set operators: UNION, INTERSECT, MINUS, EXCEPT, ALL
                        // - Left paren: (SELECT ...) — SELECT is main query of parenthesized expr
                        boolean suppressSplit = false;
                        if (ast.tokencode == TBaseType.rrw_select && lcprevsolidtoken != null) {
                            int prevCode = lcprevsolidtoken.tokencode;
                            if (prevCode == TBaseType.rrw_union || prevCode == TBaseType.rrw_intersect
                                    || prevCode == TBaseType.rrw_minus || prevCode == TBaseType.rrw_except
                                    || prevCode == TBaseType.rrw_all
                                    || prevCode == '(') {
                                suppressSplit = true;
                            }
                        }
                        if (suppressSplit) {
                            // SELECT is part of current statement — don't split
                        } else if (!isCteContext) {
                            // Non-CTE SELECT: any SELECT/WITH at paren level 0 starts a new statement
                            shouldCheckNewStmt = true;
                        } else if (cteMainSelectFound) {
                            // CTE context: main SELECT already consumed, so this SELECT/WITH
                            // at paren level 0 is a new statement
                            shouldCheckNewStmt = true;
                        } else if (ast.tokencode == TBaseType.rrw_select) {
                            // CTE context: this is the main SELECT after WITH name AS (...)
                            cteMainSelectFound = true;
                            // Don't split — this SELECT is part of the CTE statement
                        }
                        // If ast is WITH and main SELECT not yet found, it could be another
                        // CTE definition (WITH a AS (...), b AS (...)) — don't split
                    }
                    if (shouldCheckNewStmt) {
                        // For SELECT-after-SELECT/WITH splitting, use stnormal so issql can detect CTE starts.
                        // For CREATE TABLE, preserve original stsql state to avoid false positives
                        // (e.g., INSERT/DELETE keywords in blockchain table clauses).
                        EFindSqlStateType issqlState = (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstselect)
                                ? EFindSqlStateType.stnormal : gst;
                        lcStmt = sqlcmds.issql(ast, issqlState, gcurrentsqlstatement);
                        if (lcStmt != null) {
                            findNewStmt = true;
                            if (lcStmt.sqlstatementtype == ESqlStatementType.sstselect) {
                                TSourceToken prevst = ast.prevSolidToken();
                                if (gcurrentsqlstatement.sqlstatementtype == ESqlStatementType.sstcreatetable) {
                                    // For CREATE TABLE, suppress split when SELECT follows AS/(/): AS (SELECT ...)
                                    if ((prevst.tokencode == TBaseType.rrw_as) || (prevst.tokencode == '(') || (prevst.tokencode == ')')) {
                                        findNewStmt = false;
                                    }
                                }
                                // For SELECT-after-SELECT/WITH splitting at paren level 0,
                                // no suppression needed — the new SELECT/WITH is a new statement
                            }
                        }
                    }

                    if (findNewStmt) {
                        onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                        gcurrentsqlstatement = lcStmt;
                        cteMainSelectFound = false; // Reset for new statement
                        nestedParenthesis = 0; // Reset paren tracking for new statement
                        appendToken(gcurrentsqlstatement, ast);
                        continue;
                    } else
                        appendToken(gcurrentsqlstatement, ast);

                    break;
                }//case stsql

                case ststoredprocedure: {

                    if (procedure_status[nestedProcedures] != stored_procedure_status.bodyend) {
                        appendToken(gcurrentsqlstatement, ast);
                    }

                    switch (procedure_status[nestedProcedures]) {
                        case cursor_declare:
                            if (ast.tokencode == ';') {
                                nestedProcedures--;
                                if (nestedProcedures < 0) {
                                    nestedProcedures = 0;
                                }
                            }
                            break;
                        case start:
                            if ((ast.tokencode == TBaseType.rrw_as) || (ast.tokencode == TBaseType.rrw_is)) {
                                if (sptype[nestedProcedures] != stored_procedure_type.create_trigger) {
                                    if ((sptype[0] == stored_procedure_type.package_spec) && (nestedProcedures > 0)) {
                                        // when it's a package specification, only top level accept as/is
                                    } else {
                                        procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                                        if (ast.searchToken("language", 1) != null) {
                                            if (nestedProcedures == 0) {
                                                gst = EFindSqlStateType.stsql;
                                            } else {
                                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                                                nestedProcedures--;
                                            }
                                        }
                                    }
                                }
                            } else if (ast.tokencode == TBaseType.rrw_begin) {
                                if (sptype[nestedProcedures] == stored_procedure_type.create_trigger) {
                                    waitingEnds[nestedProcedures]++;
                                }
                                if (nestedProcedures > 0) {
                                    nestedProcedures--;
                                }
                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                if ((nestedProcedures > 0) && (waitingEnds[nestedProcedures - 1] == 1)
                                        && ((sptype[nestedProcedures - 1] == stored_procedure_type.package_body)
                                        || (sptype[nestedProcedures - 1] == stored_procedure_type.package_spec))) {
                                    nestedProcedures--;
                                    procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                }
                            } else if ((ast.tokencode == TBaseType.rrw_procedure) || (ast.tokencode == TBaseType.rrw_function)) {
                                if ((nestedProcedures > 0) && (waitingEnds[nestedProcedures] == 0)
                                        && (procedure_status[nestedProcedures - 1] == stored_procedure_status.is_as)) {
                                    nestedProcedures--;
                                    nestedProcedures++;
                                    waitingEnds[nestedProcedures] = 0;
                                    procedure_status[nestedProcedures] = stored_procedure_status.start;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_oracle_cursor) {
                                if ((nestedProcedures > 0) && (waitingEnds[nestedProcedures] == 0)
                                        && (procedure_status[nestedProcedures - 1] == stored_procedure_status.is_as)) {
                                    nestedProcedures--;
                                    nestedProcedures++;
                                    waitingEnds[nestedProcedures] = 0;
                                    procedure_status[nestedProcedures] = stored_procedure_status.cursor_declare;
                                }
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.create_trigger) && (ast.tokencode == TBaseType.rrw_declare)) {
                                procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.create_trigger)
                                    && (ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) {
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);

                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                appendToken(gcurrentsqlstatement, ast);
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if (sptype[nestedProcedures] == stored_procedure_type.create_trigger) {
                                if (ast.tokencode == TBaseType.rrw_trigger) {
                                    TSourceToken compoundSt = ast.searchToken(TBaseType.rrw_oracle_compound, -1);
                                    if (compoundSt != null) {
                                        procedure_status[nestedProcedures] = stored_procedure_status.body;
                                        waitingEnds[nestedProcedures]++;
                                    }
                                }
                            } else if ((sptype[nestedProcedures] == stored_procedure_type.function)
                                    && (ast.tokencode == TBaseType.rrw_teradata_using)) {
                                if ((ast.searchToken("aggregate", -1) != null) || (ast.searchToken("pipelined", -1) != null)) {
                                    if (nestedProcedures == 0) {
                                        gst = EFindSqlStateType.stsql;
                                    } else {
                                        procedure_status[nestedProcedures] = stored_procedure_status.body;
                                        nestedProcedures--;
                                    }
                                }
                            }
                            break;
                        case is_as:
                            if ((ast.tokencode == TBaseType.rrw_procedure) || (ast.tokencode == TBaseType.rrw_function)) {
                                nestedProcedures++;
                                if (nestedProcedures > stored_procedure_nested_level - 1) {
                                    gst = EFindSqlStateType.sterror;
                                    nestedProcedures--;
                                } else {
                                    waitingEnds[nestedProcedures] = 0;
                                    procedure_status[nestedProcedures] = stored_procedure_status.start;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_begin) {
                                if ((nestedProcedures == 0) &&
                                        ((sptype[nestedProcedures] == stored_procedure_type.package_body)
                                                || (sptype[nestedProcedures] == stored_procedure_type.package_spec))) {
                                    // top level package begin already counted
                                } else {
                                    waitingEnds[nestedProcedures]++;
                                }
                                procedure_status[nestedProcedures] = stored_procedure_status.body;
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                if ((nestedProcedures == 0) && (waitingEnds[nestedProcedures] == 1)
                                        && ((sptype[nestedProcedures] == stored_procedure_type.package_body)
                                        || (sptype[nestedProcedures] == stored_procedure_type.package_spec))) {
                                    procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                    waitingEnds[nestedProcedures]--;
                                } else {
                                    waitingEnds[nestedProcedures]--;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_case) {
                                if (ast.searchToken(';', 1) == null) {
                                    waitingEnds[nestedProcedures]++;
                                }
                            }
                            break;
                        case body:
                            if (ast.tokencode == TBaseType.rrw_begin) {
                                waitingEnds[nestedProcedures]++;
                            } else if (ast.tokencode == TBaseType.rrw_if) {
                                if (ast.searchToken(';', 2) == null) {
                                    waitingEnds[nestedProcedures]++;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_case) {
                                if (ast.searchToken(';', 2) == null) {
                                    if (ast.searchToken(TBaseType.rrw_end, -1) == null) {
                                        waitingEnds[nestedProcedures]++;
                                    }
                                }
                            } else if (ast.tokencode == TBaseType.rrw_loop) {
                                if (!((ast.searchToken(TBaseType.rrw_end, -1) != null)
                                        && (ast.searchToken(';', 2) != null))) {
                                    waitingEnds[nestedProcedures]++;
                                }
                            } else if (ast.tokencode == TBaseType.rrw_end) {
                                waitingEnds[nestedProcedures]--;
                                if (waitingEnds[nestedProcedures] == 0) {
                                    if (nestedProcedures == 0) {
                                        procedure_status[nestedProcedures] = stored_procedure_status.bodyend;
                                    } else {
                                        nestedProcedures--;
                                        procedure_status[nestedProcedures] = stored_procedure_status.is_as;
                                    }
                                }
                            } else if ((waitingEnds[nestedProcedures] == 0)
                                    && (ast.tokentype == ETokenType.ttslash)
                                    && (ast.tokencode == TBaseType.sqlpluscmd)) {
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);

                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                appendToken(gcurrentsqlstatement, ast);
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            }
                            break;
                        case bodyend:
                            if ((ast.tokentype == ETokenType.ttslash) && (ast.tokencode == TBaseType.sqlpluscmd)) {
                                // TPlsqlStatementParse(asqlstatement).TerminatorToken := ast;
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);

                                //make / a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                appendToken(gcurrentsqlstatement, ast);
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.tokentype == ETokenType.ttperiod) && (sourcetokenlist.returnaftercurtoken(false)) && (sourcetokenlist.returnbeforecurtoken(false))) {
                                // single dot at a seperate line
                                ast.tokenstatus = ETokenStatus.tsignorebyyacc;
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);

                                //make ttperiod a sqlplus cmd
                                gcurrentsqlstatement = new TSqlplusCmdStatement(vendor);
                                appendToken(gcurrentsqlstatement, ast);
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_package, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_procedure, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_function, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_create, 1) != null)
                                    && ((ast.searchToken(TBaseType.rrw_package, 4) != null) || (ast.searchToken(TBaseType.rrw_package, 5) != null))
                                    && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_create, 1) != null)
                                    && ((ast.searchToken(TBaseType.rrw_procedure, 4) != null)
                                            || (ast.searchToken(TBaseType.rrw_function, 4) != null)
                                            || (ast.searchToken(TBaseType.rrw_view, 4) != null)
                                            || (ast.searchToken(TBaseType.rrw_oracle_synonym, 4) != null)
                                            || (ast.searchToken(TBaseType.rrw_trigger, 4) != null))
                                    && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_create, 1) != null) && (ast.searchToken(TBaseType.rrw_library, 4) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_alter, 1) != null) && (ast.searchToken(TBaseType.rrw_trigger, 2) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_select, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_call, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_commit, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_declare, 1) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_grant, 1) != null)
                                    && (ast.searchToken(TBaseType.rrw_execute, 2) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else if ((ast.searchToken(TBaseType.rrw_alter, 1) != null)
                                    && (ast.searchToken(TBaseType.rrw_table, 2) != null) && (!endBySlashOnly)) {
                                appendToken(gcurrentsqlstatement, ast);
                                gst = EFindSqlStateType.stnormal;
                                onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, false, builder);
                            } else {
                                appendToken(gcurrentsqlstatement, ast);
                            }
                            break;
                        case end:
                            break;
                        default:
                            break;
                    }

                    if (ast.tokencode == TBaseType.sqlpluscmd) {
                        int m = flexer.getkeywordvalue(ast.getAstext());
                        if (m != 0) {
                            ast.tokencode = m;
                        } else if (ast.tokentype == ETokenType.ttslash) {
                            ast.tokencode = '/';
                        } else {
                            ast.tokencode = TBaseType.ident;
                        }
                    }

                    final int wrapped_keyword_max_pos = 20;
                    if ((ast.tokencode == TBaseType.rrw_wrapped)
                            && (ast.posinlist - gcurrentsqlstatement.sourcetokenlist.get(0).posinlist < wrapped_keyword_max_pos)) {
                        if (gcurrentsqlstatement instanceof gudusoft.gsqlparser.stmt.TCommonStoredProcedureSqlStatement) {
                            ((gudusoft.gsqlparser.stmt.TCommonStoredProcedureSqlStatement) gcurrentsqlstatement).setWrapped(true);
                        }

                        if (gcurrentsqlstatement instanceof gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage) {
                            if (ast.prevSolidToken() != null) {
                                ((gudusoft.gsqlparser.stmt.oracle.TPlsqlCreatePackage) gcurrentsqlstatement)
                                        .setPackageName(fparser.getNf().createObjectNameWithPart(ast.prevSolidToken()));
                            }
                        }
                    }

                    break;
                } //ststoredprocedure

            } //switch
        }//for

        //last statement
        if ((gcurrentsqlstatement != null) &&
                ((gst == EFindSqlStateType.stsqlplus) || (gst == EFindSqlStateType.stsql) || (gst == EFindSqlStateType.ststoredprocedure) ||
                        (gst == EFindSqlStateType.sterror))) {
            onRawStatementComplete(this.parserContext, gcurrentsqlstatement, this.fparser, this.fplsqlparser, this.sqlstatements, true, builder);
        }

        // Populate builder with results
        builder.sqlStatements(this.sqlstatements);
        builder.syntaxErrors(syntaxErrors instanceof ArrayList ?
                (ArrayList<TSyntaxError>) syntaxErrors : new ArrayList<>(syntaxErrors));
        builder.errorCode(syntaxErrors.isEmpty() ? 0 : syntaxErrors.size());
        builder.errorMessage(syntaxErrors.isEmpty() ? "" :
                String.format("Raw extraction completed with %d error(s)", syntaxErrors.size()));
    }

    /**
     * Handle token transformations during raw statement extraction.
     * <p>
     * This performs Oracle-specific keyword disambiguation that must happen
     * before statement boundary detection. Examples:
     * <ul>
     *   <li>RETURN after WHERE → treat as identifier</li>
     *   <li>VALUE after BY → mark as value_after_by</li>
     *   <li>NEW → treat as identifier or constructor based on context</li>
     *   <li>And many more Oracle-specific cases</li>
     * </ul>
     *
     * @param ast current token being processed
     */
    private void performRawStatementTokenTransformations(TSourceToken ast) {
        // This method contains the keyword transformation logic from dooraclegetrawsqlstatements
        // It's been extracted to keep the main method more readable

        if (ast.tokencode == TBaseType.rrw_return) {
            TSourceToken stMatch = ast.searchToken(TBaseType.rrw_where, 1);
            if (stMatch != null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_value_oracle) {
            TSourceToken stBy = ast.searchToken(TBaseType.rrw_by, -1);
            if (stBy != null) {
                ast.tokencode = TBaseType.rrw_value_after_by;
            }
        } else if (ast.tokencode == TBaseType.rrw_new_oracle) {
            TSourceToken stRightParen = ast.searchToken(')', -1);
            if (stRightParen != null) {
                ast.tokencode = TBaseType.ident;
            }
            TSourceToken stDot = ast.searchToken('.', 1);
            if (stDot != null) {
                ast.tokencode = TBaseType.ident;
            }

            TSourceToken stNext = ast.searchTokenAfterObjectName();
            stDot = ast.searchToken('.', 1);
            if ((stDot == null) && (stNext != null) && (stNext.tokencode == '(')) {
                ast.tokencode = TBaseType.rrw_oracle_new_constructor;
            }
        } else if (ast.tokencode == TBaseType.rrw_chr_oracle) {
            TSourceToken stLeftParen = ast.searchToken('(', 1);
            if (stLeftParen == null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_log_oracle) {
            TSourceToken stNext = ast.searchToken(TBaseType.rrw_errors_oracle, 1);
            TSourceToken stPrev = ast.searchToken(TBaseType.rrw_view, -1);
            if (stPrev == null) {
                stPrev = ast.searchToken(TBaseType.rrw_oracle_supplemental, -1);
            }
            if ((stNext == null) && (stPrev == null)) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_delete) {
            TSourceToken stPrev = ast.searchToken('.', -1);
            if (stPrev != null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_partition) {
            TSourceToken stPrev = ast.searchToken(TBaseType.rrw_add, -1);
            if (stPrev != null) {
                stPrev.tokencode = TBaseType.rrw_add_p;
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_column) {
            TSourceToken stPrev = ast.searchToken(TBaseType.rrw_oracle_modify, -1);
            if (stPrev != null) {
                ast.tokencode = TBaseType.rrw_oracle_column_after_modify;
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_apply) {
            TSourceToken stPrev = ast.searchToken(TBaseType.rrw_outer, -1);
            if (stPrev != null) {
                stPrev.tokencode = TBaseType.ORACLE_OUTER2;
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_subpartition) {
            TSourceToken stNext = ast.searchToken("(", 2);
            if (stNext != null) {
                TSourceToken st1 = ast.nextSolidToken();
                if (st1.toString().equalsIgnoreCase("template")) {
                    // don't change, keep as RW_SUBPARTITION
                } else {
                    ast.tokencode = TBaseType.rrw_oracle_subpartition_tablesample;
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_primary) {
            TSourceToken stNext = ast.searchToken("key", 1);
            if (stNext == null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_offset) {
            TSourceToken stNext = ast.searchToken(TBaseType.rrw_oracle_row, 2);
            if (stNext == null) {
                stNext = ast.searchToken(TBaseType.rrw_oracle_rows, 2);
            }
            if (stNext == null) {
                stNext = searchRowAfterOffsetExpression(ast);
            }
            if (stNext != null) {
                ast.tokencode = TBaseType.rrw_oracle_offset_row;
            }
        } else if (ast.tokencode == TBaseType.rrw_translate) {
            TSourceToken stNext = ast.searchToken("(", 2);
            if (stNext == null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_constraint) {
            TSourceToken stNext = ast.nextSolidToken();
            if (stNext == null) {
                ast.tokencode = TBaseType.ident;
            } else {
                if (stNext.tokencode != TBaseType.ident) {
                    ast.tokencode = TBaseType.ident;
                }
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_without) {
            TSourceToken stNext = ast.searchToken(TBaseType.rrw_oracle_count, 1);
            if (stNext != null) {
                ast.tokencode = TBaseType.rrw_oracle_without_before_count;
            }
        } else if (ast.tokencode == TBaseType.rrw_bulk) {
            TSourceToken stNext = ast.searchToken(TBaseType.rrw_oracle_collect, 1);
            if (stNext == null) {
                ast.tokencode = TBaseType.ident;
            }
        } else if (ast.tokencode == TBaseType.rrw_oracle_model) {
            TSourceToken stNext = ast.nextSolidToken();
            if (stNext != null) {
                switch (stNext.toString().toUpperCase()) {
                    case "RETURN":
                    case "REFERENCE":
                    case "IGNORE":
                    case "KEEP":
                    case "UNIQUE":
                    case "PARTITION":
                    case "DIMENSION":
                    case "MEASURES":
                    case "RULES":
                        ast.tokencode = TBaseType.rrw_oracle_model_in_model_clause;
                        break;
                    default:
                        ;
                }
            }
        }
    }

    /**
     * Look for the ROW/ROWS keyword terminating an OFFSET clause whose value is
     * a multi-token arithmetic expression, e.g. <code>OFFSET 1 * 10 ROWS</code>
     * (Mantis 4614). The single-token case is handled by the caller via
     * {@link TSourceToken#searchToken(int, int)}; this scan only accepts a
     * strictly alternating operand/operator token run (with balanced
     * parentheses), so a bare <code>OFFSET</code> used as a column name is not
     * rewritten.
     *
     * @param offsetToken the OFFSET keyword token
     * @return the terminating ROW/ROWS token, or null if the following tokens
     *         do not form an arithmetic expression ended by ROW/ROWS
     */
    private TSourceToken searchRowAfterOffsetExpression(TSourceToken offsetToken) {
        // generous cap: only bounds runaway scans on token runs that never hit
        // ROW/ROWS; any realistic offset expression is far shorter (a tight cap
        // would reject valid long expressions before the grammar sees them)
        final int maxTokens = 512;
        TSourceToken t = offsetToken.nextSolidToken();
        int parenDepth = 0;
        boolean expectOperand = true;
        int steps = 0;
        while (t != null && steps++ < maxTokens) {
            String text = t.toString();
            if (expectOperand) {
                if (text.equals("(")) {
                    parenDepth++;
                } else if (text.equals("+") || text.equals("-")) {
                    // unary sign, keep expecting an operand
                } else if (t.tokencode == TBaseType.iconst
                        || t.tokencode == TBaseType.fconst
                        || t.tokencode == TBaseType.ident
                        || text.startsWith(":")) {
                    expectOperand = false;
                } else {
                    return null;
                }
            } else {
                if (parenDepth == 0
                        && (t.tokencode == TBaseType.rrw_oracle_row || t.tokencode == TBaseType.rrw_oracle_rows)) {
                    return t;
                }
                if (text.equals(")")) {
                    if (--parenDepth < 0) {
                        return null;
                    }
                } else if (text.equals("+") || text.equals("-") || text.equals("*") || text.equals("/")) {
                    expectOperand = true;
                } else {
                    return null;
                }
            }
            t = t.nextSolidToken();
        }
        return null;
    }

    private void appendToken(TCustomSqlStatement statement, TSourceToken token) {
        if (statement == null || token == null) {
            return;
        }
        token.stmt = statement;
        statement.sourcetokenlist.add(token);
    }

    // ========== Error Handling and Recovery ==========

    /**
     * Find all syntax errors in PL/SQL statements recursively.
     * Extracted from TGSqlParser.findAllSyntaxErrorsInPlsql().
     */
    private void findAllSyntaxErrorsInPlsql(TCustomSqlStatement psql) {
        if (psql.getErrorCount() > 0) {
            copyErrorsFromStatement(psql);
        }

        for (int k = 0; k < psql.getStatements().size(); k++) {
            findAllSyntaxErrorsInPlsql(psql.getStatements().get(k));
        }
    }

    /**
     * Handle error recovery for CREATE TABLE/INDEX statements.
     * Oracle allows table properties that may not be fully parsed.
     * This method marks unparseable properties as SQL*Plus commands to skip them.
     *
     * <p>Extracted from TGSqlParser.doparse() lines 16916-16971
     */
    private void handleCreateTableErrorRecovery(TCustomSqlStatement stmt) {
        if (((stmt.sqlstatementtype == ESqlStatementType.sstcreatetable) ||
             (stmt.sqlstatementtype == ESqlStatementType.sstcreateindex)) &&
            (!TBaseType.c_createTableStrictParsing)) {

            // Find the closing parenthesis of table definition
            int nested = 0;
            boolean isIgnore = false, isFoundIgnoreToken = false;
            TSourceToken firstIgnoreToken = null;

            for (int k = 0; k < stmt.sourcetokenlist.size(); k++) {
                TSourceToken st = stmt.sourcetokenlist.get(k);

                if (isIgnore) {
                    if (st.issolidtoken() && (st.tokencode != ';')) {
                        isFoundIgnoreToken = true;
                        if (firstIgnoreToken == null) {
                            firstIgnoreToken = st;
                        }
                    }
                    if (st.tokencode != ';') {
                        st.tokencode = TBaseType.sqlpluscmd;
                    }
                    continue;
                }

                if (st.tokencode == (int) ')') {
                    nested--;
                    if (nested == 0) {
                        // Check if next token is "AS ( SELECT"
                        boolean isSelect = false;
                        TSourceToken st1 = st.searchToken(TBaseType.rrw_as, 1);
                        if (st1 != null) {
                            TSourceToken st2 = st.searchToken((int) '(', 2);
                            if (st2 != null) {
                                TSourceToken st3 = st.searchToken(TBaseType.rrw_select, 3);
                                isSelect = (st3 != null);
                            }
                        }
                        if (!isSelect) isIgnore = true;
                    }
                }

                if ((st.tokencode == (int) '(') || (st.tokencode == TBaseType.left_parenthesis_2)) {
                    nested++;
                }
            }

            // Verify it's a valid Oracle table property
            if ((firstIgnoreToken != null) &&
                (!TBaseType.searchOracleTablePros(firstIgnoreToken.toString()))) {
                // Not a valid property, keep the error
                isFoundIgnoreToken = false;
            }

            // Retry parsing if we found ignoreable properties
            if (isFoundIgnoreToken) {
                stmt.clearError();
                stmt.parsestatement(null, false);
            }
        }
    }

    /**
     * Copy syntax errors from a statement to our error list.
     * Extracted from TGSqlParser.copyerrormsg().
     */

    @Override
    public String toString() {
        return "OracleSqlParser{vendor=" + vendor + "}";
    }

    // ========== Main Oracle Tokenization ==========
    // Core tokenization logic extracted from TGSqlParser.dooraclesqltexttotokenlist()

    /**
     * Perform Oracle-specific tokenization with SQL*Plus command detection.
     * <p>
     * This method implements Oracle's complex tokenization rules including:
     * <ul>
     *   <li>SQL*Plus command detection (SPOOL, SET, START, etc.)</li>
     *   <li>Forward slash disambiguation (division vs PL/SQL delimiter)</li>
     *   <li>Oracle-specific keyword transformations (INNER, TYPE, FULL, etc.)</li>
     *   <li>Context-dependent token code modifications</li>
     * </ul>
     *
     * <p><b>State Machine:</b> Uses 5 boolean flags to track tokenization state:
     * <ul>
     *   <li>{@code insqlpluscmd} - Currently inside SQL*Plus command</li>
     *   <li>{@code isvalidplace} - Valid place to start SQL*Plus command</li>
     *   <li>{@code waitingreturnforfloatdiv} - Slash seen, waiting for newline</li>
     *   <li>{@code waitingreturnforsemicolon} - Semicolon seen, waiting for newline</li>
     *   <li>{@code continuesqlplusatnewline} - SQL*Plus command continues to next line</li>
     * </ul>
     *
     * <p><b>Extracted from:</b> TGSqlParser.dooraclesqltexttotokenlist() (lines 3931-4298)
     *
     * @throws RuntimeException if tokenization fails
     */
    private void dooraclesqltexttotokenlist() {
        // Initialize state machine for SQL*Plus command detection
        insqlpluscmd = false;
        javaBraceNesting = 0;
        isvalidplace = true;
        waitingreturnforfloatdiv = false;
        waitingreturnforsemicolon = false;
        continuesqlplusatnewline = false;

        ESqlPlusCmd currentCmdType = ESqlPlusCmd.spcUnknown;

        TSourceToken lct = null, prevst = null;

        TSourceToken asourcetoken, lcprevst;
        int yychar;

        asourcetoken = getanewsourcetoken();
        if (asourcetoken == null) return;
        yychar = asourcetoken.tokencode;

        while (yychar > 0) {
            sourcetokenlist.add(asourcetoken);

            switch (yychar) {
                case TBaseType.cmtdoublehyphen:
                case TBaseType.cmtslashstar:
                case TBaseType.lexspace: {
                    if (insqlpluscmd) {
                        asourcetoken.insqlpluscmd = true;
                    }
                    break;
                }

                case TBaseType.lexnewline: {
                    if (insqlpluscmd) {
                        insqlpluscmd = false;
                        isvalidplace = true;

                        if (continuesqlplusatnewline) {
                            insqlpluscmd = true;
                            isvalidplace = false;
                            asourcetoken.insqlpluscmd = true;
                        }

                        if (!insqlpluscmd) {
                            currentCmdType = ESqlPlusCmd.spcUnknown;
                        }
                    }

                    if (waitingreturnforsemicolon) {
                        isvalidplace = true;
                    }

                    if (waitingreturnforfloatdiv) {
                        isvalidplace = true;
                        lct.tokencode = TBaseType.sqlpluscmd;
                        if (lct.tokentype != ETokenType.ttslash) {
                            lct.tokentype = ETokenType.ttsqlpluscmd;
                        }
                    }

                    if (countLines(asourcetoken.toString()) > 1) {
                        // There is a line after select, so spool is the right place to start a sqlplus command
                        isvalidplace = true;
                    }

                    flexer.insqlpluscmd = insqlpluscmd;
                    break;
                }

                default: {
                    // Solid token
                    // Save semicolon flag before clearing: slash after semicolon on
                    // the same line (e.g. "END; /") should be a SQL*Plus delimiter,
                    // not division.
                    boolean prevWasSemicolon = waitingreturnforsemicolon;
                    continuesqlplusatnewline = false;
                    waitingreturnforsemicolon = false;
                    waitingreturnforfloatdiv = false;

                    if (insqlpluscmd) {
                        asourcetoken.insqlpluscmd = true;
                        if (asourcetoken.toString().equalsIgnoreCase("-")) {
                            continuesqlplusatnewline = true;
                        }
                    } else {
                        if (asourcetoken.tokentype == ETokenType.ttsemicolon) {
                            waitingreturnforsemicolon = true;
                        }

                        if ((asourcetoken.tokentype == ETokenType.ttslash)
                                && (isvalidplace || prevWasSemicolon || (isValidPlaceForDivToSqlplusCmd(sourcetokenlist, asourcetoken.posinlist)))) {
                            lct = asourcetoken;
                            waitingreturnforfloatdiv = true;
                        }

                        currentCmdType = TSqlplusCmdStatement.searchCmd(asourcetoken.toString(), asourcetoken.nextToken());
                        if (currentCmdType != ESqlPlusCmd.spcUnknown) {
                            if (isvalidplace) {
                                TSourceToken lnbreak = null;
                                boolean aRealSqlplusCmd = true;
                                if (sourcetokenlist.curpos > 0) {
                                    lnbreak = sourcetokenlist.get(sourcetokenlist.curpos - 1);
                                    aRealSqlplusCmd = !spaceAtTheEndOfReturnToken(lnbreak.toString());
                                }

                                if (aRealSqlplusCmd) {
                                    asourcetoken.prevTokenCode = asourcetoken.tokencode;
                                    asourcetoken.tokencode = TBaseType.sqlpluscmd;
                                    if (asourcetoken.tokentype != ETokenType.ttslash) {
                                        asourcetoken.tokentype = ETokenType.ttsqlpluscmd;
                                    }
                                    insqlpluscmd = true;
                                    flexer.insqlpluscmd = insqlpluscmd;
                                }
                            } else if ((asourcetoken.tokencode == TBaseType.rrw_connect) && (sourcetokenlist.returnbeforecurtoken(true))) {
                                asourcetoken.tokencode = TBaseType.sqlpluscmd;
                                if (asourcetoken.tokentype != ETokenType.ttslash) {
                                    asourcetoken.tokentype = ETokenType.ttsqlpluscmd;
                                }
                                insqlpluscmd = true;
                                flexer.insqlpluscmd = insqlpluscmd;
                            } else if (sourcetokenlist.returnbeforecurtoken(true)) {
                                TSourceToken lnbreak = sourcetokenlist.get(sourcetokenlist.curpos - 1);

                                if ((countLines(lnbreak.toString()) > 1) && (!spaceAtTheEndOfReturnToken(lnbreak.toString()))) {
                                    asourcetoken.tokencode = TBaseType.sqlpluscmd;
                                    if (asourcetoken.tokentype != ETokenType.ttslash) {
                                        asourcetoken.tokentype = ETokenType.ttsqlpluscmd;
                                    }
                                    insqlpluscmd = true;
                                    flexer.insqlpluscmd = insqlpluscmd;
                                }
                            }
                        }
                    }

                    isvalidplace = false;

                    // Oracle-specific keyword handling (inline to match legacy behavior)
                    if (prevst != null) {
                        if (prevst.tokencode == TBaseType.rrw_inner) {
                            if (asourcetoken.tokencode != flexer.getkeywordvalue("JOIN")) {
                                prevst.tokencode = TBaseType.ident;
                            }
                        } else if ((prevst.tokencode == TBaseType.rrw_not)
                                && (asourcetoken.tokencode == flexer.getkeywordvalue("DEFERRABLE"))) {
                            prevst.tokencode = flexer.getkeywordvalue("NOT_DEFERRABLE");
                        }
                    }

                    if (asourcetoken.tokencode == TBaseType.rrw_inner) {
                        prevst = asourcetoken;
                    } else if (asourcetoken.tokencode == TBaseType.rrw_not) {
                        prevst = asourcetoken;
                    } else {
                        prevst = null;
                    }

                    // Oracle keyword transformations that rely on prev token state
                    if ((asourcetoken.tokencode == flexer.getkeywordvalue("DIRECT_LOAD"))
                            || (asourcetoken.tokencode == flexer.getkeywordvalue("ALL"))) {
                        lcprevst = getprevsolidtoken(asourcetoken);
                        if (lcprevst != null) {
                            if (lcprevst.tokencode == TBaseType.rrw_for)
                                lcprevst.tokencode = TBaseType.rw_for1;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_dense_rank) {
                        TSourceToken stKeep = asourcetoken.searchToken(TBaseType.rrw_keep, -2);
                        if (stKeep != null) {
                            stKeep.tokencode = TBaseType.rrw_keep_before_dense_rank;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_full) {
                        TSourceToken stMatch = asourcetoken.searchToken(TBaseType.rrw_match, -1);
                        if (stMatch != null) {
                            asourcetoken.tokencode = TBaseType.RW_FULL2;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_join) {
                        TSourceToken stFull = asourcetoken.searchToken(TBaseType.rrw_full, -1);
                        if (stFull != null) {
                            stFull.tokencode = TBaseType.RW_FULL2;
                        } else {
                            TSourceToken stNatural = asourcetoken.searchToken(TBaseType.rrw_natural, -4);
                            if (stNatural != null) {
                                stNatural.tokencode = TBaseType.RW_NATURAL2;
                            }
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_outer) {
                        TSourceToken stFull = asourcetoken.searchToken(TBaseType.rrw_full, -1);
                        if (stFull != null) {
                            stFull.tokencode = TBaseType.RW_FULL2;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_is) {
                        TSourceToken stType = asourcetoken.searchToken(TBaseType.rrw_type, -2);
                        if (stType != null) {
                            stType.tokencode = TBaseType.rrw_type2;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_as) {
                        TSourceToken stType = asourcetoken.searchToken(TBaseType.rrw_type, -2);
                        if (stType != null) {
                            stType.tokencode = TBaseType.rrw_type2;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_oid) {
                        TSourceToken stType = asourcetoken.searchToken(TBaseType.rrw_type, -2);
                        if (stType != null) {
                            stType.tokencode = TBaseType.rrw_type2;
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_type) {
                        TSourceToken stPrev;
                        stPrev = asourcetoken.searchToken(TBaseType.rrw_drop, -1);
                        if (stPrev != null) {
                            asourcetoken.tokencode = TBaseType.rrw_type2;
                        }
                        if (asourcetoken.tokencode == TBaseType.rrw_type) {
                            stPrev = asourcetoken.searchToken(TBaseType.rrw_of, -1);
                            if (stPrev != null) {
                                asourcetoken.tokencode = TBaseType.rrw_type2;
                            }
                        }
                        if (asourcetoken.tokencode == TBaseType.rrw_type) {
                            stPrev = asourcetoken.searchToken(TBaseType.rrw_create, -1);
                            if (stPrev != null) {
                                asourcetoken.tokencode = TBaseType.rrw_type2;
                            }
                        }
                        if (asourcetoken.tokencode == TBaseType.rrw_type) {
                            stPrev = asourcetoken.searchToken(TBaseType.rrw_replace, -1);
                            if (stPrev != null) {
                                asourcetoken.tokencode = TBaseType.rrw_type2;
                            }
                        }
                        if (asourcetoken.tokencode == TBaseType.rrw_type) {
                            stPrev = asourcetoken.searchToken('%', -1);
                            if (stPrev != null) {
                                asourcetoken.tokencode = TBaseType.rrw_type2;
                            }
                        }
                    } else if ((asourcetoken.tokencode == TBaseType.rrw_by) || (asourcetoken.tokencode == TBaseType.rrw_to)) {
                        lcprevst = getprevsolidtoken(asourcetoken);
                        if (lcprevst != null) {
                            if ((lcprevst.tokencode == TBaseType.sqlpluscmd) && (lcprevst.toString().equalsIgnoreCase("connect"))) {
                                lcprevst.tokencode = TBaseType.rrw_connect;
                                lcprevst.tokentype = ETokenType.ttkeyword;
                                flexer.insqlpluscmd = false;

                                continuesqlplusatnewline = false;
                                waitingreturnforsemicolon = false;
                                waitingreturnforfloatdiv = false;
                                isvalidplace = false;
                                insqlpluscmd = false;
                            }
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_with) {
                        lcprevst = getprevsolidtoken(asourcetoken);
                        if (lcprevst != null) {
                            if ((lcprevst.tokencode == TBaseType.sqlpluscmd) && (lcprevst.toString().equalsIgnoreCase("start"))) {
                                lcprevst.tokencode = TBaseType.rrw_start;
                                lcprevst.tokentype = ETokenType.ttkeyword;
                                flexer.insqlpluscmd = false;

                                continuesqlplusatnewline = false;
                                waitingreturnforsemicolon = false;
                                waitingreturnforfloatdiv = false;
                                isvalidplace = false;
                                insqlpluscmd = false;
                            }
                        }
                    } else if (asourcetoken.tokencode == TBaseType.rrw_set) {
                        lcprevst = getprevsolidtoken(asourcetoken);
                        if (lcprevst != null) {
                            if (lcprevst.getAstext().equalsIgnoreCase("a")) {
                                TSourceToken lcpp = getprevsolidtoken(lcprevst);
                                if (lcpp != null) {
                                    if ((lcpp.tokencode == TBaseType.rrw_not) || (lcpp.tokencode == TBaseType.rrw_is)) {
                                        lcprevst.tokencode = TBaseType.rrw_oracle_a_in_aset;
                                        asourcetoken.tokencode = TBaseType.rrw_oracle_set_in_aset;
                                    }
                                }
                            }
                        }
                    }

                    break;
                }
            }

            // Get next token
            asourcetoken = getanewsourcetoken();
            if (asourcetoken != null) {
                yychar = asourcetoken.tokencode;

                // Handle special case: dot after SQL*Plus commands
                if ((asourcetoken.tokencode == '.') && (getprevsolidtoken(asourcetoken) != null)
                        && ((currentCmdType == ESqlPlusCmd.spcAppend)
                        || (currentCmdType == ESqlPlusCmd.spcChange) || (currentCmdType == ESqlPlusCmd.spcInput)
                        || (currentCmdType == ESqlPlusCmd.spcList) || (currentCmdType == ESqlPlusCmd.spcRun))) {
                    // a.ent_rp_usr_id is not a real sqlplus command
                    TSourceToken lcprevst2 = getprevsolidtoken(asourcetoken);
                    lcprevst2.insqlpluscmd = false;
                    if (lcprevst2.prevTokenCode != 0) {
                        lcprevst2.tokencode = lcprevst2.prevTokenCode;
                    } else {
                        lcprevst2.tokencode = TBaseType.ident;
                    }

                    flexer.insqlpluscmd = false;
                    continuesqlplusatnewline = false;
                    waitingreturnforsemicolon = false;
                    waitingreturnforfloatdiv = false;
                    isvalidplace = false;
                    insqlpluscmd = false;
                }
            } else {
                yychar = 0;

                if (waitingreturnforfloatdiv) {
                    // / at the end of line treat as sqlplus command
                    lct.tokencode = TBaseType.sqlpluscmd;
                    if (lct.tokentype != ETokenType.ttslash) {
                        lct.tokentype = ETokenType.ttsqlpluscmd;
                    }
                }
            }

            if ((yychar == 0) && (prevst != null)) {
                if (prevst.tokencode == TBaseType.rrw_inner) {
                    prevst.tokencode = TBaseType.ident;
                }
            }
        }
    }

    // ========== Helper Methods for Tokenization ==========
    // These methods support Oracle-specific tokenization logic

    /**
     * Count number of newlines in a string.
     *
     * @param s string to analyze
     * @return number of line breaks (LF or CR)
     */
    private int countLines(String s) {
        int pos = 0, lf = 0, cr = 0;

        while (pos < s.length()) {
            if (s.charAt(pos) == '\r') {
                cr++;
                pos++;
                continue;
            }
            if (s.charAt(pos) == '\n') {
                lf++;
                pos++;
                continue;
            }

            if (s.charAt(pos) == ' ') {
                pos++;
                continue;
            }
            break;
        }

        if (lf >= cr) return lf;
        else return cr;
    }

    /**
     * Check if return token ends with space or tab.
     *
     * @param s token text
     * @return true if ends with space/tab
     */
    private boolean spaceAtTheEndOfReturnToken(String s) {
        if (s == null) return false;
        if (s.length() == 0) return false;

        return ((s.charAt(s.length() - 1) == ' ') || (s.charAt(s.length() - 1) == '\t'));
    }

    /**
     * Determine if forward slash should be treated as SQL*Plus command delimiter.
     * <p>
     * Oracle uses '/' as both division operator and SQL*Plus block delimiter.
     * This method disambiguates by checking if the '/' appears at the beginning
     * of a line (after a return token without trailing whitespace).
     *
     * @param pstlist token list
     * @param pPos position of '/' token
     * @return true if '/' should be SQL*Plus command
     */
    private boolean isValidPlaceForDivToSqlplusCmd(TSourceTokenList pstlist, int pPos) {
        boolean ret = false;

        if ((pPos <= 0) || (pPos > pstlist.size() - 1)) return ret;

        // Token directly before div must be ttreturn without space appending it
        gudusoft.gsqlparser.TSourceToken lcst = pstlist.get(pPos - 1);
        if (lcst.tokentype != gudusoft.gsqlparser.ETokenType.ttreturn) {
            return ret;
        }

        if (!(lcst.getAstext().charAt(lcst.getAstext().length() - 1) == ' ')) {
            ret = true;
        }

        return ret;
    }

    /**
     * Get previous non-whitespace token.
     *
     * @param ptoken current token
     * @return previous solid token, or null
     */
    private gudusoft.gsqlparser.TSourceToken getprevsolidtoken(gudusoft.gsqlparser.TSourceToken ptoken) {
        gudusoft.gsqlparser.TSourceToken ret = null;
        TSourceTokenList lctokenlist = ptoken.container;

        if (lctokenlist != null) {
            if ((ptoken.posinlist > 0) && (lctokenlist.size() > ptoken.posinlist - 1)) {
                if (!(
                        (lctokenlist.get(ptoken.posinlist - 1).tokentype == gudusoft.gsqlparser.ETokenType.ttwhitespace)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == gudusoft.gsqlparser.ETokenType.ttreturn)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == gudusoft.gsqlparser.ETokenType.ttsimplecomment)
                        || (lctokenlist.get(ptoken.posinlist - 1).tokentype == gudusoft.gsqlparser.ETokenType.ttbracketedcomment)
                )) {
                    ret = lctokenlist.get(ptoken.posinlist - 1);
                } else {
                    ret = lctokenlist.nextsolidtoken(ptoken.posinlist - 1, -1, false);
                }
            }
        }
        return ret;
    }
}