2

We have a package SCHEMA.MYPACKAGE with a procedure SCHEMA.MYPACKAGE.GETDATA that calls a sql macro SCHEMA.SQLMACROFUNCTION that exists within the same schema but outside the package. The macro is called within a select statement and qualified by schema name even though it's within the same schema, like this:

SELECT col1,col2,col3
  FROM SCHEMA.SQLMACROFUNCTION(param1, param2, param3)
  ORDER BY col1 DESC;

The problem is that when anything invalidates the package (for example, adding a column to a table the package depends on) and the next procedure call tries to auto-compile the package as Oracle is supposed to do, the compile fails with

PLS-00201: identifier 'SQLMACROFUNCTION' must be declared

leaving the package in an invalid state until it's manually compiled with

ALTER PACKAGE SCHEMA.MYPACKAGE COMPILE;

which always succeeds.

Anyone have an explanation for this behavior? I considered permissions, but the package and the sql_macro function are in the same schema. I considered function name qualification, but it's already called with schemaname.objectname. Also, and I can't corroborate with evidence from Oracle documentation, but I read somewhere that functions (in this case a sql_macro) have to be executed in order to validate the code, but an auto-compile only parses the code, which might explain.

There are obvious workarounds to this issue (substituting the function code into the procedure in place of the function call resolves the auto-compile issue), but I'm curious to understand why it's happening and am interested in some documentation, if it's out there.

Thanks in advance.

Improve this question
3
  • I didn't know sql macros before I read your post. They were introduced in 19c Commented Jun 22, 2023 at 4:30
  • They were introduced in 21c. Macros for table expressions were backported to 19c. Scalar macros are not available in 19c. Commented Jun 22, 2023 at 6:28
  • SQL_Macros are new to me as of a few days ago as well. There is an important distinction between 19c and 21c with the sql_macro_clause. 19c is just ...SQL_MACRO IS... In 21c, you can provide the type (defaults to table if omitted) as ...SQL_MACRO(TABLE) IS... 19c will not take a type and will throw compile errors if you try to use it in the 21c manner as here: stackoverflow.com/questions/71114808/… Commented Jun 22, 2023 at 15:29

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.