How to pass a list of values to one Oracle Report parameter?

Question

I have one parameter (parameter_in) in my Oracle Report Builder, I am using this parameter in following query in this report but the result is not appear. My name parameter is (mhn.kod_urusan IN (replace(:p_kod_urusan,'[^,]+',',') ) or :p_kod_urusan is null) This my query:-

select distinct to_char(mhn.trh_masuk,'dd/mm/yyyy') trh_masuk,
p.nama nama,mhn.kod_urusan

from mohon mhn,mohon_hakmilik mh, pemohon pm, pihak p, 
hakmilik h,
kod_daerah kd,kod_bpm kb
where 
mhn.id_mohon = mh.id_mohon
and mhn.id_mohon = pm.id_mohon
and pm.id_pihak = p.id_pihak(+)
and mh.id_hakmilik = h.id_hakmilik
and h.kod_daerah = kd.kod(+)
and (upper(TRUNC(mhn.trh_masuk)) BETWEEN :p_date1  AND :p_date2 )
and (mhn.kod_urusan IN (replace(:p_kod_urusan,'[^,]+',',') ) or  :p_kod_urusan is null)    
order by  1 asc

Anyone got the idea?.:(:(

Answer 1

There are two ways you could solve this in Oracle Reports. I would usually prefer option #1.

Option 1: use INSTR to compare the values

and (INSTR(','||:p_kod_urusan||',', ','||mhn.kod_urusan||',') > 0
    or :p_kod_urusan is null)    

Option 2: use a lexical parameter

and (mhn.kod_urusan IN (&p_kod_urusan) or  :p_kod_urusan is null)

With option 2, you need to watch out for the SQL injection vulnerability, as well as a potential performance issue caused by hard parsing and multiple query plans.

I'm not sure why you're replacing the string '[^,]+' with a comma. I've assumed your parameter is already a comma-delimited list of values.