Cursor Focus for OAF Mobile Pages

OA Pages can be rendered in PDA or Mobile devices without doing special coding. Generally hand held devices are used in shop floor, warehouses or for some field work. And the keypad of a mobile device will be small and it is difficult to use navigation buttons. So we must design the User Interface in such way to minimize button clicks and user interventions. User would love a page where he simply has to scan the labels in shop floor and may be press a single button at the end to confirm the transaction. For such an user friendly UI it is mandatory to focus the cursor on the scan field. But javaScript is completely disabled if the device (agent) is PDA/Mobile. Even
OABodyBean.setInitialFocusId() wont work in mobile. We did the following to get the cursor focus on mobile page (11.5.10 RUP3).

a. Copy OA.jsp to XXOAMobile.jsp
b. Modify the following code in XXOAMobile.jsp

Old Code
try
{
redirectURL = pageBean.preparePage(pageContext);

if (redirectURL != null)
{
%>
<jsp:forward page="<%= redirectURL %>" />


New Code
try
{
redirectURL = pageBean.preparePage(pageContext);

if (redirectURL != null)
{
String x = redirectURL.substring(2); // modified
x= "XXOAMobile" + x; // modified
%>
<jsp:forward page="<%= x %>" />



c. Seed your AOL Menu function with WEB HTML Ref as XXOAMobile.jsp?page=<Func Name> instead of OA.jsp?page=<Func Name>

d. In your CO's always do a setForwardURL to XXOAMobile.jsp?page=<Func Name>

e. Add the java script code in XXOAMobile.jsp
WARNING: This code is definetely not upgrade safe and we must revisit it after every tech stack upgrade.

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.

OAF LOV Overriding Default Where Clause

Oracle SQL never seems to like function in the WHERE clause and if the function got an indexed column as parameter then most likely the query will not perform good.

Say for example,

SELECT emp_no
FROM emp_table
WHERE UPPER(emp_name) like 'JOHN%'

If emp_table have an index on emp_name, the above query will not pick that index.

The default behavior of Oracle Framework (OAF) LOV is to add where clause with UPPER function.

SELECT * FROM (
SELECT emp_no
FROM emp_table) QRSLT
WHERE (( UPPER(emp_name) like :1 AND (emp_name like :2 OR emp_name like :3 OR emp_name like :4 OR emp_name like :5)))

And it binds values like 'JOHN%', 'John%','john%' and 'jOHN%'.



You might end up with performance issue due to this and if OAF suggested way of making the criteria passive is to0 painful then simply add the following code in the VOImpl


public void initQuery(Dictionary[] x)
{
if( x!= null)
{
for(int i = 0 ; i < x.length ; i++)
{
Dictionary z = x[i];
z.remove(OAViewObjectImpl.CRITERIA_LOOKUP_NAME);
}
}
super.initQuery(x);
}