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

    Unanswered: Execution of a PipeLined function fails..

    I tried to execute the below enclosed Pipelined function(it is generated by our application) by calling the function from a procedure. The following error is thrown :

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

    Could any one let me know what is wrong with the Procedure(spark)/Function(Fun_Ret_Table)
    The PL/SQL code for Pipelined function and the stored procedure is given below,

    PipeLined function FUN_RET_TABLE :
    Code:
    BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE rereturnTable20';
    EXECUTE IMMEDIATE 'DROP TYPE returnTable20';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    CREATE OR REPLACE TYPE returnTable20 AS OBJECT(StatusID int);
    /
    CREATE OR REPLACE TYPE rereturnTable20 AS TABLE OF returnTable20;
    /
    BEGIN 
    EXECUTE IMMEDIATE 'DROP TABLE Fun_Ret_Table_TBL CASCADE CONSTRAINTS';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    /
    CREATE GLOBAL TEMPORARY TABLE Fun_Ret_Table_TBL(StatusID int) ON COMMIT PRESERVE ROWS
    /
    
    CREATE OR REPLACE FUNCTION Fun_Ret_Table
    (
    	TableName                  IN      	VARCHAR2 DEFAULT NULL
    )
    RETURN rereturnTable20 PIPELINED 
    IS
    returnTable200  returnTable20:=returnTable20(NULL);
    
     TYPE REFCURSOR IS REF CURSOR;
     CURSOR0 REFCURSOR;
    
    RID                        int;
    		
    	CURSOR  testCur
    	 IS
             SELECT DISTINCT rID
             FROM  testTable 
             WHERE	 TName  = Fun_Ret_Table.TableName;  
    
    BEGIN  
    	OPEN testCur; 
    
            FETCH  testCur INTO RID; 
            << LABEL2 >>
    
    	WHILE  (  testCur%FOUND  )
    	LOOP  
    	BEGIN 
    		
    		INSERT INTO  Fun_Ret_Table_TBL   
    				( StatusID )  
    		SELECT SID
    		 FROM
    		(SELECT SID
    		FROM  testTable 
    		WHERE	 RID  = Fun_Ret_Table.RID
    		ORDER BY SID DESC 
    		)WHERE	 ROWNUM  < 2; 
    
    FETCH  testCur INTO RID;
    	OPEN CURSOR0 FOR SELECT * FROM Fun_Ret_Table_TBL;
    
    	END;
    	
    	END LOOP; 
    	CLOSE testCur; 
    
    		LOOP
    		FETCH CURSOR0 INTO 
    		returnTable200.StatusID;
    		EXIT WHEN CURSOR0%NOTFOUND;
    		PIPE ROW(returnTable200);
    	END LOOP;
    	CLOSE CURSOR0;
    
    RETURN;
    
    
    END;
    /
    Procedure call to FUN_RET_TABLE
    Code:
    CREATE OR REPLACE PROCEDURE spark
    AS
    	varint1                    int;
    	varint2                    int;  
    	BEGIN 
    		
    		BEGIN
    
    			FOR REC IN ( SELECT StatusID FROM  TABLE(Fun_Ret_Table('OnlyOne')) 
    		 )
    			LOOP
    				spark.varint1 := REC.StatusID;
    			END LOOP;
    		END; 
    
    		DBMS_OUTPUT.PUT_LINE(  CAST(varint1 AS VARCHAR2));
    		
    	END;
    /

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Try making it an AUTONOMOUS_TRANSACTION function. NOTE this doesnt always work.

    Alan

  3. #3
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the reply alan. I used 'Pragma Autonomous_Transaction' in the declaration part of the pipelined function. While calling the function from the procedure got the following error..

    ORA-22905 : Cannot access rows from a nested table item.

    BTW, is there any way to collect the table returned by a function other than PIPELINED method

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Why are you inserting into a temp table before selecting from it, why dont you just merge the cursor and the inner select statement together and then return the records from there?

    Alan

Posting Permissions

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