How can I get rid of dynamic SQL

Question

I have the following dynamic SQL in one of my package bodies

 OPEN ccur for
    'select c.category 
     from test_category c 
     where c.deptid='||PI_N_Dept ||
     ' and c.category not in ('|| sExcludeCategories ||')';

sExcludeCategories will contain a set of integers separated by comma. I would like to eliminate this dynamic SQL statement. Are there any smart ways to accomplish this??

Answer 1

I don't know oracle, but in SQL Server, it is fairly common to obtain a "split" udf (like so - just one of many, many versions available) that turns the CSV into a column of values, and join (etc) to it. Does pl-sql have anything similar?

Answer 2

create or replace type numbertype
as object
(nr number(20,10) )
/ 

create or replace type number_table
as table of numbertype
/ 

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select *
    from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs 
    where id = tbnrs.nr; 
end; 
/

Answer 3

You can write it like that:

OPEN ccur for
  select c.category 
  from test_category c 
  where 
    c.deptid= PI_N_Dept
    and c.category not in 
    (select category_id from categories where <some-condition-that-finds-the-categories-that-should-be-excluded>);

Answer 4

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.

Answer 5

You could solve this by bulk-copying your values into a temporary table, and joining against that. It might sound a bit overkill at first, but as it is a very common pattern, you would certainly end up re-using that code often.