Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved, so that the people who want to help you can re-create the problem and test their ideas.
Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
Always say which version of Oracle you're using (for example, 126.96.36.199.0).
See the forum FAQ: https://forums.oracle.com/message/9362002#9362002
My best guess, based on what you've posted os far, is that your problem is discussed in the following:
p_desig works as a comma delimited string without the NVL function, that's not the issue.
It IS the issue .. you need to provide a SAMPLE so we can see what you're doing ..
How are you "passing it in" ?
procedure ( in_var in VARCHAR2 )
and nvl(sn.c_attribute1,'x@#$%') in nvl(in_var,'x@#$%')
and nvl(sn.c_attribute1,'x@#$%') in nvl(&1,'x@#$%')
.. or some other method?
ok, you got me... I didn't really want to go down that path but I have the following cusor which converts the string to the the proper delimited values....
CURSOR get_desig_cur(v_get_desig in varchar2) IS
'[^,]+', 1, level) desig from dual
connect by regexp_substr(v_get_desig, '[^,]+', 1, level) is not null;