Home » Developer & Programmer » Precompilers, OCI & OCCI » Urgent query - How to pass an array of strings to, and return a table set from a proc (Oracle 10g)
Urgent query - How to pass an array of strings to, and return a table set from a proc [message #490409] Mon, 24 January 2011 11:11 Go to next message
neel134
Messages: 1
Registered: January 2011
Junior Member
Hi,

I have an urgent requirement and I am stuck at a place. Any advice will be helpful.

I have to write a PL/SQL procedure, which is supposed to take an array of strings as input.
This array will have simple strings as elements, like '000887S','000780S'.
Now I have a query in the procedure, which will return a row, for each of the array elements.
For example:
SELECT
su.EMPLOYEE_ID,su.FIRST_NAME,
su.LAST_NAME
FROM
USERS su,
USER_E Sue,
ROLE r,
DIM_STAFF dos
WHERE
su.EMPLOYEE_ID=dos.LINE_EMPLOYEE_ID
and su.EMPLOYEE_ID=ue.EMPLOYEE_ID
and ue.ROLE_ID=r.ROLE_ID
and dos.EMPLOYEE_ID=? ;

In the place of the '?' in the above query, the array elements have to be passed. So we will get one row from the above query for each array element.

Now we either have to loop through the array elements to fetch the result set for the above query for each array element, or we can use some other method(as you suggest) too.

Our objective is to collect all the rows of the above query for each array element as a table data and this procedure has to return this table set.

Can any one tell me how to do all this together? This is the main question!

Any other easier methods you know, can also be helpful.

Suggestions are also requested about what will be the best way to pass such a set of data to the proc and best way for the proc to return this result set.
Like we can use arrays, table type data,ref cursors, etc.


Thanks in advance
Neel
Re: Urgent query - How to pass an array of strings to, and return a table set from a proc [message #490410 is a reply to message #490409] Mon, 24 January 2011 11:16 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Sounds like you want a varying in list
Previous Topic: help pro*c variable declarations
Next Topic: .c to .pc file
Goto Forum:
  


Current Time: Sat Jun 25 11:44:38 CDT 2022