create or replace function test_sm1(pdate in date := sysdate) return varchar2 sql_macro
as
begin
return 'select object_name from user_objects where created >= trunc(pdate)';
end;
/
create or replace function test_sm1_with(pdate in date := sysdate) return varchar2 sql_macro as
begin
return 'with temp as (select object_name from user_objects where created >= trunc(pdate)) select * from temp';
end;
/
create or replace function test_sm1_with2(pdate in date := sysdate) return varchar2 sql_macro as
begin
return 'with temp as (select object_name from user_objects where created >= trunc(test_sm1_with2.pdate)) select * from temp';
end;
/
select * from test_sm1();
-- OK
select * from test_sm1_with();
-- ORA-00904: "PDATE": invalid identifier
select * from test_sm1_with2();
-- ORA-64629: table SQL macro can only appear in FROM clause of a SQL statement
It seems you can't use the WITH clause with SQL_MACRO - does anyone have any ideas why?
Regards,
PM