Multiple values for a stored procedure parameter

[DianeJ]

I'm wondering if it is possible to pass multiple values into a single stored procedure parameter.

As an example let's say I have a stored procedure that returns the number of hours a person has worked when passed in an ID. Would it be possible to pass in multiple ID's (perhaps an array), and somehow use a for loop to select the relevant records to come up with the total hours worked for all the people whose ID's we passed in?

Just a thought - could it be done through a ref cursor? Some stored procs that I've done have used a ref cursor as an IN/OUT parameter to pass out the records.

Any ideas would be appreciated!

Diane

 

[MikeJones]

You could use a v-array (a variable length array) I think this would be best as it's simeple to manipulate. Log on to technet (technet.oracle.com), memebership is free and look up varrays under the documentation section for the server release you are running.

Good luck.

Mike.

 

[CRoberts]

Another approach is to pass in an Index-By table(was called PL/SQL Table). This is usually easier to work with in pure PL/SQL. A Varray is defined to be a particular size and sparseness can a problem, depending on your implementation. With an Index-By table, just pass in your Index-By table and loop on it and sum up your hours. No need to worry about how many elements and whether there is a gap between ids. The syntax for using Varray is not as straight forward as an Index-By table.

 

[BML]

Hi,

how can I call the stored procedure from VB 6.0 passing an array (Index-By table in PL/SQL) as IN parameter ?

regards,

Bruno Loureiro
<brunoloureiro@usa.net>