Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE OR REPLACE
   FUNCTION my_query_pipe return varchar2 SQL_MACRO is
   BEGIN
      RETURN q'{
          select 1 from dual
      }';
  END;
  /
select * from my_query_pipe()


1


1


with data as (
     select * from my_query_pipe()
    )
    select * from data;
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported

with data as (
      select /*+ WITH_PLSQL */ * from my_query_pipe()
    )
    select * /*+ WITH_PLSQL */  from data;
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported