Variable IN List

Another classic performance problem in OAF is to add a dynamic WHERE clause to VO with a variable IN list. i.e we need to add some thing like

<column_name> IN (:1, :2, :3 ...:N)

Where N can vary.


Illustrating various options with an example.

Assume we have a VO, ExampleVO with following SQL

select emp_name, dept_no
from emp_table

And we want to add a filter of dynamic list of dept_no.

Option 1: Use SQL Literal.
Build the WHERE clause with String literal.

public void initQuery(String depNoList)
{
String whereClause = "dept_no IN " + "(" + depNoList " + ")";
...
}

But this is a big NO-NO if depNoList changes, as it forces hard parse of the SQL. So this should never be considered as an option.


Option 2: Fix Max(N) at design time.
Fix maximum number of variables at design time, say M.
If N <= M, then
bind 1..N from the list
bind N+1 ..M with a dummy value or one value from the list.
ELSE
// pick a M such that this will never happen in real time
// if it happens then the choice is yours, you can either throw an error
// or always just bind N




public void initQuery(String depNoList)
{
String whereClause = "dept_no IN (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)";
Vector inList = strToVector(depNoList); // simple method to convert str to vector
Vector bindValues = ne Vector(10);
if(inList != null && inList.size() > 0)
{
Enumeration e = m_inList.elements();
int i=0;
while(e.hasMoreElements() && i < 10)
{
bindValues.addElement(e.nextElement());
i++;
}
for(int j=bindValues.size(); j< 10 ; j++)
{
bindValues.addElement(bindValues.elementAt(0)); // fill rest with first value
}

}
else
{
for(int i=0; i<10 ; i++)
bindValues.addElement("DUMMY");
}
...
}

Option 3: Use Global Temporary Table.

Create a Global Temporary Table

CREATE GLOBAL TEMPORARY TABLE xx_bind_value_table
(
key VARCHAR2(50),
value VARCHAR2(100),
) ON COMMIT PRESERVE ROWS


Before calling initQuery method insert these values into GTT and pass the key


public void initQuery(String key)
{
String whereClause = "dept_no IN (SELECT value FROM xx_bind_value_table WHERE key = :1)";
..
}

This option have lots of disadvantages
1. We must generate a unique key per session
2. Some PLSQL/EO code required to insert the value into GTT
3. GTT is not recommended by OAF and by using GTT our code will not be Connection Agnostic.


Option 4: TABLE CAST Operator.

Reference: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

a. Create a TABLE TYPE

create or replace type inListTableType as table of VARCHAR2;

b. Create a PLSQL function which converts comma separated list to the above table type object. (Code from asktom)

create or replace function toTable( p_str in varchar2 ) return inListTableType
as
l_str varchar2(4000);
l_n number;
l_data inListTableType := inListTableType();
begin
l_str := l_str || ',';
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;



c. Add the following where clause


public void initQuery(String depNoList)
{
String whereClause = "dept_no in ( select * from THE ( select cast( toTable( :1 ) as inListTableType ) from dual ) )";
// bind the depNoList

..
}


By far the best option I guess.