Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Question Unanswered: ORA: 14551 : cannot perform a DML operation inside a query

    Hi,

    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).

    Code:
    CREATE OR REPLACE FUNCTION funtest
    (
    	TableName                  IN      	VARCHAR2
    )
    RETURN reRetTable0 PIPELINED 
    IS
    
    RetTable00  RetTable0:=RetTable0(NULL,NULL);
    
     TYPE REFCURSOR IS REF CURSOR;
     CURSOR0 REFCURSOR;
      
    BEGIN  
    	IF GetAll = 0 THEN 
    
    	INSERT INTO funtest_TBL
    	SELECT
    			 SID,
    			 AName
    	FROM  AStatus 
    	WHERE	 AID  < 6
    	;
    	OPEN CURSOR0 FOR SELECT * FROM funtest_TBL;
    
    	END IF; 
    		LOOP
    		FETCH CURSOR0 INTO 
    		RetTable00.StatusID,RetTable00.AuditTableName;
    		EXIT WHEN CURSOR0%NOTFOUND;
    		PIPE ROW(RetTable00);
    	END LOOP;
    	CLOSE CURSOR0;
    
    RETURN;
    
    
    END;
    
    
    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)?

    could anyone help me?

    regards,
    Sn

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Make your function an autonomous function however depending on what you do even that isnt guarenteed to work.

    Alan

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the reply Alan,
    But it is not working in Autonomous function too... :-(

    the error points to the 'INSERT into SELECT from' statement as before.

    could u pls let me know under what scenarios this error occurs? Coz, some of my simple functions with a DML query in them executed successfully while this alone fails.

    Regards,
    Sn

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One question, why use the GTT why not just open a cursor against your select statement from astatus?

    Alan

  5. #5
    Join Date
    Apr 2005
    Posts
    127
    Hi,

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •