Re: Stored Proc Question (rows returned)?

Unless I'm missing something, but the following REF CURSOR procedure takes an argument. This is the method I use with Powerbuilder datawindows.

CREATE OR REPLACE PACKAGE a_demo2 IS

 TYPE my_result_set IS REF CURSOR RETURN work_orders%ROWTYPE;

END a_demo2;
/

CREATE OR REPLACE PROCEDURE proc_demo2 (as_comp IN VARCHAR2, out_result IN OUT a_demo2.my_result_set)
 AS

 BEGIN
  OPEN out_result
  FOR SELECT * FROM work_orders
 WHERE company = as_comp;

END proc_demo2;
/

--
Terry Dykstra
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message news:947184713.3336.0.pluto.d4ee154e_at_news.demon.nl...
> 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 - 13:34:38 CST