Re: Stored Proc Question (rows returned)?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 06 Jan 2000 14:16:58 -0500
Message-ID: <odq97scq1dq3k3ad8b0og6pl639mk31i7b@4ax.com>

A copy of this was sent to "Sybrand Bakker" <postmaster_at_sybrandb.demon.nl> (if that email address didn't require changing) On Thu, 6 Jan 2000 19:50:24 +0100, you wrote:

>You can do that in two ways
>- if there's going to be communication with the outside world (ie outside
>PL/SQL) you can use REF CURSORs. The ref cursor is an in out parameter to
>the procedure.
>- if the cursor results are being used in other PL/SQL procedures, you could
>just as well use packaged cursors. By this I mean cursors defined in a
>package header. Just open the cursor in a procedure and you are set.
>
>With respect to your concern: AFAIK ref cursors can not be parametrized,
>packaged cursors can.
>
>Hth,

sure they can -- just like packaged cursors. Just put references to plsql variables in them and they are parameterized:

tkyte_at_8i> variable x refcursor
tkyte_at_8i> 
tkyte_at_8i> declare
  2          l_param         number default 5;
  3  begin
  4          open :x for select * from all_users where rownum < l_param;

  5 end;
  6 /

PL/SQL procedure successfully completed.

tkyte_at_8i>
tkyte_at_8i> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 20-APR-99
SYSTEM                                  5 20-APR-99
OUTLN                                  11 20-APR-99
DBSNMP                                 18 20-APR-99

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 06 2000 - 13:16:58 CST