Oracle can handle result sets inside procedures. Look up ref cursors in the
manual or search this news group.
Jim
"Mateus Espadoto" <mespadoto_at_yahoo.com> wrote in message
news:8ca34365.0112260234.324ac2a8_at_posting.google.com...
> Oracle can't handle resultsets inside procedures.
> You have two options:
>
> 1 - Execute your SQL string directly (SELECT ...);
>
> 2 - Try this sample procedure, created using your SELECT statment:
>
> CREATE PROCEDURE proc_name
> ( n_part IN NUMBER)
> IS
> BEGIN
>
> FOR cur_parts IN ( SELECT Part_name,
> Owner_name,
> Part_size
> FROM Part_detail Pa,
> OWN_Deatil OD
> WHERE Pa.Part_id = n_part
> AND Pa.Part_id = OD.Part_id)
> LOOP
> DBMS_OUTPUT.PUT_LINE (cur_parts.Part_name || ';' ||
> cur_parts.Owner_Name || ';' || cur_parts.Part_size);
>
> END LOOP;
>
> END;
>
> Everything in Oracle is a cursor, implicit or explicit (our case).
> You have to use one, and return the results line by line, using the
> DBMS_OUTPUT.PUT_LINE function.
> It's not common to use procedures in Oracle to return resultsets, like
> people do in Sybase, for example. Procedures are mostly used to
> updates, inserts, deletes, but not with selects.
>
> This procedure will return the resultset with the fields separated by
> semicolons.
> This is a workaround that probably will work for you.
>
> Mateus Espadoto
>
> khurram.khan_at_qict.net (Khurram Khan) wrote in message
news:<a23090a4.0112252314.44160654_at_posting.google.com>...
> > We have a very simple requirement where user will provide Part # and
> > in return of that we will pass Part Name, Owner Name, Part Size etc.
> >
> > We can easily write SQL query to achieve required results but at this
> > time we are required to write Oracle stored procedure and we have
> > never written stored procedure before therefore please advice what
> > need to be changed in mentioned SQL query to convert it into stored
> > procedure.
> >
> > Select Part_name,Owner_name,Part_size
> > from Part_detail Pa, OWN_Deatil OD
> > where Pa.Part_id = < User provided ID >
> > and Pa.Part_id = OD.Part_id
Received on Wed Dec 26 2001 - 08:22:14 CST
