Re: First Oracle Stored Procedure :::

From: Mateus Espadoto <mespadoto_at_yahoo.com>
Date: 26 Dec 2001 02:34:16 -0800
Message-ID: <8ca34365.0112260234.324ac2a8@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 - 04:34:16 CST