Re: SHow a cursor

From: ddf <oratune_at_msn.com>
Date: Mon, 6 Feb 2012 14:43:22 -0800 (PST)
Message-ID: <12a53f62-027e-437f-b51c-d7738b57cf41_at_p7g2000yqk.googlegroups.com>

On Feb 6, 1:09 pm, Tim Slattery <Slatter..._at_bls.gov> wrote:
> I've got a PL/SQL function that returns a SYS_REFCURSOR. I'm using SQL
> Developer, and trying to call it and display what it's returned. And I
> don't have a clue how to do it. Help!
>
> --
> Tim Slattery
> Slatter..._at_bls.govhttp://members.cox.net/slatteryt

Answered in another group but I'll also post the example here:

SQL> create or replace function get_tab_data(tabname in varchar2)   2 return sys_refcursor as
  3
  4 mycur sys_refcursor;
  5
  6 sqlstr varchar2(4000);
  7
  8 begin
  9 sqlstr := 'select * from '||tabname;  10
 11 open mycur for sqlstr;
 12
 13 return mycur;
 14
 15 end;
 16 /

Function created.

SQL>
SQL> show errors function get_tab_data
No errors.
SQL>
SQL> select get_tab_data('EMP') from dual;

GET_TAB_DATA('EMP')

---

CURSOR STATEMENT : 1 CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7369 SMITH      CLERK           7902 17-DEC-80
800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600
300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250
500         30
      7566 JONES      MANAGER         7839 02-APR-81
2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250
1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81
2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81
2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82
3000                    20
      7839 KING       PRESIDENT            17-NOV-81
5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81
1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83
1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL
COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7900 JAMES      CLERK           7698 03-DEC-81
950                    30
      7902 FORD       ANALYST         7566 03-DEC-81
3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82
1300                    10

14 rows selected.

SQL> David Fitzjarrell Received on Mon Feb 06 2012 - 16:43:22 CST