SQLcl: MCP Server & SQL Prompt

A recent bug that caused incorrect package deployment when MERGE INTO statements were present has been fixed. However, we are still encountering a problem: comments at the end of the package body are lost whenever MERGE INTO is used.

This is problematic because:

• It’s confusing when the deployed package differs from the original source.
• We want to rely on SQLcl’s project export to detect database drift across multiple production systems.

Here is a minimal working example:

CREATE TABLE demo_tab (
    demo_id          RAW(16),
    demo_name        VARCHAR2(10 CHAR) NOT NULL,
    demo_description VARCHAR2(30 CHAR) NOT NULL,
    CONSTRAINT demo_tab_pk PRIMARY KEY (demo_id),
    CONSTRAINT demo_tab_uk1 UNIQUE (demo_name)
);

Package with MERGE INTO

CREATE OR REPLACE PACKAGE pkg_demo_merge_mwe IS
    PROCEDURE demo_proc;
END pkg_demo_merge_mwe;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo_merge_mwe IS
    PROCEDURE demo_proc IS
    BEGIN
        MERGE INTO demo_tab t
        USING (SELECT 'Name1' AS demo_name, 'Desc1' AS demo_description FROM dual) s
        ON (t.demo_name = s.demo_name)
        WHEN MATCHED THEN
            UPDATE SET t.demo_description = s.demo_description
        WHEN NOT MATCHED THEN
            INSERT (demo_id, demo_name, demo_description)
            VALUES (SYS_GUID(), s.demo_name, s.demo_description);
    END demo_proc;
END pkg_demo_merge_mwe; -- comment at end of package body
/

Package without MERGE INTO

CREATE OR REPLACE PACKAGE pkg_demo_nomerge_mwe IS
    PROCEDURE demo_proc;
END pkg_demo_nomerge_mwe;
/

CREATE OR REPLACE PACKAGE BODY pkg_demo_nomerge_mwe IS
    PROCEDURE demo_proc IS
    BEGIN
        BEGIN
            INSERT INTO demo_tab (demo_id, demo_name, demo_description)
            VALUES (SYS_GUID(), 'Name1', 'Desc1');
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                UPDATE demo_tab
                SET demo_description = 'Desc1'
                WHERE demo_name = 'Name1';
        END;
    END demo_proc; 
END pkg_demo_nomerge_mwe; -- comment at end of package body
/

Observed Behavior

• Without MERGE INTO, the comment at the end of the package body is correctly deployed.
• With MERGE INTO, the comment at the end is lost.

@ashley-chen-oracle you filed the previous bug related to MERGE INTO. Could you also report this issue about lost comments? It’s still causing inconsistencies during deployment.