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;
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.)