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
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:
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:
----- 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:
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:
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)):
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:
alter session set events 'trace[ptf_comp]' tracefile_identifier=ptf_comp;
Here is what I got in the trace file:
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:
SQL Macro related errors from $ORACLE_HOME/plsql/mesg/pcmus.msg
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.