Function returning recordset
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Function returning recordset

  1. #1
    Join Date
    May 2002
    Posts
    22

    Function returning recordset

    I created a function Get_emp_Id which returns emp_id in recordset . I want to use this function in SQL statement with where condition

    SELECT * From EMP where emp_id in (Get_emp_Id);

    But I got an error ORA-00932: inconsistent datatypes: expected - got CURSER

    Can Anyone please help me to call above function in where clause of select statement ?

  2. #2
    Join Date
    Sep 2005
    Posts
    278
    Try this:

    Code:
    CREATE OR REPLACE PACKAGE pkg_test
    IS
    TYPE typid IS RECORD(
    	id	NUMBER(6)
    );
    
    TYPE ids IS TABLE OF typid;
    
    FUNCTION get_empids RETURN ids PIPELINED;
    END;
    
    CREATE OR REPLACE PACKAGE BODY pkg_test
    IS
    FUNCTION get_empids RETURN ids PIPELINED
    IS
    	lids	ids;
    BEGIN
    	SELECT empid BULK COLLECT INTO lids
    	FROM emp
    	WHERE sal > 1500;
    	FOR i in 1..lids.count
    	LOOP
    		PIPE ROW (lids(i));
    	END LOOP;
    	RETURN;
    END;
    END;
    
    
    SELECT b.ename, b.sal 
    FROM emp a, TABLE(pkg_test.get_empids) b
    WHERE a.empno = b.id
    /

Posting Permissions

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


Click Here to Expand Forum to Full Width