Tracing SQL Macro Expansion

I decided to take a look at what events Oracle provides to diagnose or troubleshoot SQL Macro Expansions.
For that, I took almost the same code that I used before: SQL Macro script

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

drop table t; drop table t1; drop table t2;   CREATE OR REPLACE FUNCTION sample(t DBMS_TF.Table_t, how_many number DEFAULT 5) RETURN VARCHAR2 SQL_MACRO AS BEGIN   RETURN q'[SELECT *               FROM t       WHERE rownum <= how_many]'; END sample; /   create table t as select n   from xmltable('1 to 10'   columns     n int path '.');   create table t1 as select n,        'val_'||to_char(n, 'fm00') val   from xmltable('1 to 10'   columns     n int path '.');   create table t2 as select 'val_'||to_char(n, 'fm00') s   from xmltable('1 to 10'   columns     n int path '.');   select * from t1; select * from t2;   select *   from sample(t1, 3);   select *   from sample(t1);   select *   from sample(t2);

A query failed when I was trying to supply the table T - which is the same that is used in the SQL macro's definition:

?

1 2 3 4 5 6

SQL> select *   2    from sample(t);   from sample(t)                * ERROR at line 2: ORA-32039: recursive WITH clause must have column alias list

The errorstack dump for ORA-32039 (level=3) did not provide much information about the underlying cause:

?

1 2 3 4 5

----- Error Stack Dump ----- <error barrier> at 0x7ffd69f7aaf0 placed dbkda.c@296 ORA-32039: recursive WITH clause must have column alias list ----- Current SQL Statement for this session (sql_id=d3ktrfsd4s0sr) ----- select * from sample(t)

SQL Trace was referring to the same statement:

?

1 2	PARSE ERROR #140320112869816:len=24 dep=0 uid=119 oct=3 lid=119 tim=1054524149653 err=32039 select * from sample(t)

It showed the following statement, though:

?

1 2 3 4 5 6 7 8

PARSING IN CURSOR #140320116348624 len=446 dep=1 uid=119 oct=47 lid=119 tim=1054472624364 hv=3547957408 ad='7cda6688' sqlid='9awjhv79rm250'   declare t0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0);   begin :macro_text := "SAMPLE"(t0); end;

While studying those qksptfSQM functions, namely: qksptfSQM_GetTxt and qksptfSQM_Template, I found that they are calling to the PTF_Comp UTS component (Polymorphic Table Functions Compilation (qksptf)):

?

1 2 3 4 5 6

0x000000000f75c87a <+4202>:  mov    $0x6000,%ecx 0x000000000f75c87f <+4207>:  mov    $0x205018b,%esi 0x000000000f75c884 <+4212>:  mov    $0x2,%edx 0x000000000f75c889 <+4217>:  mov    -0x200(%rbp),%r8 0x000000000f75c890 <+4224>:  mov    -0xcb58(%rbx),%rdi 0x000000000f75c897 <+4231>:  callq  0x485cef0 <dbgtCtrl_intEvalCtrlEvent>

Therefore, I enabled PTF_Comp tracing:

?

1	alter session set events 'trace[ptf_comp]' tracefile_identifier=ptf_comp;

Here is what I got in the trace file:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

qksptfSQM_GetTxt(): Anonymous Block ===================================   declare t0 DBMS_TF.Table_t := DBMS_TF.Table_t(column => :0);   begin :macro_text := "SAMPLE"(t0); end; qksptfSQM_GetTxt(): Macro Text ==============================   SELECT *               FROM t              WHERE rownum <= how_many qksptfSQM_Template(): Template Text ===================================   with  T as (select  /*+ INLINE */ * from "MY_USER"."T") select "SYS__$".* from (select 5 "HOW_MANY" from SYS.DUAL) "SAMPLE",      lateral(SELECT *               FROM t              WHERE rownum <= how_many) "SYS__$"

Obviously, the ORA-32039 error is absolutely legit in this case and the PTF_Comp tracing clearly shows where the issue is. It highlights one of the current limitations of that functionality in Oracle 19.6. Beside it demonstrates how literals (HOW_MANY in the example above) are passed through a subquery and joined to a lateral view - that approach has certain restrictions. Apparently there seem to be more restrictions than that according to $ORACLE_HOME/plsql/mesg/pcmus.msg:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

776, 0, "SQL macro can only return character return types" // INDEX: "SQL macro" // RELEASE: 20.1 // CAUSE: A SQL macro function returned a type that was not a character type like VARCHAR2. // ACTION:  Change the return type to one of the character types. 777, 0, "scalar SQL macro cannot have argument of type DBMS_TF.TABLE_T" // INDEX: "SQL macro" // RELEASE: 20.1 // CAUSE: DBMS_TF.TABLE_T argument was used for scalar macros. // ACTION:  Change the argument type to a type other than //          DBMS_TF.TABLE_T. 778, 0, "PARTITION BY, ORDER BY, CLUSTER BY, or PARALLEL_ENABLE are not allowed for SQL macros" // MANUAL: partition by, order by, cluster by, deterministic, authid or parallel_enable //         not allowed for SQL macros // INDEX: "SQL macro" // RELEASE: 20.1 // CAUSE: An attempt was made to use the PARTITION BY, ORDER BY, CLUSTER BY, //        DETERMINISTIC, AUTHID, or PARALLEL_ENABLE clause in a //        SQL macro. // ACTION:  Do not specify the PARTITION BY, CLUSTER BY, ORDER BY, DETERMINISTIC, AUTHID, or //          PARALLEL_ENABLE clause with a SQL macro. 779, 0, "Formal parameters of type TABLE cannot have a default value." // MANUAL: A default value cannot be specified for parameter of type TABLE. // INDEX:  "SQL macros" // RELEASE: 20.1 // CAUSE: A default value was specified for a formal parameter of type TABLE //        in a SQL macro declaration. // ACTION: Remove the default value from the parameter of type TABLE in //         the SQL macro declaration. // 780, 0, "Formal parameters of type COLUMNS may only have a NULL default value." // MANUAL:  Parameters of type COLUMNS can only have a NULL default value. // INDEX:  "SQL macros" // RELEASE: 20.1 // CAUSE:  A non-NULL default value was specified for a formal parameter of type COLUMNS //        in a polymorphic table function. // ACTION: Change the default value for the parameter of type COLUMNS to //         have a NULL default value in the polymorphic table function //         specification. // // 781, 0, "SQL macro cannot be a type method" // INDEX:  "SQL macros" // RELEASE: 20.1 // CAUSE: The SQL macro could not be declared as a method in a type specification. // ACTION: Use the SQL macro as a package function or a top level function. // 782, 0, "A package containing SQL macro cannot be an invoker's rights package." // INDEX:  "SQL macros" // RELEASE: 20.1 // CAUSE: A package could not be declared as AUTHID CURRENT_USER if it had a SQL //        macro function declared inside. // ACTION: Declare the package as AUTHID DEFINER if it contains one or more //         SQL macro functions.

Keep in mind that this functionality is not officially released and only a limited subset of it is available in 19.6: Bug 30324180 SQL Macro should be backported to 19.5. Hence, the final version of SQL Macro in Oracle 20 might be different from what we have now in Oracle 19.6.