Multiple bind parameters

A recent otn forum post (https://community.oracle.com/message/13592114#13592114) started a discussion how to write correctly binded dynamic sql.

Problem

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 int he where clause. However this changes the number (and the order) of the binded values.

Solution

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
               )  
is  
  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
  begin
    p_no_of_binds := p_no_of_binds +1;  
    if  p_no_of_binds = 1 then   
       p_sql := p_sql || ' where ';  
    else  
       p_sql := p_sql || ' and ';  
    end if;  
  end addParam;
begin  
  -- BUild the SQL 
  v_sql := 'select * from emp';  
  
  -- Add parameters if they are not null
  if p1 is not null then  
      addParam(v_sql,v_no_of_binds);
      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  
    addParam(v_sql,v_no_of_binds);
    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  
    addParam(v_sql,v_no_of_binds);
    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.

Discussion

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.).

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s