PL/SQL Function Returning Ref Cursor Example

Oracle 9i Introduced in-built Ref cursors sys_refcursor, Independent function/procedure can return data in terms of ref cursors. Application programmers can consume ref cursor data in terms of ResultSets. Note:Unlike Static cursors Ref cursors are used for dynamic queies.

Creating a Function which returns Ref cursors.

CREATE OR REPLACE function reff1
return sys_refcursor
is
v_t sys_refcursor;
begin
open v_t for select * from pearson.orders;
return v_t;
end;

 Calling a Function Returning Ref Cursor.

select reff1() from dual;

OUTPUT:

ORDERID CUSTO EMPLOYEEID ORDERDATE REQUIREDD SHIPPEDDA    SHIPVIA    FREIGHT SHIPNAME
------- ----- ---------- --------- --------- --------- ---------- ---------- --------------------
  11051 LAMAI          7 27-APR-98 25-MAY-98                    3       2.79 La maison d'Asie
  11052 HANAR          3 27-APR-98 25-MAY-98 01-MAY-98          1      67.26 Hanari Carnes
  11053 PICCO          2 27-APR-98 25-MAY-98 29-APR-98          2      53.05 Piccolo und mehr
  11054 CACTU          8 28-APR-98 26-MAY-98                    1        .33 Cactus Comidas para
  11055 HILAA          7 28-APR-98 26-MAY-98 05-MAY-98          2     120.92 HILARION-Abastos
  11056 EASTC          8 28-APR-98 12-MAY-98 01-MAY-98          2     278.96 Eastern Connection
  11057 NORTS          3 29-APR-98 27-MAY-98 01-MAY-98          3       4.13 North/South
  11058 BLAUS          9 29-APR-98 27-MAY-98                    3      31.14 Blauer See Delikates
  11059 RICAR          2 29-APR-98 10-JUN-98                    2       85.8 Ricardo Adocicados
  11060 FRANS          2 30-APR-98 28-MAY-98 04-MAY-98          2      10.98 Franchi S.p.A.
  11061 GREAL          4 30-APR-98 11-JUN-98                    3      14.01 Great Lakes Food Mar

Tags:   Function Returning Ref Cursor Example,Sys_refcursor example, using Sys_refcursor in functions,

Creating a function which returns Ref cursor, Creating a Independent function which returns Ref cursor,Declaring Ref cursor, Calling a function Returning Ref Cursor.