About Timo Hahn

I'm a principal consultant at virtual7 GmbH and work as developer and architect for Java and J2EE applications. Since 2004 I'm working with Oracle tools, starting with JDeveloper on Struts based web application using BC4J JBO Tags, BC4J business components and Oracle DB 10g up to JDeveloper 11.1.2 using ADF Rich Faces and the full ADF technology stack and web services. I'm an active member of hte OTN JDeveloper forum and blog on Oracle, JSF and ADF in special (https://tompeez.wordpress.com). I'm active participant in the local German ADF Community (a group of Oracle partners that strive for improving ADF and FMW sales in Germany and other German speaking countries).

InputNumberSpinbox without Spin

ADF offers a wide range of components which allow user to input data. There is a build in intelligence which chooses the ‚right‘ component for the given data type when you create the UI from a data control. This allows e.g. to create a form to input data which e.g. covers the basic formatting and error handling of the data types for the given fields.

From my point of view, one wrong decision is to use the af:inputNumberSpinbox for Integer and BigInteger data types. Setting a bigger number using the spin boxes isn’t working for most people, at least not for me.

The spin buttons are of no real use in most cases. In some versions of JDev the buttons are skinned too small so that it’s hard to use them at all. There are some cases, when the range of numbers is minimal, where using the spin buttons is OK.

What I like about the component is the build in error handling if I try to input anything but a number

without doing anything to the component. This is the code used for the above image

<af:inputNumberSpinbox label="Spinnumber" id="ins2"

As you see there in nothing but the component, still we get the right error message.

You can get the same result by using a normal af:inputText with an included af:numberConverter, but you need to know how to do this:

<af:inputText label="Number in af:inputText" id="it1"
    <af:convertNumber type="number" id="nc1" pattern="0"/>

This doesn’t look identical but close enough. One difference to note is that the af:inputText starts the input on the left whereas the af:inputNumberSpinbox aligns the numbers to the right. You can change this too with setting more properties on the component.

For this I like to use the af:inputNumberSpinbox without the spin buttons.

To make the af:inputNumberSpinbox usable I get rid of the spin buttons:

The component works like hte one with the spin buttons but look like a normal inputText

This can be done by changing the skin. If you like it can be done globally or you define a custom skin class and add this class where you don’t want to see the spin butons:

.nospin af|inputNumberSpinbox::incrementor-icon-style {
    display: none;

.nospin af|inputNumberSpinbox::decrementor-icon-style {
    display: none;

The ‘.nospin‘ is the name of the custom style class you can use on the af:inputNumberSpinbox to turn the spin buttons off.

Here is the part of the page

<af:panelGroupLayout id="pgl2" layout="vertical" inlineStyle="padding-left:20px;">
    <af:inputNumberSpinbox label="Number" id="ins1" value="#{bindings.myNumber1.inputValue}" styleClass="nospin"/>
    <af:spacer width="10" height="30" id="s1"/>
    <af:inputNumberSpinbox label="Spinnumber" id="ins2"
    <af:spacer width="10" height="30" id="s2"/>
    <af:inputText label="Number in af:inputText" id="it1">
       <af:convertNumber type="number" id="nc1" pattern="0"/>
    <af:spacer width="10" height="30" id="s3"/>
    <af:button text="Submit" id="b1"/>

You can download the sample from GitHub BlogInputSpinBoxWithoutSpin. The sample was built with JDeveloper but should work with other versions too. There is no DB used or needed to run the sample.


Jdev 12c: Implementing SQL IN Clause in an ADF ViewObject Query or ViewCriteria (Part 2)

In part one, showed how to implement a SQL IN clause in ADF. Now I show how to use this technique in a ViewCriteria or directly in a query of a view object.

We have to solve a couple of problems before we can really use the technique from part one in a ViewCriteria. As you know, when using a ViewCriteria, you select an operator which in turn is translated into SQL code. So, we have to find a way to create a new operator which will then be used to create the needed SQL code.

The technique to do this comes from an older post. Please look at Extending ViewCriteria to use SQL CONTAINS where I showed the basics on how to do this. The older post was designed for JDeveloper Using the current JDeveloper version give some ways for improvement of the earlier code.

The first improvement is that JDeveloper allows us to introduce custom operators to view criteria. In the older sample, I had to use the description field of the ViewCriteria to pass information which SQL to generate. Now we can define an operator named ‘IN’ and use it in the ViewCriteria like any other default operator.

The next problem is how to generate the SQL shown in part one when the new custom operator ‘IN’ should be used. One of the big advantages of ADF is reusability. We use a base class which extends from ViewObjectImpl and use this new base class in the project.

The base class is named BaseViewObjectForSqlInClause were we implement the needed method

public String getCriteriaItemClause(ViewCriteriaItem aVCI)

which gets called for each part or item of a ViewCriteria. See the code of hte base class below.

Base Class

public class BaseViewObjectForSqlInClause extends ViewObjectImpl {
    private static ADFLogger _logger = ADFLogger.createADFLogger(BaseViewObjectForSqlInClause.class);

    // comma-separated list of custom operators. Each custom operator muast have a ',' at the end as delimeter!
    private static final String CUSTOM_OPERATORS = "IN,";

    public BaseViewObjectForSqlInClause(String string, ViewDefImpl viewDefImpl) {
        super(string, viewDefImpl);

    public BaseViewObjectForSqlInClause() {

     * Check if a given criteria item tries to use an 'IN' operator using a bind parameter (comma seperated list of strings).
     * Create special SQL clause for 'IN' operator
     * @param aVCI Criteria item
     * @return where clause part for the criteria item
    public String getCriteriaItemClause(ViewCriteriaItem aVCI) {
        // we only handle the SQL 'IN' operator
        String sqloperator = aVCI.getOperator();
        // add comma to operator as delimiter
        boolean customOp = CUSTOM_OPERATORS.indexOf(sqloperator.concat(",")) >= 0;
        customOp |= sqloperator.indexOf("NVL") >= 0;
        if (customOp) {
            ArrayList<ViewCriteriaItemValue> lArrayList = aVCI.getValues();
            if (lArrayList != null && !lArrayList.isEmpty()) {
                // check if the criteria item has bind parameters (only the first if of interest here as the IN clause onlyallows one parameter)
                ViewCriteriaItemValue itemValue = (ViewCriteriaItemValue) lArrayList.get(0);
                if (itemValue.getIsBindVar()) {
                    // get variable and check if null values should be ignored for bind parameters
                    Variable lBindVariable = itemValue.getBindVariable();
                    Object obj = ensureVariableManager().getVariableValue(lBindVariable.getName());
                    boolean b = aVCI.isGenerateIsNullClauseForBindVariables();
                    if (b && obj == null) {
                        // if null values for bind variables should be ignored, use the default getCriteriaItemClause
                        return super.getCriteriaItemClause(aVCI);

                    try {
                        // we only handle strings data types for bind variables
                        String val = (String) obj;
                    } catch (Exception e) {
                        // the bind variabel has the wrong type! Only Strings are allowed
                        _logger.warning("Bind variabel for SQL " + sqloperator +
                                        " clause is not of type String! -> No custom SQL clause created! (Class: " +
                                        obj.getClass() + ", Content: " + obj + ", Variable: " +
                                        lBindVariable.getName() + ", View: " + this.getName() + ")");
                        String s = ":" + lBindVariable.getName() + " = :" + lBindVariable.getName();
                        return s;

                    // only handle queries send to the db
                    if (aVCI.getViewCriteria()
                            .isCriteriaForQuery()) {
                        String sql_clause = null;
                        switch (sqloperator) {
                        case "IN":
                            sql_clause = createINClause(aVCI, lBindVariable);
                            _logger.severe("Unknown custom operator '" + sqloperator + "' found! -> do nothing!");

                        return sql_clause;
                    } else {
                        // bind variable not set or
                        // for in memory we don't need to anything so just return '1=1'
                        return "1=1";

        return super.getCriteriaItemClause(aVCI);

    private String createINClause(ViewCriteriaItem aVCI, Variable lBindVariable) {
        // start build the sql 'IN' where clause (COLUMN is the name of the column, bindParam the name of the bind variable):
        // COLUMN IN (SELECT regexp_substr(:bindParam,'[^,]+',1,level) FROM dual CONNECT BY regexp_substr(:bindParam,'[^,]+',1,level) IS NOT NULL
        // get flagg to create an sql where clause which ignores the case of the bind parameter
        boolean upper = aVCI.isUpperColumns();
        String sql_in_clause = null;
        StringBuilder sql = new StringBuilder();
        if (upper) {
        if (upper) {
        sql.append(" ").append(aVCI.getOperator());
        sql.append(" (select regexp_substr(");
        if (upper) {
        if (upper) {
        sql.append(",'[^,]+', 1, level) from dual connect by regexp_substr(");
        if (upper) {
        if (upper) {
        sql.append(", '[^,]+', 1, level) is not null)");
        sql_in_clause = sql.toString();

        _logger.finest("generated SQL-IN clause: " + sql_in_clause);

        return sql_in_clause;

Using Base Class in Project

To use the base class in all new created ViewObjects of the project, we change the models project properties

Now, whenever you create a new ViewObject, the new base class is used and the SQL IN operator can be used in the VOs view criteria.

You can change any existing ViewObject to use the BaseViewObjectForSqlInClause by changing the extends clause in the class definition by hand.

Creating a ViewCriteria Using the Custom IN Operator

All pieces are in place and using the IN operator is pretty easy. We start by creating a new ViewObject named EmployeesOfDepartmentsViewCriteria

Now we have a ViewObject based on an EntityObject for the Employees. We need to make one change. The DepartmentId is an Integer type attribute, the comma-separated list is of type String (containing numbers). This doesn’t match. We add another attribute to the ViewObject of type String which we calculate from the DepartmentId Integer attribute. We change the SQL query for this by selecting the ‘Query’ node first unselecting the checkbox ‘Calculate Department Query at Runtime (recommended)’, second select the checkbox ‘Write Custom SQL’ and third add the line ‘to_char(Employees.DEPARTMENT_ID) DEPARTMENT_ID_STR,’ to the query.

Once this new ViewObject has been created, we add a ViewCriteria to it

If you like, you can turn off the checkbox ‘Ignore Case’ as it is not needed. The numbers are always lower case.

Running the ApplicationModule in the Tester

At this stage, we can test run the application module in the Application Module Tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic).

Click the binocular button to select the ViewCriteria we created and click ‘Find’

Which will open a dialog asking for the value of the bind variable

Clicking ‘OK’ will show the result as

Running the ViewCriteria on a Page

Finally, we can add the ViewCriteria to a page as af:query and test it there. I’ll spare the exact howto here and just show hte running application.

Or with different parameters and spaces

You can download the sample application from GitHub BlogSqlInClause.

The Sample was built using JDeveloper (but it should work in all 12c versions) and uses the HR DB schema.

JDev 12c: Implementing SQL IN Clause in an ADF ViewObject Query or ViewCriteria (Part 1)

There have been numerous questions about how to implement a SQL IN clause in ADF using a viewCriteria since the begin of life of ADF. There are a couple of solutions e.g. using an SQL array type or a DB table to store the values of the IN clause.

I came up with another solution which was using Oracle DBs CATSEARCH function or even CONTAINS search index.

All those solutions are more or less complex and need some programming to implement.

The solution I present in this blog is easy and elegant. However, it has its limitations still. Anyway, for about 90% of the use cases, I know where you want to add an SQL IN clause it works perfectly.


The problem is that you can’t simply define an IN clause in a query or ViewCriteria like

Select * from Employees where employee_id in (:pListOfValues)

Using this select statement as a query for a view object will not throw an error, but it won’t get you the desired result.

If you use a String type parameter for ‘pListOfValues’, e.g. “100, 110, 200” the query in the VO would look like

Select * from Employees where employee_id in (“100,110,200”)

And return nothing as a result. It is easy to see why: the parameter is expanded as a string, not a list of numbers. If you think you could overcome this by converting the employee_id to a string it will still not work as “100” IN “100, 110, 200” still won’t work.


A very elegant way to solve this problem is to change the query or where clause to

    SELECT regexp_substr(:pListOfValues,'[^,]+',1,level) FROM dual
      CONNECT BY regexp_substr(:pListOfValues,'[^,]+',1,level) 
        IS NOT NULL)

The work is done by the select statement in the IN clause, This statement will split a comma-separated string in a series of values like a sub-select. If you run

    regexp_substr(:pListOfvalues, '[^,]+', 1, level)
    regexp_substr(:pListOfvalues, '[^,]+', 1, level) IS NOT NULL

In a SQL worksheet and pass ‘11,12,15,17’ as ‘pListOfValues’ you get

You can pass any comma-separated string e.g. “1, hello, 444, world” and get

The SQL builds an internal table and add the values delimited by a comma to it. This internal table can then be used in the IN clause of another SQL statement.

Finally, running a complete query we can search e.g. for the employees which have the ID 100 or 110, or 180 or 176

In part 2, I’ll show how to implement this kind of query in a ViewCriteria of a ViewObject by adding a custom operator.

JDev Multi select component table filter

In this blog article, I show how to use a multi-select component as a filter in a table. The sample is based on an older sample from Frank Nimphius (98. How-to use multi select components in table filters). The sample was built for JDev 11g R1 and R2.

It works using 12c too, but you get deprecation warnings after the migration. A user on the JDeveloper & ADF forum asked if I could provide a sample running in 12c without the deprecation warnings.

I will only show how to rewrite the bean method which is called when the user enters one or more values in the filter. The remaining part of the original sample works without a change in 12c.

To better understand what I’m talking about I show some images from the original blog:

The image above shows the sample table. Below we see the multi-select component to filter for multiple departments:

Please read the original blog entry to understand how to build the UI. The remaining part of this blog covers how to build the custom query listener method.

Custom Query Listener

In the original sample the two methods

Map _criteriaMap = fqd.getFilterCriteria();

are used which produce deprecation warnings in 12c

Starting from JDev 12.1.3 you can’t use the criteriaMap from the FilterableQueryDescriptor. Instead, you have to first get the ConjunctionCriterion from the FilterableQueryDescriptor and get the map of Criterion from it. The map holds the filter criteria entered by the user.

As you don’t use the criteria directly, you can’t set it back after generating the filter fro the multi-select. You work with the Criterion instead.

The new Method looks like

     * Custom Query Listener.
     * Applies af:selectMany choice values to the table filter criterion
     * @param queryEvent
    public void onEmployeeTableQuery(QueryEvent queryEvent) {
        //user selected values
        ArrayList<Object> departmentIdArray = null;
        FilterableQueryDescriptor fqd = (FilterableQueryDescriptor) queryEvent.getDescriptor();

        //current criteria
        ConjunctionCriterion conjunctionCriterion = fqd.getFilterConjunctionCriterion();
        Map<String, Criterion> criterionMap = conjunctionCriterion.getCriterionMap();
        Criterion criterion = criterionMap.get("DepartmentId");

        //Translate DepartmentId array list to OR separate list of values
        StringBuffer deptIdFilterString = new StringBuffer();
        AttributeCriterion adfcriterion = null;
        // flag we set only if the DepartmentId filter is set (to reset the selection later)
        boolean flagDepIdFilter = false;
        if (criterion != null) {
            adfcriterion = (AttributeCriterion) criterion;
            Object object = adfcriterion.getValue();
            if (object != null) {
                flagDepIdFilter = true;
                departmentIdArray = (ArrayList<Object>) object;

                for (int argIndex = 0; argIndex < departmentIdArray.size(); argIndex++) {

                    //You need to know what is the underlying data type you are dealing
                    //with for the attribute. If you are on 11gR1 (11.1.1.x) then this
                    //type is jbo.domain.Number for numeric attributes.
                    //If you are on 11g R2 (11.1.2.x) this could be oracle.jbo.domain.Number,
                    //Integer or BigDecimal. If you use 11g R2, check the View Object for the
                    //attribute data type

                    if (argIndex == 0) {
                        //first argument has no OR

                        //this sample used oracle.jbo.domain.Number for the
                        //DepartmentId attribute
                        Number departmentId = (Number) departmentIdArray.get(argIndex);
                    } else {
                        //any subsequent argument is OR'ed together
                        deptIdFilterString.append(" OR ");
                        Number departmentId = (Number) departmentIdArray.get(argIndex);
                //for some reasons, if in a single value select case, the
                //filter breaks and an error message is printed that the
                //String representation of the single value isn't found in
                //the list. The line below fixes the problem for filter values
                //that are positive numbers
                deptIdFilterString.append(" OR -1");
                String departmentIds = deptIdFilterString.toString();

        // preserve default query listener behavior

        FacesContext fctx = FacesContext.getCurrentInstance();
        Application application = fctx.getApplication();
        ExpressionFactory expressionFactory = application.getExpressionFactory();
        ELContext elctx = fctx.getELContext();

        MethodExpression methodExpression =
            expressionFactory.createMethodExpression(elctx, "#{bindings.allEmployeesQuery.processQuery}", Object.class,
                                                     new Class[] { QueryEvent.class });
        methodExpression.invoke(elctx, new Object[] { queryEvent });

        //restore filter selection done by the user. Note that this
        //needs to be saved as an ArrayList
        if (flagDepIdFilter) {

From the FilterableQueryDescriptor we get the ConjunctionCriterion and from this the map of Criterion. This map holds all filter values entered by the user in the filter of the table. We retrieve the one for the ‘DepartmentId’ and check if the value for it is not null. In this case, the criterion holds an array of the selected DepartmentId. From this array, we build a new string where we use the ‘OR’ operator to concatenate the array values.

Once this string is built, we set it back to the Criterion and execute the original query listener

You can download the sample from GtHubBlogMultiSelectComponentFilterTable for inspection and/or testing. The sample was built using JDev and uses the HR DB schema.

JDeveloper: executeWithParams Problem: bind variable setters are not called

In one of my current bigger ADF projects (yes, there are still big ADF projects!) I had a problem with the ‘executeWithParams’ operation binding called from a bean. To understand the problem I first give a brief description.


We have a special string column in some of our VOs. For this column, there is a bind parameter which is added to the where clause of the VO. Getter and setter for this bind parameters are generated to be able to set this parameter safely. In the setter method, some checks are done and special formatting is applied to the passed value.

Now we used the VOs executeWithParams operation to create a search form. Hitting the button to execute the query gave us a wrong result. As it turned out, that the execWithParams operation passes the parameters to the VO but it doesn’t use the getter/setter methods for the parameter. The parameters are directly set into the slots by the VariableManager.


We want to transform or convert the given parameter right in the query. This way the developer doesn’t need to think about or to remember that there should be a transformation of the parameter. A central solution is preferred. Two solutions come to mind.

Expose a method in the VO

One solution would be to create a method in the VO and expose this method in the client interface of the VO. Then this method is visible in the binding layer and you can use call the method instead of using executeWithParams.

This works OK, but if a developer uses the executeWithParams method, you’ll still don’t get the right result.

Change the behavior of the executeWithParams method

I looked for a direct hook into the executeWithParams method but did not find one 😦

However, it turned out that the executeWithParam method calls the executeQuery method of the VO. At this point, the parameters are all set. This is done in the background via the VariableManager used in VOs to keep track of the bind parameters and their different type. There are where clause parameters and parameters used in view criteria. The latter are handled differently but this is not essential for this solution. All we need to know is that the parameters are correctly set when the executeQuery method is called.

The solution is to overwrite the executeQuery method of the view object (or use a base class) and check the parameters before executing the query.

Implementing the solution

In an earlier blog “Dump VO query and it’s parameter with their values” I showed how to get to the parameters defined for a VO and dump their values. Instead of writing the values to the log file, we look for the parameter, change the value, if one is given and then execute the query with the now converted parameter.

I use reflection to check if a setter method for a parameter is defined in the VO. If this is the case, I call the setter with the value set by the user. This way the setter is called when the query is called via the executeWithParams method. The code to change the parameter has only to be written once in the setter.

Below is the code:

    public void executeQuery() {

    private void transformHistoryParameter() {
        VariableValueManager vm = ensureVariableManager();
        Variable[] variables = vm.getVariables();
        // check each parameter for this query
        for (Variable var : variables) {
            String varName = var.getName();
            try {
                String mName = "set" + varName;
                Class mvarType = var.getJavaType();
                Method m = this.getClass().getMethod(mName, new Class[] { mvarType });
                // check if method is a setter method
                if (isSetter(m)) {
                    Object val = vm.getVariableValue(var);
                    m.invoke(this, val);
            } catch (NoSuchMethodException e) {
                // nothing to do if there is no setter defined
            } catch (IllegalAccessException | InvocationTargetException e) {
                // nothing to do if there is no setter defined

    public boolean isGetter(Method method) {
        if (!method.getName().startsWith("get")) {
            return false;
        if (method.getParameterTypes().length != 0) {
            return false;
        if (void.class.equals(method.getReturnType())) {
            return false;
        return true;

    public boolean isSetter(Method method) {
        if (!method.getName().startsWith("set")) {
            return false;
        if (method.getParameterTypes().length != 1) {
            return false;
        return true;

    public void dumpQueryAndParameters() {
        // get the query in it's current state
        String lQuery = getQuery();
        // Dump query
        _logger.info("---query--- " + lQuery);
        //get Valriables
        AttributeList attributeList = getNamedWhereClauseParams();
        String[] attributeNames = attributeList.getAttributeNames();
        if (attributeNames == null || attributeNames.length == 0) {
            _logger.info("--- No variables found");
        } else {
            for (int ii = 0; ii < attributeNames.length; ii++) {
                Object lObject = attributeList.getAttribute(ii);
                _logger.info("  --- Name: " + attributeNames[ii] + " Value: " +
                             (lObject != null ? lObject.toString() : "null"));


The sample to see the code at work can be downloaded from GitHub BlogExecuteWithParamsProblem

It was implemented using JDeveloper and the HR DB schema.

To make it easy, I used the HR DB schema and built a query on the EMPLOYEES table:

A forth bind variable is introduced in a view criteria

The transformation uses the pMail bind parameter and makes it uppercase.

     * Returns the bind variable value for pMail.
     * @return bind variable value for pMail
    public String getpMail() {
        String val = (String) getNamedWhereClauseParam("pMail");
        _logger.info("value: " + val);
        return val;

     * Sets <code>value</code> for bind variable pMail.
     * @param value value to bind as pMail
    public void setpMail(String value) {
        _logger.info("value:" + value);
        String newVal = value;
        if (value != null) {
            newVal = value.toUpperCase();
            _logger.info("Value transformed to " + newVal);
        setNamedWhereClauseParam("pMail", newVal);

I know that it would be easy to without this code by just setting the ‘ignore case’ flag when defining the query, but this is just a sample of a change of a parameter.

The data model in the application module looks like

As you see I added a second instance of the EmployeesView and attached the view criteria to this instance. Running the application module in the tester (see JDeveloper & ADF: Use the Application Module Tester (BC4J Tester) to Test all your BusinessLogic)

We get

And the following log

Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl executeQuery
INFO: executeQuery
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpMail
INFO: value:ski
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpMail
INFO: Value transformed to SKI

Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpName
INFO: value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl setpDate
INFO: value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: —query— 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 ( ( ( ( Employees.LAST_NAME LIKE ( :pName || ‘%’) ) OR ( :pName IS NULL ) ) AND ( ( Employees.EMAIL LIKE ( :pMail || ‘%’) ) OR ( :pMail IS NULL ) ) AND ( ( Employees.HIRE_DATE >= :pDate ) OR ( :pDate IS NULL ) ) ) )
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: —Variables:
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pMail Value: SK
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pName Value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pDate Value: null
Feb 08, 2019 3:05:38 PM de.hahn.blog.execwithparams.model.EmployeesViewImpl dumpQueryAndParameters
INFO: — Name: pSalary Value: null

We see, that executeQuery() is called, then setpMail is called and the value given in the UI is converted to uppercase. In the dump of the query, we see that the uppercase parameter is used for the query.

The same works with the other view object in the application module. Just try it out yourself. The sample has a small UI project allowing you to test it in the browser too.

JDeveloper 12c: using Expression Language in pageDef to switch ControllerClass

An interesting question came up late 2018 in the JDeveloper & ADF forum. A user asked how to use Expression Language (EL) in a pageDef file to switch the ControllerClass at runtime depending on some condition of a page.

The ControllerClass can be used to add custom code into the lifecycle of a page or fragment (see the full details at 27.4 Customizing the ADF Page Lifecycle). A tip

You can specify the value of the page definition’s ControllerClass attribute as a fully qualified class name or you can enter an EL expression that resolves to a class directly in the ControllerClass field.
When using an EL expression for the value of the ControllerClass attribute, the Structure window may show a warning indicating that e “#{YourExpression}” is not a valid class. You can safely ignore this warning.

given in the documentation mentioned that you can use EL to specify the ControllerClass. The missing information is exactly how to do it.

Use Case

For a JSF page, a ControllerClass should be defined at runtime. The selected ControllerClass should depend on a condition.


There are a couple of blogs available which use a custom ControllerClass in a pageDef, but they use the direct specification of the custom class in the pageDef. Only one sample (https://github.com/oracle/adf-samples/releases/download/v1.0.0/OnPageLoad.zip) from Duncan Mills uses EL to set the ControllerClass. However, this sample was built for JDev 10.3.1! None of the samples I found use EL to switch the ControllerClass at runtime.

In summary, it’s time for a fresh sample using JDev

Building the UI

We start by creating a new Fusion Web Application from the gallery. The steps to follow can be looked up at Why and how to write reproducible test cases, so I skip them here.

Once the basic Fusion Web Application is built we open the adfc-config.xml (the unbounded ADF task flow) and add a page onto it.

We name the page index and create it using a Quick Layout. I normally use

but you can use whatever layout like. We add a Text to the header section and an af:inputText and an af:button to the content section. The page markup will look 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="index.jsf" id="d1">
        <af:form id="f1">
            <af:panelGridLayout id="pgl1">
                <af:gridRow height="50px" id="gr2">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc1">
                        <!-- Header -->
                        <af:outputText value="Using EL to switch ControllerClass" id="ot1"
                <af:gridRow height="100%" id="gr1">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
                        <!-- Content -->
                        <af:panelGroupLayout id="pgl2" layout="horizontal">
                            <af:inputText label="Use Listener" id="it1"/>
                            <af:button text="Update" id="b1"/>

Next, we open the properties of the af:button and edit the actionListener property. Here we create a new bean, name it ‘IndexBean’, create a new method ‘updatePage’. The bean we create in sessionScope. Once the bean has been created, we delete the actionListener from the af:button we just created. The ‘updatePage’ method in the IndexBean we delete too. We don’t need the listener and I only used it to create the bean in the right scope for other things.

I created the bean in session scope as I will use it to store the data from the inputText we added to the page. This is just a convenience, we could have used a pageDef variable for this.

We add a String property ‘usePPListener’ to the IndexBean add the needed getter and setter methods.

public class IndexBean {
    String usePPListener = "1";

    public IndexBean() {

    public void setUsePPListener(String usePPListener) {
        this.usePPListener = usePPListener;

    public String getUsePPListener() {
        return usePPListener;

We set the default value of the ‘usePPListener’ to “1”. We set the ‘usePPListener’ to the value property of the af:inputText field of the page. And set the autoSubmit property of the af:inputText to true. The page markup for the content:

<af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
    <!-- Content -->
    <af:panelGroupLayout id="pgl2" layout="horizontal">
        <af:inputText label="Use Listener" id="it1" value="#{IndexBean.usePPListener}"
        <af:button text="Update" id="b1"/>

Running the application at this stage shows

Creating the PagePhaseListener Class

Now we can go on and create the custom PagePhaseListener classes which we then use to switch using an EL. To create such a custom class, the documentation tells us that all we have to do is to create a class which implements the ‘PagePhaseListener’ interface.

This will create the first PagePhaseListener named ‘MyPagePhaseListenerA’. The resulting class looks like

package de.hahn.blog.elpagedef.view.beans;

import oracle.adf.controller.v2.lifecycle.PagePhaseEvent;
import oracle.adf.controller.v2.lifecycle.PagePhaseListener;

public class MyPagePhaseListenerA implements PagePhaseListener {
    public MyPagePhaseListenerA() {
        System.out.println("MyPagePhaseListenerA created");

    public void afterPhase(PagePhaseEvent pagePhaseEvent) {
        // TODO Implement this method
        System.out.println("MyPagePhaseListenerA afterPhase called");

    public void beforePhase(PagePhaseEvent pagePhaseEvent) {
        // TODO Implement this method
        System.out.println("MyPagePhaseListenerA beforePhase called");

We do it again but name the class ‘MyPagePhaseListenerB’. As you see I added some System.out.println(“…”) statements so that we can see which PagePhaseListener is used later.

Using EL in pageDef.xml

Finally, we start with the interesting part, the EL to use in the pageDef ControllerClass property to switch the listener. The use case demands that we switch the PagePhaseListener depending on a condition. The condition we use is pretty simple:

  • Use MyPagePhaseListenerA if the inputText value stored in usePPListener is equal to “1”
  • Use MyPagePhaseListenerB if the inputText value stored in usePPListener is equal to “2”

We have two options to make the decision,

  1. directly in EL in the pageDef
  2. use a method in a bean where we check the condition

The essential part to know is the type of result both options need to return to work. The ControllerClass expects an object of type PagePhaseListener, or a class which implements this interface.

Solution 1

The classes we created before are implementing this interface, so they should work. To implement option 1 we need to need to instantiate an object of one of the classes and return it on the EL like in the pageDef.xml:

ControllerClass=”#{IndexBean.usePPListener eq ‘1’ ? MyPagePhaseListenerA : MyPagePhaseListenerB}”

And we need to register the bean named ‘MyPagePhaseListenerA’ and ‘MyPagePhaseListenerA’ in the task flow. This will instantiate the object when the task flows starts.

Running this application this way we get

Entering ‘2’ into the af:inputText and clicking ‘Update’ we get

Heureka! The PagePhaseListener have switched as expected.

Solution 2

We implement a method in a bean which returns the right PagePhaseListener class. The EL in the pageDef.xml looks like


The method getPagePhaseListener2Use() we implement in the IndexBean as

public PagePhaseListener getPagePhaseListener2Use() {
    if (getUsePPListener() != null && getUsePPListener().equals("2")) {
        return new MyPagePhaseListenerB();
    } else {
        return new MyPagePhaseListenerA();

The method returns the PagePhaseListener interface instead of the real class object. This is necessary as a method can only return one object type.

Running the application in this configuration results in exactly the same output we saw before.

Using EL in PageDef for Fragments used in Regions

If you plan to use this technique for fragments which run in regions you would need to return a RegionController instead of a PagePHaseListener. However, as it turned out, you can’t use EL at all in a pageDef of a fragment!

The problem is, that the framework simply doesn’t evaluate the EL you specify for the ControllerClass of a fragment, but uses the EL as the class name. This results in a ‘ClassNotFoundException’ as there is no class named ‘#{your_EL}’.

I’m not sure if this is a bug or a feature. I could not think of a valid use case to use EL for a fragment pageDef.

If you do have one, share it in the comments to this post, please.

If you really think you need to switch the behavior for a RegionController at runtime, you can create one Class which implements the different in the overwritten methods and decide which part to execute inside the method.


You can download the sample application, which was built using JDev and no DB connection, from GitHub BlogELPageDef

JDeveloper: Creating a FULL OUTER JOIN View Object

On my todo list, I found a topic which I wanted to blog about for a long time. The problem is how to create a ViewObject, based on EntityObjects, which builds a full outer join between two tables.

For those of you who don’t know about full outer joins in SQL here is a short description from https://www.w3schools.com/sql/sql_join_full.asp:

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!


SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Image to visualize a full outer join

There are not too many use cases where you need to use a full outer join, but they exist (e.g. https://searchoracle.techtarget.com/answer/Another-good-FULL-OUTER-JOIN-example or to compare two or more tables).

Problem: How can a full outer join be created in ADFbc?

I show how to create a VO based on Employees and Department EO using a full outer join on the department_id. This VO will return all departments with all their employees, departments which don’t have any employee and all employees who don’t have a department.

Following the syntax from above, we use an SQL statement like

ON Departments.department_id = Employees.department_id
ORDER BY Departments.department_id, Employees.last_name;

There are other SQL statements which produce the same result like

  (SELECT d.department_id AS d_dept_id,
     e.department_id AS e_dept_id,
     e.last_name last_name,
   FROM departments d
   LEFT OUTER JOIN employees e
   ON d.department_id = e.department_id
   SELECT d.department_id AS d_dept_id,
     e.department_id AS e_dept_id,
   FROM departments d
   RIGHT OUTER JOIN employees e
   ON d.department_id = e.department_id
ORDER BY d_dept_id, last_name;

The statement combines a left outer join with a right outer join. The ‘Select distinct….’ is used to eliminate duplicate rows which are returned for both joins. Anyway, the results are equal.


Now we can build the view object based on the two entity objects (Departments and Employees). We start by creating a new view object

and fill in the name as ‘DepEmpViewObj’. Make sure you select ‘Entity’ as ‘Data Source’

On the next wizard page shuttle the Departments and the Employees entities to the right

Now select the Departments entity and you get

Selecting the Employees entity you get

This we have to change as the join type is ‘inner join’ and not what we like to do. If you select the drop down menu you see

Hm, there is no ‘full outer join’ as joint type. We can’t create this type of join declaratively, we have to do this directly with a SQL statement. So, drop down the ‘Association’ field and select ‘none’

The final definition is

On the next page select the attributes

We don’t change anything in step 4 so we go to step 5. Here uncheck the ‘Calculate…’ checkbox and select the ‘Write Custom SQL’

Now we copy the SQL statement from above and copy it into the text area after deleting the current statement. Don’t forget to delete the ‘order by…’ part from the ‘Select:’ text area and add them into the ‘Order By:’ text field

We skip the steps 6,7 and 8 and add the view object to the application module in step 9

Finally, we finish the wizard and are ready to test the view object.

Running solution

Running the application module in the tester show the resulting table (only the last ~40 rows are shown)

We see departments without employees and we have one employee (see the last row) without an assigned department. All as expected.

To complete the application we add the new DepEmpViewObj onto a page as a table. Running it we get the same result as in the tester.

You can download the sample from GitHub BlogFullOuterJoin. The sample was built using JDeveloper and uses the HR DB. The same technique can be used with other JDeveloper versions too.

JDev: af:panelList without bullet if no link is given

We all know that ADF components are well defined and have a lot of functions. However, what if we want to use a component but don’t like what we get out of the box from it?

The answer is easy most of the times as we can change the look of the component or its behavior to our needs. Sometimes the answer is not as straightforward, but still easy, as in this

Use Case

A user wants to have an af:panelList, showing bullets in front of each item in the list. The Items should be links to other pages. The problem part is that some of the links in the list should not be visible all the time. E.g. a user might not have to needed access right to some of the links.


When we use an af:panelList as is, we get the following look

From this setup

<?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="panelList.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:gridRow height="100%" id="gr2">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
                        <af:panelList rows="5"> 
                            <af:link text="link 1" id="l1" destination="http://www.oracle.com"/>
                            <af:link text="link 2" id="l2" destination="http://www.oracle.com"/>
                            <af:link text="link 3" id="l3" destination="http://www.oracle.com"/>
                            <af:link text="link 4" id="l4" destination="http://www.oracle.com"/>

Setting e.g. link 3 to not visible we get

So, seeing the bullet in front of the link isn’t what we are looking for. Using the rendered property instead of the visible property will give us

But the problem now is that the link can’t be simply brought back to the page without a full page refresh. That is one of the disadvantages of using the rendered property. Once a component is not rendered, you need to do a full page refresh to get it back. A partial page refresh won’t work.

If you want to show some white space for the missing ‘link 3’ you can’t use the rendered property at all. Putting a spacer between ‘link 2’ and ‘link 4’ you end up with the same image as you get for using the visible property.


One solution is to omit the bullet in front of the links, which is automatically generated by the component. If there is no bullet, we’ll get

So, still not what we really want.

The final part is how to get the bullet back in front of the visible links. Easy, as the af:link component has an icon property where can specify an image we use as a bullet. The final page looks like

You can use any other image as an icon to show in front of the link. The missing part is how we got rid of the original bullet from the af:panelList. Simply by using a style class, we defined in a skin file and applying it to the af:panelList

@charset "UTF-8";
/**ADFFaces_Skin_File / DO NOT REMOVE**/
@namespace af "http://xmlns.oracle.com/adf/faces/rich";
@namespace dvt "http://xmlns.oracle.com/dss/adf/faces";

.ivi af|panelList {
    list-style-type: none; 

.ivi af|panelList::item {
    list-style-type: none; 

And using this page

<?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="panelList.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">
                        <af:outputText value="PanelList with Bullet" id="ot1" inlineStyle="font-size:x-large;"/>
                        <!-- Header -->
                <af:gridRow height="100%" id="gr2">
                    <af:gridCell width="100%" halign="stretch" valign="stretch" id="gc2">
                        <af:panelList rows="5" styleClass="ivi"> 
                            <af:link text="link 1" id="l1" destination="http://www.oracle.com"
                            <af:link text="link 2" id="l2" destination="http://www.oracle.com"
                            <af:spacer width="10" height="10" id="s1"/>
                            <af:link text="link 3" visible="false" id="l3" destination="http://www.oracle.com"
                            <af:link text="link 4" id="l4" destination="http://www.oracle.com"

You can download the sample which was built using JDev from GitHub BlogPanelList. The sample doesn’t need any DB connection or model project.

JDeveloper REST POST Sample

Lately, I got a request to build a REST POST sample using an ADF REST DataControl. Well, here we go.

First of all, we need a REST API which allows us to create data as this will be translated to REST POST call. I deliberately don’t want to use an ADF based REST service as there are samples available for this.

Looking for free REST API services which allow creating data I found “reqres’ (http://reqres.in), a free ‘hosted REST-API ready to respond to your AJAX requests’. This service can be used to test REST calls using any verb you like. It promises to be online 24/7.

Let’s start by creating a new ADF Web Application. If we would only test the REST service we could have created a custom application, but I want to show the viewController part too, so the ADF Web Application is just fine.

I don’t show how to do this here as you can see it done here ‘Why and how to write reproducible test cases’

As we don’t use the ADFModel project you can delete this empty project if you like. After creating the initial workspace, we create a new project from the gallery as a ‘Custom Project’

I named the new project ‘BRPWebService’, but you can name the project anything you like. Inside the new project we new create a ‘Web Service Data Control (SOAP/REST)’:

After selecting this, a wizard will ask which kind of ‘Web Service Data Control’ we like to create. We choose REST and now have to specify the base URL to the REST service API.

We skip the next page as there is no OWSM Policy needed to access the REST API

In step 3 we define the path we want to use after the base URL. From the web page of the service, we see a bunch of possible API endpoints.

For this test, we use the ‘api/users’ path. The whole URL now is ‘http://reqres.in/api/users’. To test the creation of data we use the POST verb, so we select the POST. To make sure the service is functional, we add the GET verb too.

After naming the methods getUsers for the GET and createUser for the POST, we need to give the wizard info about the parameters the API expects and the response we get when the call is successful. When we click on the GET verb on the web page, we get all the info we need.

All JSON code samples are available from the web page or from this blog in the appendix

We copy the response from the web page and pate it o the ‘Response Sample’ field and create a parameter ‘page’ in the parameter section of the next wizard step.

The same we do for the createUser method. Here we have to copy the request parameter JSON and the response JSON from the web page.

On the next page, we test the Web Service Data Control

This completes the creation process. We can now run the Datacontrol from inside JDeveloper by right-clicking the data control and choosing ‘Run’

Testing the getUsers method with the parameter set to 2 we receive the right answer

Now, let’s try the POST verb by trying out the createUser method. As a parameter, we pass some JSON and after executing the method we get a JSON part back telling us the new Id of the user.

Everything works. Please remember that we only use a ‘fake’ service which accepts our JSON, but will not add any data!

Sample Application

For the fun of it, I developed a ViewControler which uses the REST Data Control. It allows to ask for users by providing a page to load, or you can test the POST verb to create a ‘fake’ new user.

The final application can be downloaded from GitHub BlogRestPost. It was built using JDeveloper without a DB connection.

After starting the app we see this UI

The service allows getting users in page mode, three users per page. So, enter 3 into the ‘Page’ filed and we get

Now, filling ‘name’ and ‘job’ field and clicking the createUser button we get

The service returns a new user with a new ID and the timestamp when the user was created.


JSON reponse for GET api/users?page=2

    "page": 2,
    "per_page": 3,
    "total": 12,
    "total_pages": 4,
    "data": [
            "id": 4,
            "first_name": "Eve",
            "last_name": "Holt",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/marcoramires/128.jpg"
            "id": 5,
            "first_name": "Charles",
            "last_name": "Morris",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/stephenmoon/128.jpg"
            "id": 6,
            "first_name": "Tracey",
            "last_name": "Ramos",
            "avatar": "https://s3.amazonaws.com/uifaces/faces/twitter/bigmancho/128.jpg"

JSON payload and response for POST api/create

    "name": "morpheus",
    "job": "leader"

    "name": "morpheus",
    "job": "leader",
    "id": "783",
    "createdAt": "2018-11-19T21:42:47.556Z"

JDeveloper: Task Flow with optional Parameters

In one of my current projects, I came across a wired problem concerning a task flow which uses parameters to configure the flow. To make it simple to understand the problem I made up a fictive use case.

Use Case

This is not a real use case, but an abbreviation of it just to show the problem and how to resolve it. It might not make sense in real life, but it allows to show the problem.

We like to see a form to enter some data. One of the fields the user can enter should be used to select a specific layout of the following pages. Ony Layout should be horizontal and one should be vertical.

The layout should allow showing a title with a number, a text part, and a footer part. Each of the parts is optional, only the layout must be specified.

The flow can be used at different points in different flows in the application.


The implementation is easy. We build a task flow, build with pages, and add 5 parameters to it. This task flow looks like this:

In a router component, we check the required parameter to decide which layout to use. The ‘none’ page is used if the parameter is not ‘v’ or ‘h’.

For the layout pages, which implement the layout part, I used a af:panelGridLayout with the right number of rows and columns. Here is the vertical.jsf page

Or the source view

The other pages can be found in the sample which was built using JDeveloper You can download the sample using the link at the end of the blog.

Now that we have the reusable task flow we need another task flow (adfc-config.xml in this case) to show how to use the use of the task flow.

We see an index page which calls the task flow using three different navigations (toPageNoParam, toPageAllParam and toPageTextOnly). The difference between the navigations are the parameters set to the task flows.

Here they are

Running the application we get the following output after filling in the form on the index page

WAIT, this doesn’t look right. The page with text only parameters displays the text in the footer section instead of the text section. If we look at the parameters defined for this task flow call we see

Yes, we added the value to the wrong parameter, ‘footer’ instead of ‘text’!

Easy change, we copy move it over to the ‘text’ parameter using copy and paste:

And we get

Great, this looks like it should be.


WAIT again, in the log window we now get an error message

<oracle.adf.model> <ValueMappingXmlImpl> <parse> 
   <ADFc: /WEB-INF/adfc-config.xml: Failed to parse element input-parameter: null value found for value.>

Why’s that?

If you look closely at the parameters, you’ll notice, that the ‘footer’ value is empty, whereas the other not set values showing a ‘-’. So we add the ‘-’ to the footer parameter:

To get this output running the app:

The error message in the log window is gone 🙂

Hm, but now we see the ‘-’ for the footer value. The value for the ‘title’ parameter looks identical to the parameter ‘footer’. However, we don’t see the ‘-’ for the ‘title’ in GUI.


Looking at the XML of the task flow call shows:

Now the problem is, that JDeveloper shows a ‘-’ for a parameter which is not set in the XML representation, meaning that the parameter is not in the XML structure at all!

The ‘-’ we typed into the ‘footer’ parameter is visible as value for the parameter and thus it printed when running the application.

The final solution is to remove the ‘footer’ parameter from the XML structure:

This will get us the following display in JDeveloper

The resulting running page now looks like

And we don’t see the error message in the log.

Sample Download

You can download the final application from GitHub BlogTaskFlowParameter. The sample was built using JDeveloper and doesn’t need a DB connection.