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