How can I get rid of dynamic SQL

oracle, plsql, sql

Solution

I'm guessing you know you can bind a variable to `PI_N_Dept` to remove that piece of dynamic sql. Unfortunately for your `IN` clause and `sExcludeCategories` you can't bind a variable for a list in Oracle (at least up to 9.2 as far as i know)

You do have a few choices. Your current solution is the easiest. Another solution is to change the procedure to accept multiple variables and create a list of AND statements.

'select c.category 
     from test_category c 
     where c.deptid= :PI_N_Dept
       and c.category <> :sExcludeCategory1 
       and c.category <> :sExcludeCategory2
       and c.category <> :sExcludeCategory3

';

or have a fixed list of IN values

'select c.category 
     from test_category c 
     where c.deptid= :PI_N_Dept
       and c.category not in (:sExcludeCategory1 , :sExcludeCategory2, :sExcludeCategory3)';

You'll have to be careful in the case where you only want 2 categories. The third will have to be set to some value not in c.category (NB: be careful and test null values here)

Another solution is presented in Ask Tom. This looks quite simple, though i haven't tested it. It works by creating a function str2tbl() which allows you to pass a series of numbers separated by commas and creating a 'table' via dual to do a IN.

create or replace type myTableType as table of number;

create or replace function str2tbl( p_str in varchar2 ) return myTableType
  as
     l_str   long default p_str || ',';
     l_n        number;
     l_data    myTableType := myTabletype();
  begin
      loop
          l_n := instr( l_str, ',' );
          exit when (nvl(l_n,0) = 0);
          l_data.extend;
          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
          l_str := substr( l_str, l_n+1 );
      end loop;
      return l_data;
  end;

Your example would look something like

'select c.category 
     from test_category c 
     where c.deptid= :PI_N_Dept
       and c.category not in ( select * from INLIST ( select cast( str2tbl( :sExcludeCategories  ) as mytableType ) from dual ) )';

This would only work if `sExcludeCategories` was a list of numbers. You'd have to change str2tbl to handle quotes if they are included in the variable (and you cant change it) plus change the type of `myTableType` to `varchar2(10)` or something more appropriate.

Overall, if the original sql not effecting performance then for simplicity sake i'd leave it as dynamic SQL. It's much less of a head ache to maintain. Otherwise test out the str2tbl. It should work in Oracle 8 and above.

PS: Just for completeness, i came across this nice article on binding vars that covers the simple problems like though not using variables for IN clauses.

Problem

Original source

Related problems

• How do I split a delimited string so I can access individual items?