title: Oracle dynamic SQL with null parameters
body: <p>I have the following procedure, that updates client's info depending on the input parameters.</p>
  
  <pre><code>-- Update client
  create or replace procedure p_update_client (i_client_id in number, i_iin in number default null, i_full_name in varchar default null)
  as
  query_str varchar(200);
  no_id_provided exception;
  all_null_values exception;
  begin
  -- Handle input parameters
  if i_client_id is null then
      raise no_id_provided;
  end if;
  if i_iin is null and i_full_name is null then
      raise all_null_values;
  end if;
  
  -- Base query string.
  query_str := 'update t_client set';
  -- Form SQL depending on the input parameters.
  if i_iin is not null then
      query_str := query_str || ' iin = :param1';
  end if;
  if i_full_name is not null then
      query_str := query_str || ' full_name = :param2';
  end if;
  -- Add necessary where clause to identify record.
  query_str := query_str || ' where client_id = :param3;';
  
  -- Execute query.
  execute immediate query_str using i_iin, i_full_name, i_client_id;
  exception
  when no_id_provided then
      raise_application_error(-20100, 'Client_id value must not be null.');
  when all_null_values then
      raise_application_error(-20101, 'To update record, input parameters must not be null.');
  when others then
      rollback;
  end p_update_client;
  </code></pre>
  
  <p>So, procedure's logic is the following: if passed parameter has non-null value, then I dynamicaly update my SQL and execute it using <code>execute immidiate</code>.
  This works fine as long as both parameters has non-null values. If one of the parameters is null, then <code>query_str</code> will throw an SQL error <code>ORA-01006: bind variable does not exist</code> because number of parameters, specified in the <code>query_str</code> is not the same as in <code>using</code> clause.</p>
  
  <p>What is the better way to handle such situation, maybe sone kind of named parameters, but as I know, <code>execute emmidiate</code> does not provide that.
  Any ideas?  </p>