Multiple bind parameters

A recent otn forum post ( started a discussion how to write correctly binded dynamic sql.


If there a several parameters which influence the WHERE condition of a dynamic SQL statement, then it is not so easy to consider all of them with bind variables. A typical scenario is that, if some of the parameters are NULL then they are not in the where clause. However this changes the number (and the order) of the binded values.


A simple workaround is to keep track of the number of parameters and have several execute immediate statements prepared with a different number of parameters.

Code template

create or replace
procedure doSQL (p1 in number default null
               , p2 in number default null
               , p3 in number default null
  type params_t is table of number;  

  v_sql varchar2(4000);
  v_no_of_binds binary_integer := 0;
  v_bind_params params_t ;   

  procedure addParam(p_sql in out nocopy varchar2,p_no_of_binds in out nocopy binary_integer) is
    p_no_of_binds := p_no_of_binds +1;
    if  p_no_of_binds = 1 then
       p_sql := p_sql || ' where ';
       p_sql := p_sql || ' and ';
    end if;
  end addParam;
  -- BUild the SQL
  v_sql := 'select * from emp';  

  -- Add parameters if they are not null
  if p1 is not null then
      v_sql := v_sql || ' deptno = :'||to_char(v_no_of_binds);
      v_bind_params(v_no_of_binds) := p1;
  end if;  

  if p2 is not null then
    v_sql := v_sql || ' empno = :'||to_char(v_no_of_binds);
    v_bind_params(v_no_of_binds) := p2;
  end if;  

  if p3 is not null then
    v_sql := v_sql || ' sal >= :'||to_char(v_no_of_binds);
    v_bind_params(v_no_of_binds) := p3;
  end if;  

  --- do the statement
  if v_no_of_binds = 0 then
    execute immediate v_sql;
  elsif v_no_of_binds = 1 then
    execute immediate v_sql using v_bind_params(1);
  elsif v_no_of_binds = 2 then
    execute immediate v_sql using v_bind_params(1), v_bind_params(2);
  elsif v_no_of_binds = 3 then
    execute immediate v_sql using v_bind_params(1), v_bind_params(2), v_bind_params(3);
  end if;
end doSQL;

Feel free to reuse this as a template for your own version of dynamic sql with binds.


For such a simple example it is better to have three different versions of the SQL availaible and use that in a non-dynamic way. But as the number over parameters increases, then the number of SQL statements increases in an exponential way (all permutations for all parameters).

Datatypes are also difficult to consider. It might make sense to build groups of parameters that all have the same datatype. Or to somehow map the paramters to varchar and bin the varchar value. This can create new issues (nls problems, code injects, etc.).

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.