Re: Stored Proc Question (rows returned)?

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,

--
Sybrand Bakker, Oracle DBA
Joe M. <joemart_at_my-deja.com> wrote in message news:852md6$6ku$1_at_nnrp1.deja.com...
> I think this is a simple question, I want to write a stored procedure
> that will take a pramater and return result rows. As an example,
> gieven a department number I want to return a list (rows) for all
> employees in that department. I know how to do this in Sybase or MS
> SQL but I haven't seen an exapmle anywhere in PL/SQL. All the exapmles
> of procs (or functions) I see in PL/SQL only insert a row into a table,
> never return multiple rows.
>
> My motivation for this is to replace a bunch of ad hoc queries with
> stored procedures. I hope that by doing this I can better manage what
> is going in/out of the shared pool (~ procedure cache in Sybase?). My
> concern is that in the system I am newly admining there are a lot of
> users using ad hoc queries and I'm not sure the execution plans are
> being reused. Interesting article at www.oreview.com/9701snow.htm.
>
> Thanks,
> Joe
> Yes, I am new to oracle.
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 06 2000 - 12:50:24 CST