function returning record or table
dear fellows i have a package say
CREATE OR REPLACE PACKAGE P AS
TYPE emp_t IS RECORD(
empno emp.empno%TYPE,
job emp.job%TYPE,
hiredate emp.hiredate%TYPE,
deptno dept.deptno%TYPE);
TYPE t_empdept
IS TABLE OF emp_t
INDEX BY BINARY_INTEGER;
FUNCTION TST(VEMPNO IN NUMBER)
RETURN EMP_t;
END ;
and package body
CREATE OR REPLACE PACKAGE BODY P
AS
FUNCTION TST(VEMPNO IN NUMBER)
RETURN EMP_T
IS
VRET EMP_T;
BEGIN
SELECT EMPNO,JOB,HIREDATE,DEPTNO
INTO VRET.EMPNO,VRET.JOB,VRET.HIREDATE,VRET.DEPTNO
FROM EMP
WHERE EMP.EMPNO=VEMPNO;
RETURN VRET;
END;
END;
/
this compiles but i m unable to select it like
sql> select tst(7788) from emp;
is there any way i can select it like ordinary select returning
result to sql prompt.
thanks
/