sqlfunctionplsqloracle10gsys-refcursor

Function return sys_refcursor call from sql with specific columns


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;

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.