Function return sys_refcursor call from sql with specific columns
function, oracle10g, plsql, sql, sys-refcursor
Solution
No, not with a `ref cursor` at all, and otherwise not without creating SQL types to cast the return into, like this example: http://dbaspot.com/oracle-server/9308-select-ref-cursor.html:
create or replace type myType as object (
a int,
b varchar2(10)
)
/
create or replace type myTable as table of myType;
/
create or replace function f1 return myTable as
l_data myTable := myTable();
begin
for i in 1 .. 5 loop
l_data.extend;
l_data(i) := myType(i, 'Row #'||i );
end loop;
return l_data;
end;
/
select * from TABLE ( cast( f1() as myTable ) );
---------- ----------
1 Row #1
2 Row #2
3 Row #3
4 Row #4
5 Row #5
From the last post on that thread:
the way you already knew about is the only one to use the REF CURSOR in a select statement.
Problem
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; ```