There are number occasions, which i personally met when many of my juniors are looking for solution of using ref-cursor in SQL based on some certain requirement and they want to use it in SQL that can be embed into some JAVA based applications. I've looked into that and able to produce one such useful case which can handy (Though i'll prefer different approach than this.) when i require this kind of work.
satyaki>
satyaki>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create table t_emp
2 as
3 select empno,
4 ename,
5 mgr,
6 sal
7 from emp
8 where 1=2;
Table created.
Elapsed: 00:00:00.05
satyaki>
satyaki>set lin 80
satyaki>
satyaki>desc t_emp;
Name Null? Type
----------------------------------------- -------- ----------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(4)
SAL NUMBER(7,2)
satyaki>
satyaki>
satyaki>set lin 310
satyaki>
satyaki>select * from t_emp;
no rows selected
Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>create or replace type etype as object
2 (
3 empno number,
4 ename varchar2(10),
5 mgr number,
6 sal number
7 );
8 /
Type created.
Elapsed: 00:00:01.03
satyaki>
satyaki>create or replace type t_etype as table of etype;
2 /
Type created.
Elapsed: 00:00:00.02
satyaki>
satyaki>create or replace function get_dept_emps(p_deptno in number)
2 return sys_refcursor
3 is
4 v_rc sys_refcursor;
5 begin
6 open v_rc for 'select empno, ename, mgr, sal from emp where deptno = :deptno' using p_deptno;
7 return v_rc;
8 end;
9 /
Function created.
Elapsed: 00:00:00.05
satyaki>
satyaki>
satyaki>create or replace function fetch_emps(deptno in number := null)
2 return t_etype
3 is
4 v_emptype t_etype := t_etype(); -- Declare a local table structure and initialize it
5 v_cnt number := 0;
6 v_rc sys_refcursor;
7 v_empno number;
8 v_ename varchar2(10);
9 v_mgr number;
10 v_sal number;
11 begin
12 v_rc := get_dept_emps(deptno);
13 loop
14 fetch v_rc into v_empno, v_ename, v_mgr, v_sal;
15 exit when v_rc%NOTFOUND;
16 v_emptype.extend;
17 v_cnt := v_cnt + 1;
18 v_emptype(v_cnt) := etype(v_empno, v_ename, v_mgr, v_sal);
19 end loop;
20 close v_rc;
21 return v_emptype;
22 end;
23 /
Function created.
Elapsed: 00:00:00.06
satyaki>
satyaki>
satyaki>select * from t_emp;
no rows selected
Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>
satyaki>select * from t_emp;
no rows selected
Elapsed: 00:00:00.00
satyaki>
satyaki>
satyaki>insert into t_emp
2 select *
3 from table(fetch_emps(30));
4 rows created.
Elapsed: 00:00:00.02
satyaki>
satyaki>select * from t_emp;
EMPNO ENAME MGR SAL
---------- ---------- ---------- ----------
7654 MARTIN 7698 1815
7844 TURNER 7698 2178
7900 JAMES 7698 1379.4
7599 BILLY 7566 4500
Elapsed: 00:00:00.00
satyaki>
satyaki>commit;
Commit complete.
Elapsed: 00:00:00.00
satyaki>