How to use DML in a pipelined function (Doc ID 313597.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.1.0.3 and later
Information in this document applies to any platform.
***Checked for relevance on 23-Dec-2013***

Symptoms

 

Executing a DML statement within a pipelined function produces the following error:

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


The following example is executed within the SCOTT schema. In this example, all the employee numbers (EMPNO fields) passed in will be divided by 2, and returned.


Create the required table and type:

CREATE TABLE HALFEMPNO_TBL (EMPNO NUMBER);
CREATE OR REPLACE TYPE halfempno_type AS TABLE OF NUMBER;
/


Create the required function

create or replace function HalfEmpnoTblFunc(myrefcursor sys_refcursor) return halfempno_type
pipelined as
v_empno emp.empno%TYPE;
v_halfempno NUMBER;
begin
LOOP
FETCH myrefcursor INTO v_empno;
EXIT WHEN myrefcursor%NOTFOUND;
v_halfempno := trunc(v_empno/2);
INSERT INTO HALFEMPNO_TBL values (v_halfempno);
PIPE ROW (v_halfempno); --returns the number that represents half the empno....only a test case folks.
END LOOP;
RETURN;
end;
/


Execute the following code to recreate the error:


SQL> select halftbl.column_value EMPNO_HALF from table(HalfEmpnoTblFunc(cursor(select empno from emp))) halftbl;
 
select halftbl.column_value EMPNO_HALF from table(HalfEmpnoTblFunc(cursor(select empno from emp))) halftbl
          *
ERROR at line 2:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SCOTT.HALFEMPNOTBLFUNC", line 10

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms