Hi experts,
My db version is 19.14. I have a pipelined function running some complex query, and I would like to use this result set to update a table. Unfortunately the updated table is being referenced by the function, so I got this error:
ORA-04091: table XXX is mutating, trigger/function may not see it
Then I think mayb SQL Macro is a good solution for this use case. I created this test case:
create table t as select level as id, 'test'||level as col from dual connect by level<=10;
select * from t;
ID COL
---------- -------------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
7 test7
8 test8
9 test9
10 test10
create or replace function test_macro(
p_id in number
)
return varchar2 sql_macro
is
v_sql varchar2(4000);
begin
v_sql := 'with q as (select * from t where id = p_id)
select * from q';
return v_sql;
end;
/
And I got this error:
select * from test_macro(10)
*
ERROR at line 1:
ORA-00904: "P_ID": invalid identifier
Then I changed the code to qualify the parameter p_id:
create or replace function test_macro(
p_id in number
)
return varchar2 sql_macro
is
v_sql varchar2(4000);
begin
v_sql := 'with q as (select * from t where id = test_macro.p_id)
select * from q';
return v_sql;
end;
/
But I then got a new error:
select * from test_macro(10)
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'TEST_MACRO'
I would like to use subquery factoring and keep the parameter p_id as a place holder not a literal. Is there any workaround for this use case? Thank you!