Category: sys_refcursor

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>