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
/