Function return sys_refcursor call from sql with specific columns

Question

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;

@NicholasKrasnov Idea behind this is I would create a function for getting employee details and when I would want to get only salary of an employee is it possible to specify fetch only salary instead of all columns? `select get_employee_details('7369') from dual` would return all the columns which is specified in the function select list of columns. — Jacob, Dec 03 '12 at 19:28

Sylvain Leroux · Answer 1 · 2014-09-19T17:16:36.153

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

@Polppan :D I wasn't even hoping you will take a look at that after such a long time! I'm glad you appreciate it. — Sylvain Leroux, Sep 19 '14 at 17:14
Thank you! Very helpful and very clearly showed. I don't use Oracle much and needed some good example for testing purposes. — mdob, Sep 11 '18 at 11:54

score 4 · Accepted Answer · answered Dec 03 '12 at 19:29

4

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.

answered Dec 03 '12 at 19:29

Didn't quite understand this? `the way you already knew about is the only one to use the REF CURSOR in a select statement.` – Jacob Dec 03 '12 at 19:32
1

It makes sense if you read the whole thread in the link ;) basically, `select get_employee_details('7369') from dual` is all you get for SQL. `ref cursors` are not really suposed to be used that way, they are for passing between subprograms (and I think in some cases for client libraries, but don't quote me on that). – Dec 03 '12 at 19:33
Ah I see :-) So ref cursors usage with sql statements has only limited options unless use in a plsql block. Thanks – Jacob Dec 03 '12 at 19:38

Function return sys_refcursor call from sql with specific columns

2 Answers2

Linked