This may find little silly, but I would like to know whether this is possible.
I have a function which return sys_refcursor
CREATE OR REPLACE FUNCTION get_employee_details(p_emp_no IN EMP.EMPNO%TYPE)
RETURN SYS_REFCURSOR
AS
o_cursor SYS_REFCURSOR;
BEGIN
OPEN o_cursor FOR
SELECT EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM emp
WHERE EMPNO = p_emp_no;
RETURN o_cursor;
-- exception part
END;
/
and I could get the results using
select get_employee_details('7369') from dual;
Is it possible to get the result from the above function by specifying column name? E.g. If I would want to get ename or salary, how could I specify in the sql statement without using a plsql block? Something like
select get_employee_details('7369') <specific column> from dual;
For that purpose, you might want to take a look at PIPELINED
functions. You will have to declare explicit type at PL/SQL level though. That part will set the output column name:
CREATE OR REPLACE TYPE my_rec AS OBJECT (
c CHAR,
n NUMBER(1)
);
CREATE OR REPLACE TYPE my_tbl AS TABLE OF my_rec;
Now, the great advantage is you can not only "rename" your columns, but modify the records from your cursor on the fly too. For ex:
CREATE OR REPLACE FUNCTION my_fct
RETURN my_tbl PIPELINED
AS
-- dummy data - use your own cursor here
CURSOR data IS
SELECT 'a' as A, 1 AS B FROM DUAL UNION
SELECT 'b', 2 FROM DUAL UNION
SELECT 'c', 3 FROM DUAL UNION
SELECT 'd', 4 FROM DUAL;
BEGIN
FOR the_row IN data
LOOP
PIPE ROW(my_rec(the_row.a, the_row.b*2));
-- ^^
-- Change data on the fly
END LOOP;
END
Usage:
SELECT * FROM TABLE(my_fct())
-- ^^^^^^^^^^^^^^^
-- Use this "virtual" table like any
-- other table. Supporting `WHERE` clause
-- or any other SELECT clause you want
Producing:
C N
a 2
b 4
c 6
d 8