0

I have a pipelined table function that work fine.

what I need now is to perform an update query inside this function:

create or replace FUNCTION test(A varchar2 )
 RETURN type_As PIPELINED  as row_type type_A;
Begin
....
update X set A=0 where B=1;
select type_A(...) 
  into   row_type 
  from   dual;
PIPE ROW(row_type);
  return ;
end;

When I run this query :

SELECT * from TABLE(test('123'))

I get this error:

ORA-14551:cannot perform a DML operation inside a query

So it's clear that a cannot add the update query here , so how to perform the update instead ?

Any help is appreciated.

user123
  • 387
  • 1
  • 4
  • 13
  • Possible duplicate of [Solution to "cannot perform a DML operation inside a query"?](https://stackoverflow.com/questions/8729236/solution-to-cannot-perform-a-dml-operation-inside-a-query) – Kaushik Nayak Feb 06 '18 at 17:33
  • @KaushikNayak No, this is not PIPELINED function. – JERKER Aug 06 '19 at 12:25

1 Answers1

-1

Try to add 

pragma autonomous_transaction;

to your function and use dynamic SQL for your update.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
I3rutt
  • 574
  • 4
  • 18
  • Using AUTONOMOUS_TRANSACTION means instant commit, and outer transactions that are rolled back will not roll back this "local" commit. So, it can have unpredicted consequenses in bigger scenarios. – JERKER Aug 06 '19 at 14:02