SET DEFINE OFF

In the last post I mentioned about shell script for converting excel to insert statements. I came across one more problem with that task. The values in insert statement had "&" (ampersand) and the sqlplus was prompting to enter a value. There are slick ways to encode ampersand using sed, but the simplest one is to turn that behavior off in sqlplus. The following command will do the trick.

SET DEFINE OFF.

My first SED-AWK script

I have heard a lot about SED-AWK scripts but never had a chance to work on it. Recently I was assigned a mundane task of importing excel sheet to Oracle database table. Of course there are plenty of tools available in market but my case is little complex as the data I had was denormalized. So I must find ID value for some column and also some formatting/calculation was required for other columns. There are many ways to solve this problem but this time I took an approach of solving it using SED-AWK.

AWK Script to create insert statements.

/a*/ {
print "insert into <TABLE> (<COLUMNLIST>) values (";
for (i=1; i< NF; ++i)
{
    if ($i=="SYSDATE")
    {
         print $i",";
    }
    else
    {
        if (i >= 2 && i <= 7)
        {
            print $i"*3600,";
        }
   else
      {
           print "'"$i"',";
   }
 }
}
print "(select item_id from ic_item_mst where item_no = "$i"))";
print "/";}

Shell script to converts CSV to Insert SQL.

#
# CSV to INSERT SQL
# param 1 TABLE NAME
# param 2 COMMA SEPERATED COLUMN LIST
# param 3 awk script file
# parma 4 CSV values
#

USAGE="Usage: 0-ScriptName 1-Table Name 2- Comma seperated column list 3- awk script 4- values csv file";

if test $# != 4
then
  echo "$0: Error: Incorrect Arguments.."
  echo $USAGE
  exit 1
fi

cp $3 temp.awk

#
# Encode spaces with @@ token
#
sed 's/ /@@/g' $4 > temp1

#
# Replace , with spaces so that awk can read it
#
sed 's/,/ /g' temp1 > tempvalues.txt

#
# Replace table name in AWK script
#
sed 's/<TABLE>/'$1'/g' temp.awk > test.awk
rm temp.awk

#
# Replace column list in AWK script
#
sed 's/<COLUMNLIST>/'$2'/g' test.awk > final.awk
rm test.awk

#
# AWK and generate the insert stmt
#
awk -f final.awk tempvalues.txt > temp.sql
rm final.awk
rm tempvalues.txt
rm temp1

#
# Decode spaces back
#
sed 's/@@/ /g' temp.sql > insertstmt.sql
rm temp.sql

In the AWK script I have written code to multiply columns 2 to 7 with 3600 and I am getting ID value for the last column.

Example:

./ci.sh xxtmg_sl_std_maintenance "RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID" 
xsm.awk
final.csv

Sample insert statement from the output:

insert into xxtmg_sl_std_maintenance (RUN_SPEED,CT_6ORLESS,CT_6TO15,
CT_15ORMORE,CCT_6ORLESS,CCT_6TO15,CCT_15ORMORE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,ORGN_CODE,RESOURCES,ITEM_ID)
values (
'800',
0.5*3600,
0.75*3600,
1*3600,
0.2*3600,
0.25*3600,
0.33*3600,
'1913',
SYSDATE,
'1913',
SYSDATE,
'-1',
'LIB',
'SLITTER',
(select item_id
from ic_item_mst
where item_no = '0315MS600 000'))

Ask Tom first

Oracle histogram statistics helps CBO to choose the right/best execution plan for the SQL. There are plenty of articles on it in the net and most of deals with it really deep. And of course the authoritative one is the Oracle Ref Guide . So I was planning to write a blog entry with simple example to explain histograms. Ok, then I thought let me quickly search asktom and make sure it don't have similar entry .. and hola ...there was this discussion on histogram ... much better and simpler ....boy .....that site is the best .....

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

I decided to look at that site first for all my Oracle database problems .....

TNS

Have been using the abbrevation TNS in Oracle context for last four years but never cared about what actually it meant. Well by chance I learned today that TNS stands for Transparent Network Substrate.

IS_NUMBER()

If we you are storing NUMBER and character in a VARCHAR2 column and you want to validate if the value is number or not you can use the following regex based SQL.

SELECT 
 nvl(regexp_substr(:test_number,
     '^(\+|-)?([0-9])*(.)?([eE])?(\+|-)?([0-9])+$'),              
    'Not a number') 
FROM   DUAL 

You can read more about it in asktom

OA Framework Custom Component: Pivot Table 1.1 beta version

I am in the process of creating a custom component for OA Framework called Pivot Table which has similar features of Microsoft Excel Pivot Table, but UI more inline with OA Framework. I am releasing the first (beta) version. It is meant to be used with table region, preferably a read only table. But it can be used independently. Currently it supports only single level of summary and needs to be added to your page from JDeveloper. Future vesions will have multiple levels of summary and ability to add it through personalization. Following are the steps to use it in your page.

1. Download the beta version.
2. Follow the installation steps in ReadMe. It is simple.
3. Add a stackLayoutRegion below the table region region of your page.
4. For the above stackLayoutRegion, set the extends property as
/ramble/oracle/apps/qa/component/pivot/ui/webui/PivotSharedRN
5. Set User Defined: Attribute1 in Property Inspector as the View Object Instance name of the table region.

Pivot Table has two regions
Pivot Parameter Region:
It will have four picklist.

• Row - Pivot row which will show all attributes of Source VO set in step 5. Can be null.
• Column - Pivot column which will show all attributes of Source VO set in step 5. Can be null
• Data - Pivot data which will show all attributes of Source VO set in step 5. Has to be Number attribute if you want some statistical function from pivot table.
• Function - Pivot data function. Currently supports COUNT, SUM, AVERAGE, MIN and MAX.

Result Region
Based on the parameter region and if the pivot source specified in step 5 is executed, user can press "REFRESH" to see the pivot summary.

I am working on final version of release 1.0 and will come up with better documentation soon.

OAF v ADF: Finally its official

I always had tough time convincing my management and sales folks (who in turn must convince customers) to prefer OAF over much hyped ADF for custom extension of Ebusiness Suite. Well, to be honest there was one part of me, a teeny tiny part, that was skeptical and sometime cynically pointing out the conservative nature of me sticking to the technology I know rather than learning or adopting the new. Oracle finally managed to release a white paper on it. (Got it from Steve Chan's blog) You can download it from metalink.

I am happy that Oracle finally felt it important to release something of this sort. Some of the key highlights
- OAF and ADF are not entirely different. Both uses some common components like BC4J, UIX etc
- If you are planning to integrate with Ebusiness suite, stick with OAF.
- If your application is entirely new and independent of ebusiness suite then use ADF.

Some of my observations

1. The disclaimer, quoted below, kinda scared me initially, but I guess its just some legal stuff.

"The following is intended to outline our general product direction. It is intended
for information purposes only, and may not be incorporated into any contract. It is
not a commitment to deliver any material, code, or functionality, and should not
be relied upon in making purchasing decisions. The development, release, and
timing of any features or functionality described for Oracle’s products remains at
the sole discretion of Oracle."


2. You have to believe things said in the article since it is from Oracle. But still I was curious about author of this white paper. Sarah Woodhull, not heard that name before but googled it found that she is Senior Product Analyst with ATG group. The co-author, Padmaprabodh Ambale, is famous in OAF circles and I have interacted with him many times during my stint in Oracle development and he took one of the OAF training class. (Ironically in that class like three years ago he kinda mentioned that OAF is gonna go away soon)

3. Oracle evades one key question. Can we easily migrate the custom extensions to fusion middle ware? White paper says that it is "possible" to migrate the UI (OK thats convincing) and Controller migration is non-trivial (How???) and easy migration for BC4J (phuh I guessed it).

4. Well guys it is final ..but not final yet. Wait until ADF11g is out. Oracle might change their mind.

"When ADF 11g becomes production, we will publish a revised paper that compares OAF with
ADF 11g, and discuss development against E-Business Suite."


5. OAF AND ADF – A DETAILED COMPARISON Table looks cool and totally favors OAF based on number of "X" marks against ADF. But I think the paper left out of lotsa cool things of ADF which would have cause lot many "X" against OAF. So it seems the table was made after making the decision or should I say recommendation.


6. It has "X" for PLSQL DML Operation Support for ADF. I dont know that much ADF, but thats scary ....


Overall I am sure that lotsa customers are gonna get huge benefits out of this white paper ....

Bound Values in OA framework - II

Lets take a closer look at the interface DataObject.

From javadoc

The DataObject interface provides an extremely simple API for retrieving arbitrarily structured data. All "queries" are based simply on a selection string. It is entirely up to an implementation of this interface to define the syntax for these strings.

It has only one method.

public java.lang.Object selectValue(RenderingContext context, java.lang.Object select)


OA Framework creates a named DataObject for each data source and then caches the DataObject on oracle.cabo.ui.RenderingContext. A data source is identified by the view usage name set on the web bean. OA Framework then gets the value from the DataObject using the view attribute on the bean as a lookup key.

OAWebBean.getValue() >> delegates to OADataBoundValue.getValue(RenderingContext context) and passes rendering context as parameter >> which lookups DataObject from
renderingcontext based on view usage name setup in the web bean and calls that DataObject.selectValue () with view attribute name as lookup key.

For a table bean OA fraemwork creates DataList which is a list of DataObject. ReneringContext.getCurrentDataObject() will return the DataObject for the current processing
row.

So code in OADataBoundValue.getValue(RenderingContext context) will look like

  DataObject dataobject = renderingcontext.getCurrentDataObject(); 
if(dataobject == null)   {
     // will return view usage name
     String s = ((OAWebBeanData)mOAWebBean).getDataObjectName();
     if(s != null)     {  
      // first parameter is name space URI
        dataobject = renderingcontext.getDataObject(
                             "oracle.apps.fnd.framework",
                              s);
     }
 }
   // gets view attribute name  
String s1 = ((OAWebBeanDataAttribute)mOAWebBean).getDataAttributeName();  
if(s1 != null && !s1.equals(""))    
   return dataobject.selectValue(renderingcontext, s);

Most of the above things are just good to know kind of information. So lets take a look at this in OAF developer perspective on how to use this.

Typical use cases are
(a) To programaticaly bind a web bean property like "Rendered" or "Prompt" etc to a view object
(b) To programaticaly set fire action event parameters.

STEP 1: Find out whether the web bean takes BoundValue as parameter for the property/attribute.
STEP 2: Find the right BoundValue implementation to pass it to the web bean. For example for the USE CASE (a) we will use OADataBoundValueViewObject and for USE CASE (b) we will use OADataBoundValueFireActionURL.
STEP 3: Determine the parameters required to be passed to the BooundValue constructor.
STEP 4: Create the corresponding BoundValue object and pass it to the setAttribute() method.

Example 1: (Based on USE CASE a: To set prompt of web bean to a view object)

OAMessageStyledTextBean mTextBean =
     (OAMessageStyledTextBean)webBean.findChildRecursive("TextID");
OADataBoundValueViewObject promptBV =
   new OADataBoundValueViewObject(mTextBean, "VoAttribute");
mTextBean.setAttributeValue(
       oracle.cabo.ui.UIConstants.PROMPT_ATTR, promptBV);

Example 2: (Based on USE CASE b: To set fire action event programaticaly )

        OAMessageStyledTextBean b =
        (OAMessageStyledTextBean)createWebBean(
                                    pageContext,
                                    MESSAGE_STYLED_TEXT_BEAN,
                                    "NUMBER",
                                    "TextID");       
    b.setViewUsageName("ViewusageName");
    b.setViewAttributeName("ViewAttributeName");
    Hashtable params = new Hashtable(1);
    OADataBoundValueFireActionURL fireEventBV = new
      OADataBoundValueFireActionURL(webBean,"{$ViewAttributeName}");
    params.put("FireEventParameter",fireEventBV);
    b.setFireActionForSubmit("fireEvent",null,
                             params,false,false);

TemplateCO in OA Framework

RAD way of assigning a controller to a region is to right click on the region node in Structure Pane and selecting "Set new Controller" option. OA framework automatically creates the CO with some default code. That default code comes from <JDEV_HOME>\jdevbin\jdev\lib\ext\jrad\config\TemplateCO.java

I modified this template CO to import OAApplicationModule and OAException. You can play around with other templates in this folder (at your own risk).

Bound Values in OA framework - I

A web bean in OAF(UIX) consists of

• Indexed Children
• Named Children
• Attributes.

Attributes can be specified at design time in which case it will be a simple name/value pair and value of that attribute will be static. Attribute value can also be determined during run time. If the attribute value has to be determined runtime it must be bound to a data source. When OAF(UIX) is building/rendering these UI nodes it establishes a context called rendering context. And to acheive this runtime behavior we get the data source from this rendering context.

Lets convert the above statement in English Language to Java language

RenderingContext - Interface representing the context when OAF(UIX) builds(renders) the UI nodes.
BoundValue - Is a simple interface with a single method getValue(RenderingContext context). It is left to the implementation of getValue() method to achieve the run time behavior based on rendering context object.

So OAWebBean (MutableUINode to be precise) have a method to set attribute value.

public void setAttributeValue(AttributeKey attrKey,Object value)

Value can be any implementation of BoundValue interface if it must be determined at runtime.

And at run time, when OAF(UIX) uses getAttributeValue(RenderingContext context, AttributeKey key), a typical code might look like

if ((value instanceof BoundValue))
{
return ((BoundValue)value).getValue(context);
}

Generally the parameters that determine the run time value will be passed to the constructor of BoundValue implementing object. For example DataBoundValue is a implementation of BoundValue which reads value based on DataObject in the context. DataObject is very simple interface to extract arbitarily structured data.

So one of the construtor of that Class is

public DataBoundValue(
Object select
)


where the parameter, select, is used to get the DataObject from RenderingContext.

And getValue() method implementation will be simple

return context.getCurrentDataObject().selectValue(context, select);

Bind Peeking

We have this really dandy performance monitoring tool which breaks down database activity (wait events) time wise, module wise, user wise etc. I am usually concerned with Top SQL tab which shows most non performing SQL (Top SQL !! misnomer I guess). So out of no where for few days one SQL got this top most spot and disappeared automagically. I decided to sleuth a little bit about this mysterious SQL.


The suspect

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = :B1


The Investigation


IC_LOTS_MST is Oracle Process Manufacturing table which stores lot information for the item. ITEM_ID and LOT_ID forms combinational primary key and there exists a unique index, IC_LOTS_MST_PK, on these columns. LOT_ID is unique but for the fact that there can be non lot controlled item for which LOT_ID = 0. So we also have non-unique index IC_LOTS_MST_I2 on LOT_ID.


select lot_dist, count(*)
from (select decode (lot_id,0,'ZERO','NONZERO') lot_dist
from ic_lots_mst)
group by lot_dist

LOT_DIST    COUNT
---------------------
ZERO       14,432
NONZERO  2,31,886

SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 0


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 13 K 1734
TABLE ACCESS FULL GMI.IC_LOTS_MST 13 K 604 K 1734


If I pass 0 as literal, CBO performs a FTS on IC_LOTS_MST. Make sense


SELECT LOT_CREATED,
EXPIRE_DATE,
RETEST_DATE,
EXPACTION_DATE,
CREATION_DATE
FROM IC_LOTS_MST
WHERE LOT_ID = 12345

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 4
TABLE ACCESS BY INDEX ROWID GMI.IC_LOTS_MST 1 45 4
INDEX RANGE SCAN GMI.IC_LOTS_MST_I2 1 3


If I pass a non zero as literal, CBO picks a index. Make more sense.

The Findings
When we bounced database, the query in question was executed with a value 0. CBO peeked into the value first time, created a plan with FTS and continued using this. Until we bounced the database again and this time a non zero value was bound.

CUBE function and Pivot Table in SQL

I am currently working on a new OA component, pivot table. To mimic the pivot table in Microsoft Excel, I decided to design the functionality in Java rather than using Oracle's powerful CUBE functionality. I can definitely blog about the reasons behind this decision later, but I am overwhelmed by the power of using CUBE and decided to ramble something about it.

1. Its syntax is very simple
2. Its pretty fast

Illustration:

select e.dept_no, e.job_type, e.salary
from emp_table e

DEPT_NO   JOB_TYPE               SALARY
---------------------------------------
10                Manager         10,000
10                Clerk            3,000
10                Clerk            4,000
20                Manager         12,000
20                Clerk            3,000
 20                Clerk            5,000
20                Foreman          1,000

Say we want to see a tabular information of sum of salary for department wise and again group on it based on job type

select e.dept_no, e.job_type, sum(e.salary)
 from emp_table e
group by cube(e.dept_no, e.job_type)

DEPT_NO   JOB_TYPE   SALARY
 ---------------------------------------
          Manager         22,000
          Clerk           15,000
          Foreman          1,000
10        Manager         10,000
10        Clerk            7,000
 10        Foreman              0
10                        17,000
20        Manager         12,000
 20        Clerk            8,000
  20        Foreman          1,000
 20                        21,000
                          38,000

Now we can use the famous pivoting technique in SQL to get the same information like Excel.

select nvl(job_type, 'Grand Total') JOB_TYPEX
       decode(dept_no,10,max(s),0) DEPT_10,
       decode(dept_no,20,max(s),0) DEPT_20,
       decode(nvl(dept_no,-999),-999,max(s),0) ALL_DEPT
from (select e.dept_no, e.job_type, sum(e.salary) s
from emp_table e
group by cube(e.dept_no, e.job_type))
group by job_type
order by job_type

JOB_TYPEX    DEPT_10   DEPT_20  ALL_DEPT
-----------------------------------------
Clerk         7,000    8,000     15,000
Foreman           0    1,000      1,000
Manager      10,000   12,000     22,000  
Grand Total  17,000   21,000     38,000

But the problem is that, we must know all departments to pivot it.

set serveroutput on by default

DBMS_OUTPUT.PUT_LINE is unavoidable tool for an Oracle developer to debug issues. We must issue a sqlplus comman "SET SERVEROUT ON" to see the output from the above function in our console. By default it is off and we must issue the command explicitly to turn it on. Here is a small trick to turn it on by default.

1. Open login.sql/glogin.sql from SQL_PATH. (Search or grep for that file in the directory you have installed sqlplus.)
2. Add SET SERVEROUT ON command to that file.

Thats it. glogin.sql/login.sql will be executed every time you open a new sqlplus session.

Clearing Profile Cache

Its a royal pain in the **** to bounce the apache everytime you change a profile value, especially during OA page development. Well here is an easy way to clear the profile cache.

1. Login to APPS
2. Choose "Functional Administrator" responsibility
3. Navigate Core Services > Caching Framework > Cache Components
4. Search for %PROFILE%
5. Select "PROFILE_OPTION_CACHE" and "PROFILE_OPTION_CACHE_VALUES".
6. Press Clear Cache button.

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