Beautifier cannot parse proc with local subprogram

[CoffeeK1d]

In the SQL Window the parser does not like [c]log_bulk_exceptions[/c] and [c]mark_processed[/c] procedures in the declaration. This doesn't seem to be an issue for the Program Window.

CREATE OR REPLACE PROCEDURE JGUST.SP_local_sub_proc (
    p_acad_yr               IN NUMBER
   ,p_lea_id                IN NUMBER
   ,p_resource_name         IN VARCHAR2
   ,p_max_change_event_id   IN NUMBER
   ,p_commit_limit          IN NUMBER
   ,p_update_user           IN VARCHAR2)
IS
    CURSOR Cr_resource (p_changetype IN VARCHAR)
    IS
SELECT 1 AS CHANGE_EVENT_ID
      ,NULL        AS RESOURCE_JSON
      ,2025        AS ACAD_YR
      ,'000000'    AS LEA_ID
      ,'Delete'    AS CHANGE_TYPE
      ,1           AS ID
      ,'000000001' AS CAMPUS_ID
      ,'UPSERT'    AS NEW_CHANGE_TYPE
      ,SYSDATE     AS END_DT
      ,1 AS RN
  FROM DUAL
 WHERE p_changetype = 'Invalid';

    TYPE resource_tbl IS TABLE OF Cr_resource%ROWTYPE
        INDEX BY BINARY_INTEGER;

    v_resource_rec   resource_tbl;
    bulk_errors      EXCEPTION;
    PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
    PRAGMA AUTONOMOUS_TRANSACTION;

    PROCEDURE log_bulk_exceptions
    IS
    BEGIN
        FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
        LOOP
            SP_LOG_BULK_EXCEPTION (
                v_resource_rec (SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX).CHANGE_EVENT_ID
               ,   SQLERRM (-SQL%BULK_EXCEPTIONS (indx).ERROR_CODE)
                || ' at '
                || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
        END LOOP;
    END;

    PROCEDURE mark_processed
    IS
    BEGIN
        FORALL i IN 1 .. v_resource_rec.COUNT SAVE EXCEPTIONS
            UPDATE JGUST.T_CHANGE_EVENTS
               SET PROCESSED_FLAG = 1, CHANGED_TIMESTAMP = CURRENT_TIMESTAMP
             WHERE ID = v_resource_rec (i).CHANGE_EVENT_ID;
    END;
BEGIN
    NULL;
END;
/

Edit: typo, not an issue for the Program Window

 

[Marco Kalter]

It seems that the SQL Window automatically selects part of this code when the PL/SQL Beautifier is invoked. If you explicitly select the complete text in the SQL Window (Ctrl-A), then it works correctly.

We'll fix it.