hi,

i have a question regarding external table and macro's
we have an external table and and there are a couple of complex sql functions case, regexp, subst.. that i would 'hide' in a view, so we can reuse those expression in PLSQL.
With a view a cannot pass the location, so i thought a macro can help me out, but till now, no luck

select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

create table test_table (
    a       varchar2(10),
    b       varchar2(10),
    c       varchar2(10),
)
 organization external ( 
  type oracle_loader
  default directory dir_temp 
  access parameters ( 
    fields csv without embedded 
    missing field values are null
    reject rows with all null fields
  )  
  location ( 'test.csv' )
);

-- this works
select *  
  from test_table
 external modify (
  location('test.csv')
);

create or replace function external_table_macro_test
(
  p_tab       in dbms_tf.table_t
 ,p_filename in varchar2
) return clob sql_macro is
begin
  return q'{select a, case a when 'test' then b end as just_an_example from p_tab external modify(location('p_filename'))}';
end external_table_macro_test;
/

-- this returns an error, ORA-30663: An EXTERNAL MODIFY clause can only be specified for an external or hybrid-partitioned table.
select * from external_table_macro_test(p_tab => test_table, p_filename => 'test.csv' );

even without the filename parameter it fails, is this combination even possible?

thx