ADF method for calling DB procedures, executing other SQL statements with input and output parameters

How to call DB procedure with input and output parameters. Here I provide an example of ADF method which can be used with any number of in/pout parameters used in any order:

    public void runStatement(String stt, boolean commit, ArrayList<Map<String, Object>> pars) {
        
        CallableStatement st = this.getDBTransaction().createCallableStatement((stt), 0);
        try {
            for (int i = 0; i < pars.size(); i++) {
                if (pars.get(i).get("type").equals("in")) {
                    if (pars.get(i).get("datatype").equals("String")) {
                        st.setString(i + 1, (String)pars.get(i).get("value"));
                    }
                } else {
                    if (pars.get(i).get("datatype").equals("String")) {
                        st.registerOutParameter(i + 1, Types.VARCHAR);
                    }
                }
            }
            st.execute();
            for (int i = 0; i < pars.size(); i++) {
                if (pars.get(i).get("type").equals("out")) {
                    if (pars.get(i).get("datatype").equals("String")) {
                        pars.get(i).put("value", st.getString(i+1));
                    }
                }
            }
            if (commit) {
                getDBTransaction().commit();
            }
        } catch (SQLException e) {
            throw new JboException(e);
        } finally {
            try {
                st.close();
            } catch (Exception exc) {
                // something to do
            }
        }
    }

As you can see here I use List of Map. Containing Map has 3 mappings: type, datatype, value. Type stands for in/out, datatype could be any datatype which should be handled in this method explicitly. In this example I handle just String. If you need any other, e.g. Integer, you should extend this method by some more ifs... Value stands for any value which is delivered to DB procedure as input parameter or got back as output parameter.

Example of constructing parameter array:

    public String createEmployee(String name, String surname) {

        ArrayList<Map<String, Object>> pars = new ArrayList<Map<String, Object>>(3);

        Map<String,Object> parMap = new HashMap<String, Object>();
        parMap.put("type", "in");
        parMap.put("datatype", "String");
        parMap.put("value", surname);
        pars.add(0, parMap);

        parMap = new HashMap<String, Object>();
        parMap.put("type", "in");
        parMap.put("datatype", "String");
        parMap.put("value", name);
        pars.add(1, parMap);

        parMap = new HashMap<String, Object>();
        parMap.put("type", "out");
        parMap.put("datatype", "String");
        pars.add(2, parMap);

        runStatement("begin program_pkg.create_employee(?,?,?); end;", true, pars);
        return (String)pars.get(2).get("value");
    }

This method calls DB procedure which creates employee and returns autogenerated ID.

How to output text with line breaks in ADF ?

   If we use regular "af:outputtext" component to print text with line breaks (new line symbols like "\n" or  "\r\n")  in ADF - we don't succeed. Text is displayed in one line regardless of those symbols. Instead we need to use  "af:outputFormatted" component. But it is not enough. While it does not understand "\n" or "\r\n" symbols, we need to use HTML <br> tag instead. Thus need to translate text manually by using custom java method. How it could be done is described in my other post - How to pass parameter to method using expression language (EL) in ADF? .

How to pass parameter to method using expression language (EL) in ADF?

Apparently there is not possible to pass parameter to java method using expression language. But it is possible to use Map as a return type for method. So - utilization of Map can benefit as a workaround for our needs.

Example:

Suppose in ADF faces page we need to print text with all line break symbols replaced with HTML <br> tags.


  • Create bean "utilsBean" with method "getBrFormattedString"

    public Map<String, String> getBrFormattedString() {
        return new HashMap<String, String>() {
            @Override
            public String get(Object key) {
                String str = (String)key;
                str = str.replaceAll("(\\r|\\n|\\r\\n)+", "<br>");
                return str;
            }
        };

    }

  • Create "af:outputFormatted" component

 <af:outputFormatted value="#{utilsBean.brFormattedString[requestScope.messageDetails]}"
                                        id="of1"/>

As you can see, in this example Map is constructed dynamically and finally it acts as a regular method. We can put there any functionality we need. We could use any kind of object instead of String as well - Map<Object, Object>.

Introduction to ADF. Mindmap of major topics.

Recently had a pleasure to introduce Oracle ADF to participants of Kaunas Java Users Group.
Mindmap of major topics presented can be downloaded here.

How to get LOV value instead of index when it is used in "selectOneChoice" component in the table row?

   When using LOV's, a common issue in ADF is to get selected value instead of index when LOV is used to render for example "selectOneChoice" component. I've faced it when "selectOneChoice" was used in table. That means every row in table can have different selection of LOV values. Thus we can't rely on iterators current row and simply to access desired attribute by using attribute binding. But we can simply access it through EL:

We have LOV based attribute "MyAttribute" in table. In GUI it is rendered as selectManyChoice:

  <af:selectOneChoice value="#{row.bindings.MyAttribute.inputValue}"
                        label="#{row.bindings.MyAttribute.label}" id="soc1">
      <f:selectItems value="#{row.bindings.MyAttribute.items}" id="si1"/>
    </af:selectOneChoice>

To access selected item, we can use:

  • "#{row.bindings.MyAttribute.inputValue}" - will get an LOV index of selection "
  • "#{row.bindings.MyAttribute.attributeValue}" - will get a LOV value of selection







How to make selectManyShuttle component work with nested table?

ADF can pretty easy handle Oracle DB nested tables in its components. In this post I will describe how it works with selectManyShuttle. Probably a similar behaviour can be used with other list components as well. I will use departments and employees tables of HR schema. Will create new column in departments with type of nested table. Then fill-in this nested table with all employee ids which belong to according department. Then create application to show You how those records can be managed. Everything should go in pretty standard way except few trick which will emphasize further.

There are 5 major steps to achieve the result:

1. Execute SQL scripts to create/alter necessary DB objects (nested table type creation, employees table altering, data migration):



2. In the ADF application create necessary objects for departments and employees.
Two things to change in generated entity:
   a) Change (if its different) type of attribute EmployeesIds to oracle.jbo.domain.Array
   b) Switch view of entity to source mode and add attribute  ElemType="java.lang.String" to EmployeesIds definition element.
After both preceding changes attribute definition XML element should look like this:


Note: probably depending on JDeveloper version in graphical view of attributes You may see wrong type for EmployeesIds. It can be shows as "Array", but don't worry about it if in XML source there is oracle.jbo.domain.Array:


3. Generate view row implementation class DepartmentsViewRowImpl.java  for DepartmentsView object. Add there couple new methods. Here is the main trick of this example. Looks like selectManyShuttle component knows how to handle String[], but not oracle.jbo.domain.Array. Thats why we need extra methods for conversion:

And please expose those methods to client.

4. One more specific to this example trick has to be done (that means it may be not necessary in Your particular case). While we're using nested table of varchar2 and employee ids in DB table employees are stored as Number, we need a little conversion in EmployeesView object. Will convert Number to String. To carry out this we need generate EmployeesViewRowImpl.java and change getEmployeeId method like this:

5. Create main.jspx with necessary bindings. Here is selectManyShuttle with reference to previously created getEmployees/setEmployees methods:


Select items are rendered directly from EmployeesView1 binding.

Here is what we should finally see in browser:


Thats it! Should work. Please download sample application to run it or check details of this example.


JDeveloper version 11.1.1.7

JBO-25058: Definition vc_temp_1 of type Variable is not found in ...

"JBO-25058: Definition vc_temp_1 of type Variable is not found in ...".
What can be the reason?
ADF creates/reuses application module (AM) instance for each user request. It is passivated (saved) after each HTTP request (if configured accordingly). When in application there are used some queries with view criteria, filter variables are passivated  for according AM as well. During the next HTTP request of the same user it is recalled and used in processing.
But... There are couple important AM parameters:

  • Idle Instance Timeout (s) = 600 (default)
  • Pool Polling Interval (s) = 600 (default)

In my case I've got JBO-25058 error when trying to re-execute query with view criteria after 600s. Simply my referenced AM module with view criteria variable was purged before...
So the fix was simple - just increased mentioned AM parameters to 1800s. I've choosed such period because session timeout in my app is the same. That means user will not experience such error because session simply ends before.

How to create XMLIndex? When its worth to use it?

How to create XML index?

Very general way with all default applied as follows:

create index index_name on table_name (xmltype_column) indextype is xdb.xmlindex;

Check the name of PATH table

select path_table_name from user_xml_indexes  where table_name = 'table_name' and index_name = 'index_name'; 

Check what secondary indexes are used

select c.index_name, c.column_name, c.column_position, e.column_expression
  from user_ind_columns c left outer join user_ind_expressions e on (c.index_name = e.index_name)
where c.table_name in (select path_table_name from user_xml_indexes
where index_name ='index_name')
order by c.index_name, c.column_name;

How to turn off the use of XMLIndex?

Use one of optimizer hints:
/*+ NO_XMLINDEX_REWRITE */
/*+ NO_XMLINDEX_REWRITE_IN_SELECT */
/*+ NO_XML_QUERY_REWRITE */

When to use XML index ?
We can benefit from XMLIndex when queries with filter predicates from inside XML document exist. And there is no outside key, which could return particular XML doc for further processing.

Example:

select t.id, xt.id, xt.name
from
test_table t,
xmltable(
xmlnamespaces(default 'http://my.namespace.com'),
'/element' passing t.xml_content
columns
id path '/activity/@id',
name path '/activity/name'
) xt
where t.xml_content is not null
) where xt.name ='BUBU' and t.id='ID'

Here we have "xt.name" from inside XML. If would use only this filter criteria with "t.id" absent, XMLIndex would be very beneficial to find particular row with "/activity/name/BUBU". For DB engine it should be quite easy to do that, because "/activity/name" will be available in index path table (if created by default). If "t.id" in filter predicate is present, "test_table" is first filtered on "t.id", then particular XML doc is passed for xpath processing. Therefore we not experience any XMLIdex advantages.

Thus - use XMLIndex when can't uniquely identify row with XML content using only columns of outside table (i.e. column "id" of table "test_table").

Learn more about XMLIndex from Oracle XML DB Developer's guide.