Re: SHow a cursor
From: ddf <oratune_at_msn.com>
Date: Mon, 6 Feb 2012 14:41:56 -0800 (PST)
Message-ID: <dc8cac48-5f1e-480a-83a9-8b1449978d31_at_a15g2000yqf.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
CURSOR STATEMENT : 1 CURSOR STATEMENT : 1
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
Date: Mon, 6 Feb 2012 14:41:56 -0800 (PST)
Message-ID: <dc8cac48-5f1e-480a-83a9-8b1449978d31_at_a15g2000yqf.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
You fetch from the refcursor:or select the function from dual:
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:41:56 CST