[SOLVED] How can I see the Expanded TABLE SQL Macro Statement

How can I see the Expanded TABLE SQL Macro Statement

I created a TABLE SQL Macro in Oracle 21c database that performs an aggregation from a general table with parameters for the grouping columns and the columns that should be summarized.

Using the Oracle documentation I finally manage it to work as expected (see the code below), but what I missed and what would make the code development much easier is the possibility to see the expanded SQL text that is the result of the TABLE SQL Macro.

Note that simple looking in GV$SQL does not help as here is the original SQL statement as it was called using the SQL Macro.

To see the expanded text would be even more helpful in case of an error in the SQL Macro, because in this case you get e.g. this exception

ORA-64626: invalid SQL text returned from SQL macro: 
ORA-00936: missing expression

but it is not easy to react without knowing the SQL text that caused this problem.

So the question is how can I see the expanded SQL statement resulting of SQL Macro call.

I know, that this could be managed with some tracing (e.g. 10053) but this will require an administrator access which I do not have.

Here is the SQL Macro example

create or replace function tab_grp(sum_cols  DBMS_TF.COLUMNS_T, t DBMS_TF.Table_t, grp_cols  DBMS_TF.COLUMNS_T)  
  return varchar2 SQL_Macro(TABLE) as
v_sum_list varchar2(4000);
v_grp_list varchar2(4000);
begin
  -- concat the SUM column
 FOR i IN 1 .. sum_cols.count() LOOP
      v_sum_list := v_sum_list || case when i > 1 then ',' end || 'SUM('|| sum_cols(i)|| ') '|| sum_cols(i);
  END LOOP;
  FOR i IN 1 .. grp_cols.count() LOOP
      v_grp_list := v_grp_list || case when i > 1 then ',' end || grp_cols(i);
  END LOOP;
  return 'select '  || v_grp_list || ', ' || v_sum_list ||' , count(*) cnt from t group by '|| v_grp_list ;
end;
/

and the call of it (that can be found as SQL_TEXT in GV$SQL)

 select * from tab_grp (columns(value), tab, columns(customer_id, product_id));

What I want to see it the statement that is really executed, something like this:

select customer_id, product_id, sum(value) value, count(*) cnt
from tab
group by customer_id, product_id

Solution

The procedure DBMS_UTILITY.EXPAND_SQL_TEXT will show the SQL statement generated after applying SQL macros.

Below is the output from Marmite's LiveSQL demo. The output from the expand procedure is always a bit cryptic, but after running the output through a code beautifier, this is the result:

SELECT "A1"."CUSTOMER_ID" "CUSTOMER_ID",
       "A1"."PRODUCT_ID"  "PRODUCT_ID",
       "A1"."VALUE"       "VALUE",
       "A1"."CNT"         "CNT"
  FROM (SELECT "A3"."CUSTOMER_ID" "CUSTOMER_ID",
               "A3"."PRODUCT_ID"  "PRODUCT_ID",
               "A3"."VALUE"       "VALUE",
               "A3"."CNT"         "CNT"
          FROM (SELECT "A4"."CUSTOMER_ID" "CUSTOMER_ID",
                       "A4"."PRODUCT_ID" "PRODUCT_ID",
                       SUM("A4"."VALUE") "VALUE",
                       COUNT(*) "CNT"
                  FROM (SELECT "A2"."CUSTOMER_ID" "CUSTOMER_ID",
                               "A2"."PRODUCT_ID"  "PRODUCT_ID",
                               "A2"."VALUE"       "VALUE"
                          FROM "SQL_NTOIZUWOYDICAPPIYTUZKZBNM"."TAB" "A2") "A4"
                 GROUP BY "A4"."CUSTOMER_ID", "A4"."PRODUCT_ID") "A3") "A1"