title: Oracle nvl in where clause showing strange results?
body: <p>I have a web form that allows users to search on and edit records from an Oracle table based on parameters passed in to a proc. Here's my data:</p>
  
  <pre><code>CAE_SEC_ID  SEC_CODE  APPR_STATUS
  1           ABC1      100
  2           ABC2      100
  3           ABC3      101
  4           (null)    101
  5           (null)    102
  6           ABC4      103
  </code></pre>
  
  <p>And here's the where clause:</p>
  
  <pre><code>select foo 
    from bar 
   where CAE_SEC_ID = NVL(p_cae_sec_id,CAE_SEC_ID)
     and Upper(SEC_CODE) like '%' || Upper(NVL(p_sec_code,SEC_CODE)) || '%'
     and APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
  </code></pre>
  
  <p>Using nvl on the parameters should return only the matched records if any of the parameters have values, and all records if none of the parameters have values. All pretty standard or so I thought. However when I do a search without any parameter values the query isn't returning records with a null SEC_CODE i.e. only records 1, 2, 3, and 6 are being returned. Shouldn't the where clause above include records with null SEC_CODE values?</p>