Re: Returning many values in a PL/SQL function

From: Chris L. Mason <cmason_at_unixzone.com>
Date: 7 Aug 1998 01:17:14 GMT
Message-ID: <6qdkiq$stv$1@nntp2.uunet.ca>

In article <6ps4m2$m6u_at_edrn.newsguy.com>, <Ulf.pettersson_at_ims.se> wrote:
>Hi,
>
>is there a way to return multiple data from a stored procedure ? (with the
>object option in version 8 it works from other PL/SQL procedures) but how do I
>do if I want to read a set of strings from a VB program ? workarounds ?
>

I'm not sure about VB, but you can create a procedure using value/result arguments. For example:

CREATE PROCEDURE testing (

	inputval1	IN	NUMBER,
	outval1		OUT	VARCHAR2(100),
	outval2		OUT VARCHAR2(100),
	outval3		OUT VARCHAR2(100)

) AS
...
...
END testing;

Then you bind four variables when preparing the sql statement:

BEGIN
        testing(:1, :2, :3, :4)
END The first variable should contain a value being passed to the procedure, and the last three should be pointers to variables. After executing, those three variable should contain whatever the procedure set outval1, outval2 and outval3 to.

I've done this successfully with perl/DBI and C/OCI

Chris

--

---

Chris L. Mason           For UNIX software ratings and reviews as
cmason_at_unixzone.com      well as industry news and opinions visit

                         UnixZone:       http://www.unixzone.com/

-----------------------------------------------------------------

Received on Thu Aug 06 1998 - 20:17:14 CDT