OraFAQ Forum: SQL & PL/SQL » Executing a proc that returns a cursor as output param

Home » SQL & PL/SQL » SQL & PL/SQL » Executing a proc that returns a cursor as output param

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Executing a proc that returns a cursor as output param [message #15549]

Wed, 13 October 2004 09:43

Oracle Greenhorn
Messages: 1
Registered: October 2004

Junior Member

Hello,

I seek your expert advise to help me understand how to execute a stored procedure that returns a cursor as an output parameter. I want to be able to execute this call from DB Visualizer (Oracle client tool). How do I declare the variable into which I like to take the output paramter? How do I display the results that are returned into this variable? Your help with a sample or example will be grately appreciated.

Thanks & Regards,

Oracle Green Horn

Report message to a moderator

Re: Executing a proc that returns a cursor as output param [message #15550 is a reply to message #15549]

Wed, 13 October 2004 09:52

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I'm not familiar with that tool, but their website says that "Executing stored procedures is not officially supported by DbVisualizer even though it works for some databases. The best way to figure it out is to test." The parameter would be declared as a Cursor or Ref Cursor or equivalent.

Report message to a moderator

Re: Executing a proc that returns a cursor as output param [message #15554 is a reply to message #15550]

Wed, 13 October 2004 12:28

Oracle Green Horn
Messages: 2
Registered: October 2004

Junior Member

Todd,

Thanks for your reply. Can you provide me with an example on how to display the results taken into the variable declared for the output param. I could be a generic one, not specific to DB Viz.

Thanks,
OGH

Report message to a moderator

Re: Executing a proc that returns a cursor as output param [message #15556 is a reply to message #15554]

Wed, 13 October 2004 15:04

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Well, it is going to be different in every host program, but here is how it works in SQL*Plus:

sql>variable rc refcursor
sql>exec pkg_prototype.p_get_employees(:rc)
 
PL/SQL procedure successfully completed.
 
sql>print rc
 
    EMPNO ENAME      JOB
--------- ---------- ---------
     7876 ADAMS      CLERK
     7499 ALLEN      SALESMAN
     7698 BLAKE      MANAGER
     7782 CLARK      MANAGER
     7902 FORD       ANALYST
     7900 JAMES      CLERK
     7566 JONES      MANAGER
     7839 KING       PRESIDENT
     7654 MARTIN     SALESMAN
     7934 MILLER     CLERK
     7788 SCOTT      ANALYST
     7369 SMITH      CLERK
     7844 TURNER     SALESMAN
     7521 WARD       SALESMAN
 
14 rows selected.

Now, that PRINT command in SQL*Plus is doing a lot under the hood. It is parsing the cursor (to figure out which columns are there), fetching each row into variables, printing those values, and then finally closing the cursor.

Report message to a moderator

Previous Topic:	help required with sql script re. numbers and characters
Next Topic:	Error while using a Stored Function in Select

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 18 08:40:00 CST 2026