Can't compile a Oracle db package with a SQL Macro and it caller in the same package

249 Views Asked by At

I'm getting this error, when try to compile a package with a sql_macro function and it caller in the same package.

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

If I delete the call, pkg can be compiled. After compilation I can call the sql_macro function with the same statement from "outside" of the pkg. Creating the same function as a seperate func and calling it in the pkg, also working.

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;
/
0

There are 0 best solutions below