Tutorial :How can I get rid of dynamic SQL


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??


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.


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?


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;   /  


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


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »