Hello All,

I encounter an error trying to use a SQL MACRO (TABLE) in freesql.com, in both versions 26ai and 23ai.

The function top_n compiles successfully, but trying to call it fails:

create or replace
    function top_n (p_table DBMS_TF.TABLE_T, p_rows NUMBER)
           return varchar2 SQL_Macro 
    is
       l_sql varchar2(200) := 'select * from top_n.p_table fetch first top_n.p_rows rows only';
    begin
      dbms_output.put_line('sql='||l_sql);
      return l_sql;
    end;
/

Function TOP_N compiled

-- test in SQL

select * from top_n (scott.dept, 3)
/

ORA-00942: table or view "TOP_N"."P_TABLE" does not exist

The same example works ok in 19c

SQL> select * from top_n (scott.dept, 3)

DEPTNO DNAME        LOC        
------ ------------ ---------- 
10     ACCOUNTING   NEW YORK   
20     RESEARCH     DALLAS     
30     SALES        CHICAGO    

sql=select * from top_n.p_table fetch first top_n.p_rows rows only

3 rows selected. 

It looks like this is a database bug, as I also encounter the same problem in a different 26ai environment.

Thanks a lot in advance & Best Regards,
Iudith Mentzel

create or replace function get_tab ( tab dbms_tf.table_t ) return clob sql_macro as
begin
  return 'select * from random_stuff.tab ';
end;
/
select count(*) from get_tab ( hr.employees );

  COUNT(*)
----------
       107