title: Alternate of using NVL with IN clause in Oracle 11g
body: <p>I have a workplace problem to which I am looking for an easy solution. 
  I am trying to replicate it in a smaller scenario. </p>
  
  <p><b>Problem in short</b></p>
  
  <p>I want to use <code>nvl</code> inside an <code>in</code> clause. Currently I have an input string which consists of a name. It is used in a where clause like below</p>
  
  <pre><code>and column_n = nvl(in_parameter,column_n)
  </code></pre>
  
  <p>Now I want to pass multiple comma separated values in same input parameter. So if I replace <code>=</code> with <code>in</code>, and transpose the input comma separated string as rows, I cannot use the <code>nvl</code> clause with it.</p>
  
  <p><b>Problem in Detail </b></p>
  
  <p>Lets consider an Employee table emp1.</p>
  
  <pre><code>Emp1
  +-------+-------+
  | empno | ename |
  +-------+-------+
  |  7839 | KING  |
  |  7698 | BLAKE |
  |  7782 | CLARK |
  +-------+-------+
  </code></pre>
  
  <p>Now this is a simple version of an existing stored procedure</p>
  
  <pre><code>create or replace procedure emp_poc(in_names IN varchar2)
  as
   cnt integer;
  begin
   select count(*) 
    into cnt 
   from emp1 
   where 
   ename = nvl(in_names,ename); --This is one of the condition where we will make the change.
  dbms_output.put_line(cnt);
  end;
  </code></pre>
  
  <p>So this will give the count of number of employees passed as Input Parameter. But if we pass null, it will return the whole count of employee becuase of the <code>nvl</code>.</p>
  
  <p>So these procedure calls will render the given outputs.</p>
  
  <pre><code>Procedure Call          Output
  exec emp_poc('KING')    1
  exec emp_poc('JOHN')    0
  exec emp_poc(null)      3
  </code></pre>
  
  <p>Now what I want to achieve is to add another functionality. So <code>exec emp_poc('KING,BLAKE')</code> should give me <code>2</code>. So I figured a way to split the comma separated string to rows and used that in the procedure. </p>
  
  <p>So if I change the <code>where</code> clause as below to <code>in</code></p>
  
  <pre><code>create or replace procedure emp_poc2(in_names IN varchar2)
  as
   cnt integer;
  begin
   select count(*) 
    into cnt 
   from emp1 
   where 
  ename in (select trim(regexp_substr(in_names, '[^,]+', 1, level)) 
          from dual 
              connect by instr(in_names, ',', 1, level - 1) &gt; 0
            );
  dbms_output.put_line(cnt);
  end;
  </code></pre>
  
  <p>So <code>exec emp_poc2('KING','BLAKE')</code> gives me <code>2</code>. But passing <code>null</code> will give result as <code>0</code>. However I want to get <code>3</code> like the case with <code>emp_proc</code> 
  And I cannot use <code>nvl</code> with <code>in</code> as it expect the subquery to return a single value.</p>
  
  <p>1 way I can think of is rebuilding the whole query in a variable, based in input paramteter, and then use <code>execute immediate</code>. But I am using some variables to collect the value and it would be difficult to achieve it with <code>execute immediate</code>. </p>
  
  <p>I am again emphasizing that this is a simple version of a complex procedure where we are capturing many variables and it joins many tables and has multiple <code>AND</code> conditions in where clause.</p>
  
  <p>Any ideas on how to make this work. </p>