Oracle "IN clause" from parameter

Question

I'm very unfamiliar with Oracle and am just trying to get this to work. I've looked here and here but have no idea how to make it all work. Here's what I need. I need a proc that will accept a comma-delimited string as a parameter that I can use in the "IN clause" of the query and return a cursor result. Is ther a way to do this in one, all-inclusive proc? If not, what are the different steps I need to take? Again, I apologize for my lack of knowledge of Oracle, this is just trying to get something to work real quick.

Thanks

Here's the proc (p_Scope would be the comma-delimited input):

create or replace PROCEDURE CU_SELECTION_ID
(
p_Scope IN varchar2,
p_ResultSet OUT SYS_REFCURSOR
)
is
BEGIN
OPEN p_ResultSet FOR
select
b.addr1,
b.addr2,
b.city,
b.country_code,
a.customer_no,
b.des1,
a.entity,
b.main_phone_no,
b.phone_area_code,
b.status,
b.wb_site_url,
b.zip
from
ar_customer a,
ct_addr b
where b.main_loc_flag = 'Y' and
a.customer_no = b.customer_no and
a.entity = b.cust_entity and
b.stk_loc_no = '3' and 
b.customer_no in (p_Scope);
END;

Answer 1

I believe there is a 'better way', but I'm not sure what it is right now...

This should work for you though:

replace:

b.customer_no in (p_Scope);

with

instr(p_Scope, ','||b.customer_no||',' ) > 0

This will search p_Scope and return a value of > 0 if b.customer_no appears in the list.

Make sure that the first and last character in the list is a comma (',')

(also, as a new comer to Oracle I found Tech Republic to be a very helpful quick resource.)