Weakly typed ref cursors are truly "dynamic": when a function executes that returns such a ref cursor, the "shape" of the result can be different every time.
SQL table macros are not "dynamic": when a table macro is called in a SELECT statement, it executes during the hard parse phase, after which the "shape" of the result is the same during multiple executions. This is true even when the macro is "polymorphic", since the parameters that might change the shape are identifiers (known at parse time) and not variables (known at execution time).
So, to use SQL table macros we would have to call them from the code that opens the ref cursor! Here is a generic JSONIZE function:
SQL> create or replace function jsonize(
2 p_table in dbms_tf.table_t
3 )
4 return varchar2 sql_macro is
5 begin
6 return 'select json_serialize(json_arrayagg(json_object(*)) pretty) as json_result from p_table';
7 end jsonize;
8 /
To use it, we have to pass the original query as a named subquery, not as a string or a ref cursor.
SQL> var n number;
SQL> exec :n := 20;
PL/SQL procedure successfully completed.
SQL> with data as (
2 select ename, empno, deptno, dname from emp join dept using(deptno)
3 where deptno = :n
4 )
5 select * from jsonize(data);
JSON_RESULT
-------------------------
[
{
"ENAME" : "JONES",
"EMPNO" : 7566,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "SCOTT",
"EMPNO" : 7788,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "FORD",
"EMPNO" : 7902,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "SMITH",
"EMPNO" : 7369,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "ADAMS",
"EMPNO" : 7876,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
}
]
Here is an simplified example of code that opens a ref cursor. The first time, the original result is returned. The second time, JSON is returned. The only difference is in line 7.
SQL> var rc refcursor;
SQL> begin
2 open :rc for '
3 with data as (
4 select ename, empno, deptno, dname from emp join dept using(deptno)
5 where deptno = :depto
6 )
7 select * from data' using :n;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> print :rc;
ENAME EMPNO DEPTNO DNAME
---------- ---------- ---------- --------------
JONES 7566 20 RESEARCH
SCOTT 7788 20 RESEARCH
FORD 7902 20 RESEARCH
SMITH 7369 20 RESEARCH
ADAMS 7876 20 RESEARCH
SQL> begin
2 open :rc for '
3 with data as (
4 select ename, empno, deptno, dname from emp join dept using(deptno)
5 where deptno = :depto
6 )
7 select * from jsonize(data)' using :n;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> print :rc;
JSON_RESULT
----------------------------
[
{
"ENAME" : "JONES",
"EMPNO" : 7566,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "SCOTT",
"EMPNO" : 7788,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "FORD",
"EMPNO" : 7902,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "SMITH",
"EMPNO" : 7369,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
},
{
"ENAME" : "ADAMS",
"EMPNO" : 7876,
"DEPTNO" : 20,
"DNAME" : "RESEARCH"
}
]But wait! Once we realize that we have to switch to JSON in the code that opens the ref cursor,
we don't need a SQL macro any more! This produces the same result:
var rc refcursor;
begin
open :rc for '
with data as (
select ename, empno, deptno, dname from emp join dept using(deptno)
where deptno = :depto
)
select json_serialize(json_arrayagg(json_object(*)) pretty) as json_result from data' using :n;
end;
/
print :rc;
Bottom line, I would look at adding a switch to the code that produces the ref cursor, so that it can optionally add the one-liner that produces JSON.
Best regards,
Stew