Hello All,

I have a PL/SQL function that takes two INTERVAL DAY TO SECOND parameters and returns a value of the same data type.

It works correctly as a regular PL/SQL function.

I try to convert it to an equivalent SQL MACRO, both as a TABLE (in 19c and 23ai ) and as a SCALAR macro
(in 23ai ) and they compile without error, but when trying to use them from a SELECT statement they both fail.

I cannot perform tracing for trying to debug the problem.

Also, using DBMS_UTILITY.EXPAND_SQL_TEXT cannot be used in this case, because it works only for a TABLE SQL Macro
and only when the SQL runs successfully.

As an enhancement suggestion, it would be extremely useful to have the ability to see the transformed SQL query
( after the macro replacement ) in all cases, TABLE or SCALAR, successful or not, either by using
DBMS_UTILITY.EXPAND_SQL_TEXT or by any other method.


I created a script for the test case, under the link below:

https://livesql.oracle.com/ords/livesql/s/czlvwpdwtohm5u5l6njsmw4h9

and, although it appears as "Publicly shareable", the submission page DOES NOT allow me to enter it in the URL box !



Thanks a lot in advance for your help !
Iudith Mentzel
( 37 years of using Oracle, not just 35 as the dropbox on this submit page allows :) )

create or replace function f ( 
  period interval day to second 
) return clob sql_macro as
begin
  return ' select period ';
end;
/

declare 
  p interval day to second := interval '5' minute;
begin 
  for rws in (
    select * from f ( p )
  ) loop 
    dbms_output.put_line ( rws.period );
  end loop;
end;
/
+000000000 00:05:00.000000000

declare
  l_clob clob;
begin
  dbms_utility.expand_sql_text (
    input_sql_text  => q'! select junk sql !',
    output_sql_text => l_clob  );
  dbms_output.put_line(l_clob);
end;
/
ORA-24256: EXPAND_SQL_TEXT failed with ORA-00904: "JUNK": invalid identifier