Hi could someone help me on the best way to pass an array as a Parameter to a Stored procedure. This this case I am passing the string 'Bob,Sue' as a parameter and then I want 'Bob' and 'Sue' to end up as items in an IN list in a select statement. I tried parsing the string 'Bob,Sue' to create a varibable whose value is = 'Bob','Sue' and then reference that varible in the Select statement but that did not seem to work. I have copied that code below. If parsing a parameter string in this way is not the best method to handle this could someone please let me know what a better alternative would be. I am running Oracle version 9i running on NT.


CREATE OR REPLACE package body test_package as
procedure proto2 (
p_cust_seg_nbr in VARCHAR2,
p_start_bk_mo in number,
p_end_bk_mo in number,
o_customer_cursor out T_CURSOR)
IS
loopcount number;
posofcomma number;
tempstr varchar2(200);
origtempstr varchar2(200);
reconstr varchar2(200);
begin
posofcomma := INSTR(p_cust_seg_nbr,',');
origtempstr := p_cust_seg_nbr;
loopcount := 0;
if posofcomma > 0 then
while posofcomma > 0 loop
tempstr := substr(origtempstr,0,posofcomma-1);
origtempstr := substr(origtempstr,posofcomma+1);
posofcomma := instr(origtempstr,',');
if loopcount > 0 then
reconstr := reconstr||chr(39)||tempstr||chr(39)||',';
else
reconstr := chr(39)||tempstr||chr(39)||',';
end if;
loopcount := loopcount + 1;
end loop;
reconstr := reconstr||chr(39)||origtempstr||chr(39);
end if;
open o_customer_cursor for
select
reconstr as reconstr,
ename from emp
where ename IN (reconstr)
;
end;
end test_package;
/