同一包内含SQL Macro函数及其调用者时Oracle包编译失败

编译包含SQL Macro函数及其内部调用者的Oracle数据库包时，触发以下报错：

ORA-62565: The SQL Macro method failed with error(s). ORA-04067: not executed, package body does not exist

现象细节：

• 删除包内对SQL Macro的调用语句后，包可正常编译
• 包编译完成后，从外部用相同语句调用该SQL Macro函数可正常执行
• 将SQL Macro函数单独创建为独立对象后，在包内调用也可正常工作

复现代码 #

CREATE OR REPLACE PACKAGE PA_MACRO_TEST
AS
    FUNCTION FNC_CALL
        RETURN NUMBER;

    FUNCTION FNC_SQL_MACRO
        RETURN VARCHAR2 SQL_MACRO;
END PA_MACRO_TEST;
/

CREATE OR REPLACE PACKAGE BODY PA_MACRO_TEST
IS
   
    FUNCTION FNC_CALL
        RETURN NUMBER
    IS
       first_col VARCHAR2(50);
       sec_col NUMBER;
    BEGIN
   --SELECT 'test',1 into first_col,sec_col from dual;     
   SELECT * into first_col,sec_col FROM PA_MACRO_TEST.FNC_SQL_MACRO();
        RETURN 1;
    END FNC_CALL;

    FUNCTION FNC_SQL_MACRO
        RETURN VARCHAR2
    IS
       
    BEGIN
        RETURN q'[SELECT 'test', 1 from dual]';
    END FNC_SQL_MACRO;
END PA_MACRO_TEST;
/

这是Oracle编译SQL Macro时的依赖解析机制导致的问题：

• 编译包体时，Oracle会在编译阶段解析包内调用的SQL Macro，此时包体尚未完全创建完成，Oracle无法找到该Macro函数的完整定义，从而触发"包体不存在"的错误。
• 包编译完成后，外部调用时包体已存在，SQL Macro可正常解析；而将Macro函数独立出来时，编译包体时该对象已存在，也能正常解析。

方案1：使用动态SQL绕过编译时解析 #

修改包内的调用语句为动态SQL，这样编译时不会解析SQL Macro，而是在运行时再执行解析：

CREATE OR REPLACE PACKAGE BODY PA_MACRO_TEST
IS
    FUNCTION FNC_CALL
        RETURN NUMBER
    IS
       first_col VARCHAR2(50);
       sec_col NUMBER;
    BEGIN
       EXECUTE IMMEDIATE 'SELECT * FROM PA_MACRO_TEST.FNC_SQL_MACRO()' INTO first_col, sec_col;
        RETURN 1;
    END FNC_CALL;

    FUNCTION FNC_SQL_MACRO
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN q'[SELECT 'test', 1 from dual]';
    END FNC_SQL_MACRO;
END PA_MACRO_TEST;
/

方案2：将SQL Macro独立为单独对象 #

将SQL Macro函数创建为独立的函数，而非包内成员，然后在包内调用：

-- 先创建独立的SQL Macro函数
CREATE OR REPLACE FUNCTION FNC_SQL_MACRO
    RETURN VARCHAR2 SQL_MACRO
IS
BEGIN
    RETURN q'[SELECT 'test', 1 from dual]';
END FNC_SQL_MACRO;
/

-- 再创建包
CREATE OR REPLACE PACKAGE PA_MACRO_TEST
AS
    FUNCTION FNC_CALL
        RETURN NUMBER;
END PA_MACRO_TEST;
/

CREATE OR REPLACE PACKAGE BODY PA_MACRO_TEST
IS
    FUNCTION FNC_CALL
        RETURN NUMBER
    IS
       first_col VARCHAR2(50);
       sec_col NUMBER;
    BEGIN
       SELECT * into first_col,sec_col FROM FNC_SQL_MACRO();
        RETURN 1;
    END FNC_CALL;
END PA_MACRO_TEST;
/

内容的提问来源于stack exchange，提问作者SebastianOS