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:
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.
