5

What I'd like to do is return results from function, so function could be used in following way: select * from stuff(1)

Simplified function example:

FUNCTION stuff(p_var number) RETURN SYS_REFCURSOR
IS

CURSOR cur(cp_var number) IS
    SELECT * FROM dual ;

BEGIN
    OPEN cur(p_var);
    RETURN cur;
END stuff;

But this this doesn't compile: Error: PLS-00382: expression is of wrong type

Is it possible to return cursor/results from function, which is defined this way?

msturek
  • 541
  • 6
  • 17
iljau
  • 2,151
  • 3
  • 22
  • 45
  • You cannot do this using a standalone function, you'll need to create a table type and a table function. Take a look at https://docs.oracle.com/cd/E11882_01/appdev.112/e10765/pipe_paral_tbl.htm#ADDCI4680 – msturek Nov 20 '16 at 18:32
  • 1
    `cur` is a `cursor` not a `sys_refcursor`. You need declare the variable as `cur sys_refcursor` and then use `open cur for select * from dual` –  Nov 20 '16 at 22:20
  • @a_horse_with_no_name Oracle version: 12c Standard Edition, 12.1.0.2.0 – iljau Nov 20 '16 at 22:22
  • Possible duplicate of [Function return sys\_refcursor call from sql with specific columns](http://stackoverflow.com/questions/13690110/function-return-sys-refcursor-call-from-sql-with-specific-columns) – Prashant Mishra Nov 21 '16 at 08:00

3 Answers3

5

Your function is returning a sys_refcursor and in your code you are returning a simple cursor. This makes the code wrong. If you want to return a ref_cursor from a function you can use as below:

create or replace function stuff (p_var number)
   return sys_refcursor
is
   rf_cur   sys_refcursor;
begin
   open rf_cur for
      select *
        from employee
       where employee_id = p_var;

   return rf_cur;
end stuff;

Execution:

select stuff(1) from dual;
XING
  • 9,608
  • 4
  • 22
  • 38
  • 2
    Note that this doesn't return rows and columns as may be expected. So you'll need to unpack returned result. – iljau Nov 21 '16 at 19:18
  • Yes ofcourse, your result set is a collection and then you need to unpack to see each column. – XING Nov 22 '16 at 07:35
1

you need to create pipe lined table function, here is an article which will help you.

https://oracle-base.com/articles/misc/pipelined-table-functions

TheName
  • 697
  • 1
  • 7
  • 18
0

I think you cannot call ref cursors directly in FROM clause however you may try following..

SELECT * FROM TABLE(function_name());

Function should be with Return type as PLSQL Nested table CREATE NESTED TABLE TYPE of CURSOR%ROWTYPE;

Raj
  • 1