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