SHow a cursor

Tim Slattery

unread,

Feb 6, 2012, 3:09:01 PM2/6/12

to

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
Slatt...@bls.gov
http://members.cox.net/slatteryt

ddf

unread,

Feb 6, 2012, 5:43:22 PM2/6/12

to

On Feb 6, 1:09 pm, Tim Slattery <Slatter...@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...@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

ddf

unread,

Feb 6, 2012, 5:41:56 PM2/6/12

to

On Feb 6, 1:09 pm, Tim Slattery <Slatter...@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...@bls.govhttp://members.cox.net/slatteryt

You fetch from the refcursor:or select the function from dual:

joel garry

unread,

Feb 6, 2012, 5:53:29 PM2/6/12

to

On Feb 6, 12:09 pm, Tim Slattery <Slatter...@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...@bls.govhttp://members.cox.net/slatteryt

Also see http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/

members.cox.net? I feel for ya, man.

jg
--
http://www.utsandiego.com/news/2012/feb/02/jewish-indiana-jones-admits-ny-torah-fraud/

Tim Slattery

unread,

Feb 7, 2012, 10:43:26 AM2/7/12

to

ddf <ora...@msn.com> wrote:

>You fetch from the refcursor:or select the function from dual:

>SQL> select get_tab_data('EMP') from dual;

And that's the magic word I had forgotten! Thank you.

Now...

I have a very simple function:

create or replace FUNCTION IHELP_GETSTATUS RETURN SYS_REFCURSOR AS
cv_1 SYS_REFCURSOR;
stmt VARCHAR2(700);
BEGIN
stmt := 'select * from ihelp.ihelp_survey_mapping';
open cv_1 for stmt;
RETURN cv_1;
END IHELP_GETSTATUS;

The "select" statement works when I run it from the SQL developer
console. But when I call the function I'm told "table or view does not
exist". But it *does* exist.

For the record, I'm trying to be able to construct a SQL select
statement dynamically. I figure if I can get this simple example to
work, then I'm home free.

--
Tim Slattery
Slatt...@bls.gov

Tim Slattery

unread,

Feb 7, 2012, 12:20:13 PM2/7/12

to

Tim Slattery <Slatt...@bls.gov> wrote:

>The "select" statement works when I run it from the SQL developer
>console. But when I call the function I'm told "table or view does not
>exist". But it *does* exist.

I saw a web post that may have the answer. He says that a common cause
of this is that select permission on the table has been granted to a
role, and that role has then been assigned (granted? not sure of the
proper terminology) to me. But that role does not propagate to
functions that I write. The result is just what I'm seeing: I can
select from the table directly, but the function can't find it.

Seems an irrational way for things to work, but then I'm only a
developer...

--
Tim Slattery
Slatt...@bls.gov

ddf

unread,

Feb 8, 2012, 10:47:03 AM2/8/12

to

On Feb 7, 10:20 am, Tim Slattery <Slatter...@bls.gov> wrote:

> Slatter...@bls.gov

Sounds like a post of mine from a while back. You will need a direct
grant on the object to create a function or procedure against it, but
you can also compile it authid current_user and those who do not have
direct grants on that table can still use the function as that
construct will traverse roles for object permissions (again, it won't
work for the author of the code). A simple 'grant select on ihelp
to ....' from the table owner will get you working.

David Fitzjarrell

Gerard H. Pille

unread,

Feb 8, 2012, 1:39:12 PM2/8/12

to

If you'd like to become a good developer, don't use dynamic SQL.

Peter Schneider

unread,

Feb 8, 2012, 3:21:34 PM2/8/12

to

If you'd like to become a superb developer, learn how to use dynamic SQL at
great depth, know what bind variables are good for, learn what excessive
parsing means to your application performance...

And then you learn when dynamic SQL is appropriate and when not.

DBMS_SQL, EXECUTE IMMEDIATE and OPEN ... FOR '...' can do magic in the hand of
PL/SQL wizards ;-)

Regards
Peter

--
The only way to keep your health is to eat what you don't want, drink what
you don't like, and do what you'd rather not. -- Mark Twain

Gerard H. Pille

unread,

Feb 8, 2012, 3:37:52 PM2/8/12

to

And bring a production database to its knees.

joel garry

unread,

Feb 8, 2012, 6:49:53 PM2/8/12

to

On Feb 8, 12:37 pm, "Gerard H. Pille" <g...@skynet.be> wrote:
> Peter Schneider wrote:
> > Am 08.02.2012 19:39, schrieb Gerard H. Pille:
> >> Tim Slattery wrote:

Good is relative. When the choice is, solve a correlated update
ORA-1779 by adding a primary key constraint with all the testing and
design changes that implies, versus a one time script of 10K update
statements generated by a korn shell script, "on yer knees, beeyatch,
gettin' mah moneys worth tonight."

jg
--
@home.com is bogus.
http://www.utsandiego.com/news/2012/feb/07/viasat-files-lawsuit-against-satellite-builder/