Unanswered: ORA: 14551 : cannot perform a DML operation inside a query
I ve got an error while executing the below function. This function is constructed to return a table type data.
The issue is :
ORA: 14551 : cannot perform a DML operation inside a query (on the line 16 near the INSERT statement).
CREATE OR REPLACE FUNCTION funtest
TableName IN VARCHAR2
RETURN reRetTable0 PIPELINED
TYPE REFCURSOR IS REF CURSOR;
IF GetAll = 0 THEN
INSERT INTO funtest_TBL
WHERE AID < 6
OPEN CURSOR0 FOR SELECT * FROM funtest_TBL;
FETCH CURSOR0 INTO
EXIT WHEN CURSOR0%NOTFOUND;
where funtest_TBL is a global temp table :
CREATE GLOBAL TEMPORARY TABLE funtest_TBL(StatusID INT,AuditTableName VARCHAR2(50)) ON COMMIT PRESERVE ROWS
RetTable0 is TYPE object :
CREATE OR REPLACE TYPE RetTable0 AS OBJECT(StatusID INT,AuditTableName VARCHAR2(50));
I executed successfully the following combinations,
1. a simple function with an Insert stmt to a global temp table (funtest_TBL)in it
2. a simple function with a select into stmt from the global temp table (funtest_TBL)in it
What is the reason for the error thrown when i tried to execute the above function (funtest)?
Thats a third party source, I do not know exactly why the code was written that way. It has a function that returns a Table Type object. I would like to know how to call this function and collect the value returned?
I tried to call it from another procedure
1. using a 'select col1 from table(funtest(arg1, arg2)) where condition' query but it throwed the error (ORA-14551: cannot perform a DML operation inside a query).
2. created another table object of the same type returned by funtest() and assigned it to the function call. this ended up with a compilation error PLS-00653: aggregate/table functions are not allowed in PL/SQL scope