JDev 12.1.3: Use Default Activity Instead of the Deprecated Invoke Action

Since JDeveloper 12.1.3 the invoke action used in earlier version has been deprecated. Users still using the old invoke action to load data on page load should migrate their code to using the default activity in a bounded task flow instead. This article describes how to use the executeWithParams method as a default activity in a bounded task flow (btf) to load data to be shown in a region. For this we implement a common

Use Case:
in a text field the user enters a string which should be used to look-up data in the DB and show the data as a table in a region.
For this we use the HR schema and build a look-up for locations after the name of the city of the location. In a page the user can insert the name or part of a cities name into a text field. This input is send as parameter to a bounded task flow. The default activity of the btf calls a method in the view object which uses a view criteria to search for cities starting with the given input data. In a second implementation the same technique is used but a where clause is used in the VO and the VO is called with executeWithParams. The result of the search is displayed as a table in a region.

Implementation

Model Project:
We start by creating a new ‘Fusion Web Application’ and creating a model project of the HR DB schema. Here we only use the location table for which we create entity object and view object.
Now we create the view criteria which we use to find locations by part of the city name.

Next step is to create the java class for the view object including the method to safely access the created bind variable. In the class we add a method to apply the created view criteria which we expose in the client interface well as the methods to access bind variables.


Finally we have to make sure that the locations view object is part of the data model of the application module.
Resulting Application Module Data Model

Resulting Application Module Data Model


Next we add another view object to the data model which we use to implement the use case a second time. This time we use the view criteria we defined in the view object LocationsView and select it as the default where clause.

ViewController Project:
We start implementing the view controller project by first adding a start page, ‘Start’, to the unbounded task flow in adfc-config.xml. For this page we use a quick layout (One Column, Header stretched).

After opening the page (which creates it) we add a third grid row to the panelGridLayout we got from the quick layout which later holds the result table. In the first grid row we add a captain for the page, ‘Execute with param sample’, the second grid row we add an af:inputText which holds the users input for the city name to search for.
The page looks like

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE html>
<f:view xmlns:f="http://java.sun.com/jsf/core" xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <af:document title="Start.jsf" id="d1">
        <af:form id="f1">
            <af:panelGridLayout id="pgl1">
                <af:gridRow height="50px" id="gr1">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc1">
                        <!-- Header -->
                        <af:outputText value="ExecuteWithParams Test" id="ot1" inlineStyle="font-size:x-large;"/>
                    </af:gridCell>
                </af:gridRow>
                <af:gridRow height="50px" id="gr2">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
                        <!-- Content -->
                        <af:inputText label="City" id="it1" value="" autoSubmit="true"/>
                    </af:gridCell>
                </af:gridRow>
                <af:gridRow id="gr3">
                    <af:gridCell id="gc3">
                        <!-- REGION HERE -->
                    </af:gridCell>
                </af:gridRow>
            </af:panelGridLayout>
        </af:form>
    </af:document>
</f:view>

Now we create a pageDefinition for the page, where we define a variable and an attribute binding which holds the users input into the inputText we added to a grid row below the header.


The final inputText look like

<af:inputText label="City" id="it1" value="#{bindings.searchCityName1.inputValue}" autoSubmit="true"/>

As you see we set the autoSubmit property to true as we don’t have (and need) a button to submit the data to the binding layer.

The next task is to create a new bounded task flow which has one input parameter, which is used to search for locations with cities starting with the given parameter from the inputText component.

Once the bounded task flow is created we can drag this btf onto the start page and drop it in the girdCell in the third gridRow and wire the parameter for the task flow to the value we have stored in the in the variable iterator via the inputText.

Finally we make the region refresh whenever the inputParamter of the task flow changes by setting the regions refresh property to ‘ifNeeded’.
The final ‘Start’ page layout looks like

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE html>
<f:view xmlns:f="http://java.sun.com/jsf/core" xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
    <af:document title="Start.jsf" id="d1">
        <af:form id="f1">
            <af:panelGridLayout id="pgl1">
                <af:gridRow height="50px" id="gr1">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc1">
                        <!-- Header -->
                        <af:outputText value="ExecuteWithParams Test" id="ot1" inlineStyle="font-size:x-large;"/>
                    </af:gridCell>
                </af:gridRow>
                <af:gridRow height="50px" id="gr2">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
                        <!-- Content -->
                        <af:inputText label="City" id="it1" value="#{bindings.searchCityName1.inputValue}" autoSubmit="true"/>
                    </af:gridCell>
                </af:gridRow>
                <af:gridRow id="gr3">
                    <af:gridCell id="gc3">
                        <af:region value="#{bindings.showlocatiobycitybtf1.regionModel}" id="r1"/>
                    </af:gridCell>
                </af:gridRow>
            </af:panelGridLayout>
        </af:form>
    </af:document>
</f:view>

This concludes the first implementation and we can run the application

The sample application can be downloaded form ADFEMG Sample Project. It contains a second page (Start2) which uses the other view object (LocationsWithParamsView) inside the region. It’s build like the first version. The difference is that the default activity nor is the executeWithParams from the VOs operations instead the self implemented method from the VO. You spare writing the method and exposing the method in the client interface this way.
Be aware that the sample uses the HR DB schema and you have to change the connection information to point to your DB.

Advertisements

Using one ViewObject for Global Lookup Data

A user on the OTN JDeveloper & ADF forum asked how to use one ViewObject, which holds lookup data of different areads, as LOV source. This post shows how to do this.

UPDATE:
In part 2 I added a use case which shows how to use a global lookup view object to initialize a LOV component before a page loads and show the selected lookup data.

Before we begin we have to setup the data for the the sample we build to work with. For this we add two tables to the HR schema (or any other schema you have access to). One, GENERALLOOKUP, hols lookup data, which is grouped ba a type attribute.

General Lookup Data

General Lookup Data


As we see the type column is used to group the data into different areas. The task is to use one view object which queries this table as base for model driven LOVs. To show this we need another table which we use to enter values selected by a LOV for the area from the type column.
Test Table and Data

Test Table and Data

BlogGeneralLokup 018

Scripts to create the tables and insert some data into them are provided together with the sample workspace in the file ‘setup_db.sql’. After we setup the table and the data we create entity objects and view objects like we normally do. Once the eo and vo are created, we add a view criteria which we use to select a specific type of lookup data from the GENERALLOOKUP table.

ViewCriteria to Select Lookup Data

ViewCriteria to Select Lookup Data

The view criteria has one bind variable which we later use to distinguish the different groups of lookup data. Now we can setup the lov for the attributes of the LookupTestView view object. We show this for the TitleId attribute:

Setup LOV for  TitleId Attribute

Setup LOV for TitleId Attribute


Create LOV VO

Create LOV VO


BlogGeneralLokup 003 View Accessors
rename the view object to TiltelookupView andshuttle it to the selected area
Select the GeneralLookupView

Select the GeneralLookupView


Click the edit button on the top right
Edit the LOV View

Edit the LOV View


The vital part is that we switch to the ‘View Object’ node and select the view criteria ‘TypeLookupViewCriteria’ and set the bind variable to the desired type. In this case it is ‘TITLE’
BlogGeneralLokup 006 Edit View Accessor_ TitlelookupView

Select ViewCriteria and Bind Variable

Select ViewCriteria and Bind Variable

Once the list source is setup we select the id of the list source (TitlelookupView) as ‘List Attribute’ and set the TitleId as ‘View Attribute’ for the id. Finally select the ‘UI Hint’ tab and shuttle the ‘Data’ attribute to the ‘Selected’ side. The ‘Data’ attribute of the TitlelookupView will then shown in the listbox on the UI.
BlogGeneralLokup 008 Create List of Values

BlogGeneralLokup 009 Create List of Values

Setup LOV for TitleId

Setup LOV for TitleId

This concludes the implementation for the TitleId attribute. The other attributes (GenderId, PositionId and WeekdayId) are done hte same way. Only change the bind variable ‘bindType’ to the type area you are generating the LOV for.

The gallery above shows the running application. You can download the sample workspace which was build JDeveloper 11.1.1.7.0 and using the HR schema from the ADF EMG Samples side BlogStaticVOLov_V2.zip. You can open the workspace using JDev 11.1.1.6.0 without a problem. If you are asked if you like to migrate the workspace to 11.1.1.6.0 answer with Yes.

UPDATE:
In part 2 I added a use case which shows how to use a global lookup view object to initialize a LOV component before a page loads and show the selected lookup data.

JDeveloper: Fitler Table on Transient Column

This blog entry is based on a question on the JDeveloper and ADF OTN forum. The use case is that a entity (EO) based view object (VO) is shown as a editable table on a page. One column should show a checkbox which is used to select rows on the VO. On an user action, a button pressed in this sample, the selected rows should be displayed in an other table (read only in this case).

We build this use case using JDeveloper 11gR2 (11.1.2.2.0) using the HR schema. As we only want to show how to solve this use case, we only need the ‘Countries’ table. The sample can be downloaded using the link provided at the end of this blog.

There are two problems to solve here:

  1. showing a checkbox to select rows
  2. filter the second table to only show the marked rows

The solution for the first quest is outlined in ADF Code Corner article 99. Multi Table Row Selection for Deferred Delete by Frank Nimphius. We use a transient attribute on the Countries EO, making sure that the transient radio button is selected

Adding a Transient Attribute

Adding a Transient Attribute

We name the new attribute ‘Selected’ and choose Boolean as type for it. Now open the Counties VO and also add an attribute, this time from the EO

Add Attribute to VO from EO

Add Attribute to VO from EO

New Attribute 'Selected' in VO

New Attribute ‘Selected’ in VO

Now open the UI Hints tab and select ‘Check Box’ as ‘Control Type’. You can also add a label to be used for the attribute in the ui.

Set UI Hints

Set UI Hints

This concludes the first quest in the model layer. The second problem is to only show those rows in a second table which are marked (check box selected) in the first table. For the selection we added a new transient attribute ‘Selected’. This boolean attribute we now use to create a view criteria (VC) to filter the row set.
Open the VO CountrieView and select the ‘Query’ node. Add a VC using the green plus sign, name the VC ‘CountriesSelectedVC’ and select the transient ‘Selected’ attribute to equal ‘true’, which is the value the check box get if it’s marked. It’s essential that we set the ‘Query Execution Mode’ to ‘In Memory’ as the VC uses transient data. The framework give you a warning if you don’t obey this rule.

ViewCriteria to Filter Selected Rows

ViewCriteria to Filter Selected Rows

Now that we are able to filter all rows which have the ‘Selected’ attribute set, we have to set up the application modules data model. We use the CountriesView VO to show all rows including the check box to select some or all rows and use the same CountriesView VO, now with the VC applied, to show only the marked rows.
For this we add the CountriesView VO once to the data model as ‘CountriesView1’ and then add the same VO again to the data model as ‘CountriesViewSelected’. To apply the VC ‘CountriesSelectedVC’we defined earlier, we select the ‘Edit’ button in the top right corner of the data model. In the dialog we select the CountriesSelectedVC VC and shuffle it to the selected side. This adds the defined VC as where clause to the VO.

Add VC to VO

Add VC to VO

If you run the model project in the application module tester you’ll see that it works as expected.

The final task is to set up the UI. We use a single page and put a splitter onto a panel stretch layout. The top part shows the editable table with the check box, the lower splitter facet the row which are selected in the upper table. The image below shows the running application

Final Application after Start

Final Application after Start

Select some rows

Some Rows are Selected

Some Rows are Selected

After selecting some rows by marking the check box we need to issue an action to see the result. Click the ‘refresh’ button which is bound to the ‘execute’ method of the CountriesViewSelected view from the Data Control. All left to do is to set up a partial trigger in the table showing the selected records pointing to the ‘refresh’ button.

<af:table value="#{bindings.CountriesViewSelected.collectionModel}" var="row"
          rows="#{bindings.CountriesViewSelected.rangeSize}"
          emptyText="#{bindings.CountriesViewSelected.viewable ? 'No data to display.' : 'Access Denied.'}"
          fetchSize="#{bindings.CountriesViewSelected.rangeSize}" rowBandingInterval="0"
          id="t2" partialTriggers="::ctb1">

Now the final result look like

Final Application

Final Application

You can download the sample workspace, build with JDeveloper 11.1.2.2.0 and depending on the HR db schema, from here: BlogFilterTableOnColumn.zip.doc
Please rename the file to ‘.zip’ after downloading it!

I set up a second version of the sample which uses the toolbar button set to partial submit. At first it looks like the button in the toolbar doesn’t work as there is no change in the selected table section. The problem is that the check box which selects the rows doesn’t auto submit the values. So the query executed by the toolbar button only sees the old marks. If you set the selectBooleanCheckbox autoSubmit property to true it works as expected.
The new version of the sample can be loaded here BlogFilterTableOnColumnV2.zip

JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic

This blog post describes how to use the Application Module Tester provided together with JDeveloper to test all your business logic without having to write or design the client UI (view controller part of a Fusion Web Application).
I consider this a ‘best practice’ to do this in each project. It allows the developer(s) of the business logic to run, debug and test all business logic before the designers have worked out the UI. Changes to interfaces can be done without an impact to the UI this way.

One other advantage of using the Application Module Tester is that it starts up really fast compared to the long init phase the full web application needs.

Before going into detail lets talk about what this blog shows and how it’s implemented. A sample work space build in JDeveloper 11.1.2.1.0 using the HR schema as DB can be downloaded using the link provided at the end of the blog.
First of all you can test the data model you designed into the application module, meaning that you can execute the VO and walk over the result set or show the result as table

Data Model of the Application Module

Data Model of the Application Module

The application module can implement service methods which your later use in the UI to archive a business need. In the sample the application module defines just one service method ‘howManyEmpEarnMoreThen’ which returns the number of employees who are earning more (or equal) to the given amount.

Service Method 'howManyEmpEarnMoreThen'

Service Method 'howManyEmpEarnMoreThen'

and the implementation of this method

    public oracle.jbo.domain.Number howManyEmpEarnMoreThen(oracle.jbo.domain.Number aSalary)
    {
        _logger.info("Call with salary: "+aSalary);
        EmployeesViewImpl lEmployeesView1 = this.getEmployeesView1();
        oracle.jbo.domain.Number n = lEmployeesView1.countEmpWihtSalaly(aSalary);
        return n;
    }

Next a VO might have some ViewCriteria attatched which you also want to test

ViewCriteria to Test

ViewCriteria to Test

and the last thing to test are service methods which are exposed in the VO instead of the application module (‘countEmpWihtSalaly’ in the sample)

Exposed Methods in a View Object

Exposed Methods in a View Object

and the implementation of the method

    public Number countEmpWihtSalaly(Number aSalary)
    {
        RowSet lCreateRowSet = this.createRowSet("counterView");
        ViewCriteriaManager lCriteriaManager = this.getViewCriteriaManager();
        String[] lAvailableViewCriteriaNames = lCriteriaManager.getAvailableViewCriteriaNames();
        for (String vcName :lAvailableViewCriteriaNames)
        {
            this.removeApplyViewCriteriaName(vcName) ;
        }
        lCreateRowSet.ensureVariableManager().setVariableValue("bindSal", aSalary);
        ViewCriteria lCriteria = this.getViewCriteria("EmpSalaryVC");
        this.applyViewCriteria(lCriteria);
        lCreateRowSet.executeQuery();
        long count = lCreateRowSet.getEstimatedRowCount();
        _logger.info("Count Emp wiht salay > " + aSalary + " = " + count);      
        lCreateRowSet.closeRowSet();    
        return new Number(count);
    }

Now let start up the the Application Module Tester and debug the business logic. To start the tester in debug mode, right click the application module facade and select ‘Debug’

Start the Application Module Tester in 'Debug' Mode

Start the Application Module Tester in 'Debug' Mode

which shows the tester like

Running Tester

Running Tester

A double click on a ViewObject (e.g. EmployeesView1) opens one record in form mode. You can navigate the result set using the icons in the header

Navigate Result Set

Navigate Result Set

One of the icons (the field glass) allows you to specify one or more of the available view criteria and hitting the ‘Find’ button after selecting a view criteria opens a dialog to enter bind variables

Specify View Criteria

Specify View Criteria

Specify Bind Variables

Specify Bind Variables

The result of this is again shown as a result set with navigation. If you like to see the result a table you can right click on the VO and select ‘Show Table’.

Show Result as Table

Show Result as Table

So how do we execute the ‘countEmpWihtSalaly’ method which is exposed in the client interface of the VO?
If you look at the image above you’ll see the option ‘Operations’ which will open a new tab showing all available operations which are exposed to the client interface of the VO.

Available Operations

Available Operations

After selecting the method you can hit execute and the method is executed

Result of Operation

Result of Operation

To access the public available methods from the application module you right click on the application module and select ‘Show’. After that you see a tab like the one for the VO operation.

Application Module Operations

Application Module Operations

Result of Operation

Result of Operation

This might not look as much to you, but the advantage of the Application Module Tester is that you can debug the code in the application module methods or view object operation easily. You can set needed variables when calling the methods and use the outcome of the operations in calls to other operations. This way you can easily test the whole business logic without the need to have an UI present.

For more information you can check the Oracle® Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g Release 2 (11.1.2.1.0)

You can download the sample workspace, build with JDev 11.1.2.1.0 and depending on the HR db schema, from here: BlogBC4JTester.zip.doc
Please rename the file to ‘.zip’ after downloading it!

JDeveloper: Case Insensitive Search and Performance

A couple of days ago Frank Nimphius published a new ADF Insider Essential video about Search Forms Customization where he also showed how to implement case insensitive searches. While the tip how to do this is fine, he did not mention the bottleneck involved in doing so. Yesterday, while writing up this blog I came across this blog ‘ADF ViewCriteria performance impact’ by Raman Nanda who summarized the same issue. The last statement in his blog is the starting of this blog:

Note:Also don’t choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .

Lets start with a look on a view criteria definition in JDeveloper 11.1.2.1.0. the image below shows the definition of a simple view criteria to search for employees who’s names start with a given bind variable. When you first add items to the view criteria both check boxes ‘Ignore Case’ and ‘Ignore NULL Values’ are checked.

ViewCriteria Definition: Ignore Case and Ignore Null

ViewCriteria Definition: Ignore Case and Ignore Null


A close look at the ‘View Object Where Clause’ part reveals that the ‘Ignore Case’ part of the query is not visible in JDev 11.1.2.1.0. This is a bug which I’m going to file in the near future. Before going into detail with the query lets uncheck the ‘Ignore NULL Values’ to see the final criteria:
ViewCriteria only 'Ignore Case'

ViewCriteria only 'Ignore Case'


Running the application module in the tester reveals the final query as (copied from the log window)

[104] SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,        
             Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         
             Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID,         
             Employees.ACTION_COMMENT 
             FROM EMPLOYEES Employees 
             WHERE ( ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER( :bindLN || '%') ) ) ) )
[105] Bind params for ViewObject: [de.hahn.blog.vcinsesitivesearch.model.dataaccess.EmployeesView]VCISAppModule.EmployeesView1
[106] Binding param "bindLN": K

Here you see that the ‘Ignore Case’ results in calling SQL UPPER'(…) on the bind parameter as well as on the row attribute. When you copy this query and run it in a SQL worksheet it returns the right results. However, in this bog we are more interested in the execution plan of the query. This is shown in the below image:

Execution Plan Without Index

Execution Plan Without Index


The interesting thing is that the result is reached by a full table scan, which you see as the option is ‘FULL’ for table access. This is not a problem if the table contains only a small number of rows, but if you work on large tables with 10000+ rows it’ll take ages (OK, it’s notable longer then you would expect) to execute.
If you only have defined the normal index on the LAST_NAME column

create index normal_ln_idx on employees (last_name);

the plan doesn’t change at all. This is exactly what Raman Nanda meant in his blog. You need to create a function based index on the LAST_NAME column. Here is the SQL to do so:

create index upper_ln_ix on employees (UPPER(last_name));

Running the query again after creation of the new index results in

Execution Plan with UPPER Index

Execution Plan with UPPER Index


As yo ucan see the table now is accessed via the ‘upper_ln_ix’ index we created. This speeds things up in large tables.

To summarize this blog: you should, as part of your testing, check the execution plans of the queries executed by your application. This can result in a huge improvement of the performance. You should ask your DBA to help you with this task. There are tools readily available to the DBA to help getting information about the queries executed by your application.

Extending ViewCriteria to use SQL CONTAINS

Some time ago a customer asked how to use more sophisticated search operands in a ViewCriteria like Oracles CONTAINS or CATSEARCH functions.
CONTAINS and CATSEARCH are part of the Oracle DB 10g and 11g (Enterprise) and offer full text search capabilities (Oracle Text) for columns (even columns containing pdf or word documents in a blob) and external data. As the customer extensively uses full text search the question was how to integrate this functionality in existing and new to develop ADF rich faces applications. As most of the queries are done using view criteria I looked for a way to integrate the CONTAINS search as operand in the ViewCriteria wizard. A thread on OTN I opened for this issue gave some inside hints on how to try to implement this.

Steve Muenchs hint to add the operator directly in the XML definition of the ViewObject (VO) did not work out. The solution I show in this article is a variation of Jobineshs blog. I overwrite the getCriteriaItemClause(…) method to generate the desired SQL clause and add it to the rest of the query. One problem needed to be solved: how to trigger the use of the CONTAINS in the query. As I mentioned before, putting the CONTAINS as operand into the definition of the VO is to cumbersome as it has to be done in every VO where you want to use the CONTAINS search. In the end I used the bind variable to trigger the special treatment.

Each bind variable provides custom properties which I use as trigger. I use the DESCRIPTION property (you can use your own name) and add CONTAINS as value. The overwritten getCriteriaItemClause(…) method checks for each ViewCriteriaItem if its uses a bind variable with the DESCRIPTION set to CONTAINS. If this is the case I generate the SQL clause for the CONTAINS search.

OK, lets walk through the implementation. First I need to create the CONTAINS index which I want to use in the ViewCriteria. I like the index to be case insensitive, so I need to define a preference which is used to alter the index in this way. As a CONTAINS index doesn’t automatically synchronize I change this behavior too, so that I don’t need to do this by hand. For more info about this kind of index check the Oracle Text Reference.

--create a preference to make the index case insensitive
begin
ctx_ddl.drop_preference('bloglex');
end;
/
begin
ctx_ddl.create_preference('bloglex', 'BASIC_LEXER');
ctx_ddl.set_attribute ('bloglex', 'mixed_case', 'NO');
end;
/
-- create the index with sync on commit 
drop index employee_ln_idx;
create index employee_ln_idx on employees(LAST_NAME) indextype is ctxsys.context PARAMETERS('LEXER bloglex SYNC(ON COMMIT)');

A typical SQL query using this index look like

SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, 'ha%' ) >0) ) ) );

The syntax I need to generate for a CONTAINS SQL where clause looks like: contains(Employees.LAST_NAME, :bindLN ) >0
I defined a ViewCriteria “ContainsLastNameCriteria” for this

ViewCriteria ContainsLastNameCriteria

ViewCriteria ContainsLastNameCriteria


The definition of the bind variable look like
Bind Variable "bindContainsLastName"

Bind Variable "bindContainsLastName"


As you see I choose ‘Equals’ as operator. It does not really matter what I choose, as the query where clause I build will overwrite it anyway. Instead of the default name “DESCRIPTON” you can choose any other name you like, just overwrite the name field in the dialog.
Now using the technique mentioned in Jobineshs blog I overwrite the getCriteriaItemClause(…) method of the ViewObjectImpl class like:

    ADFLogger mLogger = ADFLogger.createADFLogger(EmployeesViewImpl.class);

    /**
     * Check if a given criteria item holds a bind variable with a property DESCRIPTION set to CONTAINS
     * If yes we build a special where clause part to execute a contains search using the the bind variable as
     * parameter.
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
     */
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            // check for the special DESCRIPTION in the used bind variable
            Object obj2 = lBindVariable.getProperty("DESCRIPTION");
            String desc = (obj2 != null? obj2.toString(): "null");
            if ("CONTAINS".equalsIgnoreCase(desc))
            {
                if (aVCI.getViewCriteria().getRootViewCriteria().isCriteriaForQuery())
                {
                    // normal query execution
                    return getCONTAINSClauseForDatabaseUse(aVCI);
                }
                else
                {
                    // for in memory we don't need to anything so just return '1=1'
                    return "1=1";
                }
            }
            else
            {
                // no special treetment for all other CriteriaItems
                return super.getCriteriaItemClause(aVCI);
            }
        }
        return super.getCriteriaItemClause(aVCI);
    }

    protected String getCONTAINSClauseForDatabaseUse(ViewCriteriaItem aVCI)
    {
        ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
        ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
        String whereCluase = "1=1";
        if (itemValue.getIsBindVar())
        {
            Variable lBindVariable = itemValue.getBindVariable();
            Object objVarVal = ensureVariableManager().getVariableValue(lBindVariable.getName());
            String varVal = null;
            if (objVarVal != null)
            {
                varVal = objVarVal.toString();
            }
            else
            {
                // if no parameter given we search for all
                varVal = "%";
            }

            String bindVarName = lBindVariable.getName();
            // can use entiy name only if VO is based on an EO
            String entityName =
                (this.getEntityDefCount() > 0? this.getEntityDef(0).getAliasName() + ".": "");
            whereCluase =
                    "(contains(" + entityName + aVCI.getColumnName() + ", :" + bindVarName + " ) >0) ";
        }
        mLogger.fine("Build clause: " + whereCluase);
        return whereCluase;
    }

That’s about it for the code, lets look at the running sample which you can download from here BlogContainsSearchCriteria.zip. Remove the ‘.doc’ suffix after downloading the sample work space for JDeveloper 11.1.2 as it contains a normal Zip archive.
After the start of the sample app you see a normal QueryPanel with table component which I dragged fro mthe DataContron onto a page fragment. Select ‘ContainsLastNameCriteria’ from the search select list and enter e.g. ‘ha%’ into the LastName input field.

First run using ContainsLastNameCriteria

First run using ContainsLastNameCriteria

The query returns all last names starting with ‘ha’, but also the last name ‘De Haan’ where the ‘ha’ starts the second part of the last name. This is normal expected behavior. This is the output in the log window:

[368] SELECT Employees.EMPLOYEE_ID,
  Employees.FIRST_NAME,
  Employees.LAST_NAME,
  Employees.EMAIL,
  Employees.PHONE_NUMBER,
  Employees.HIRE_DATE,
  Employees.JOB_ID,
  Employees.SALARY,
  Employees.COMMISSION_PCT,
  Employees.MANAGER_ID,
  Employees.DEPARTMENT_ID
FROM EMPLOYEES Employees
WHERE ( ( ( (contains(Employees.LAST_NAME, :bindContainsLastName ) >0) ) ) )
[369] Bind params FOR ViewObject: [de.hahn.blog.containscriteria.model.dataaccess.EmployeesView]BCSCAppModule.EmployeesView1
[370] Binding param "bindContainsLastName": Ha%

Here we see the generated query using the CONTAINS index in the where clause. You may argument that this same result could archived using the normal ‘contains’ operator’ available in the ViewCriteria wizard. The generated where clause in this case looks like

... WHERE ( ( (UPPER(Employees.LAST_NAME) LIKE UPPER('%' || :bindLastName || '%') ) ) )

The big difference is the usage of ‘UPPER’ and the ‘%’ in front and after the bind variable. The usage of ‘UPPER’ is the result of selecting ‘Ignore case’ in the view criteria editor. One disadvantage of using UPPER is, that if you have defined an index on the column (LAST_NAME in this sample), it can only be used if it was created with the UPPER function too. For small data volumes it’s OK to use a query like this without an index, but if you query big volume data it’s an absolute (time) killer.
Next the usage of ‘%’ before the bind variable is problematic too fro big volume data, as the DB needs to read each value to match it against the expression. The result also differs from the desired as names like ‘Kochhar’ are found too.

Normal 'contains' criteria - Wrong result

Normal 'contains' criteria - Wrong result


The very popular web side Ask Tom has plenty of threads discussing this.

The sample workspace contains this criteria and a mixed criteria to show that you can mix normal criteria and the special criteria in one criteria group. Feel free to play with these cafeterias too.

The technique shown here can be used for almost any other SQL where clause which is not part of the ViewCritera editor (e.g. you want to call a pl/sql function as part of the ViewCriteria).