HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Reading PIPELINED function conntaining DML in a query, ORA-14551 - small cheat
ORACLE STUFFS
06-Apr-2018
When a PIPELINED function contains DML operaions it is usually impossible flat select from it. It works well in INSERT-SELECT operation but hardly in a plain select.

select * from TABLE(L0_SB_PKO.D$UCTRANS('L1_L0SB_ACCTRN'));

select * from TABLE(L0_SB_PKO.D$UCTRANS('L1_L0SB_ACCTRN')) * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "L0_SB_PKO.D$UCTRANS", line 22 ORA-06512: at "L0_SB_PKO.D$UCTRANS", line 37 ORA-06512: at line 1

There is a solution for the problem. It is a bit tricky, but you can cheat the restriction by using the MATERIALIZE hint in a query factory such as:

With l1 as(select /*+materialize*/ * from TABLE(L0_SB_PKO.D$UCTRANS('L1_L0SB_ACCTRN')))         
Select * from l1;

It works!

just be careful about transactional consistency.

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky