Function returning recordset

[orakumar]

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 ?

[tabreaz]

Try this:

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
/