Iterate through the rows returned by the DB Adapter

A new A-Team hint
Written by Olivier Le Diouris, Oracle Corporation
October, 2005

Here is a quick document, summarizing the little trick I used to iterate though the data returned by the DB Adapter. It also adresses a question we commonly have.
Let's say we use the DB Adapter to do a query, and it returns a collection of elements we wish to treat in a certain way, depending - for example - on the data contained in the elements of the collection.
Let us suppose we have a view to query, described this way:

SQL> desc approvers_view
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 APPROVER_ID                               NOT NULL VARCHAR2(128)
 APPROVER_NAME                             NOT NULL VARCHAR2(128)
 TECHNOLOGY                                NOT NULL VARCHAR2(128)

SQL>

We query this view with the technology column:

SQL> col approver_id for a30
SQL> col approver_name for a20
SQL> col technology for a20
SQL> select * from approvers_view where technology = 'Security';

APPROVER_ID                    APPROVER_NAME        TECHNOLOGY
------------------------------ -------------------- --------------------
amit.jasuja@oracle.com         Amit Jasuja          Security
peter.farkas@oracle.com        Pete Farkas          Security

SQL>

We have two people responsible for this technology stack, what we want to do is to send each of them a separate email...

If we use the DB Apadter, we will eventually have to deal with a document - or document fragment - like this one:

<n:ApproversViewCollection xmlns:n="http://xmlns.oracle.com/pcbpel/adapter/db/top/RequestTrackerTest" 
                           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ApproversView>
    <approverId>amit.jasuja@oracle.com</approverId> 
    <approverName>Amit Jasuja</approverName> 
    <technology>Security</technology> 
  </ApproversView>
  <ApproversView>
    <approverId>peter.farkas@oracle.com</approverId> 
    <approverName>Pete Farkas</approverName> 
    <technology>Security</technology> 
  </ApproversView>
</n:ApproversViewCollection>

We are going to use the BPEL while activity. The trick is to find the right formulation for the while-condition.
The XPath way to access the email address we are interested in is the following one:

 /ApproversViewCollection/ApproversView[index]/approverId

where 'index' is the index of the row in the array returned by the DB Adapter. index begins at '1'. And most important, when 'index' exceeds the size of the array, XPath returns an appropriate zero-length string.
Basically that's it, we want to loop while the XPath expression returns a string longer than zero.

To do so:

Process

we create a variable xsd:int, which we will use as the index

we initialize this variable to 1 before the while loop

we define the loop condition as mentionned above, involving the index variable

in the loop, we refer to the row, using the index variable

in the loop, we increment the index variable

The trick is the expression of the while condition:

 string-length(bpws:getVariableData('GetApprovers_OutputVariable',
                                    'ApproversViewCollection')/ApproversView[number(bpws:getVariableData('index'))]/approverId) > 0

...which is not what the expression builder suggests first.
NB: Using the cound-node function is also an option.
Refering to the data we're interested in can be done using the same kind of syntax:

  bpws:getVariableData('GetApprovers_OutputVariable',
                       'ApproversViewCollection')/ApproversView[number(bpws:getVariableData('index'))]/approverId

Incrementing the index is easy:

 number(bpws:getVariableData('index') + 1)

Note:
Another syntax, closer to what the Expression Builder suggests by default can be used:

  bpws:getVariableData('GetApprovers_OutputVariable',
                       'ApproversViewCollection',
                       '/ns4:ApproversViewCollection/ApproversView[number(bpws:getVariableData(&quot;index&quot;))]/approverId')

Notice that you have to escape the quotes, in the reference to the index variable, as you have two embedded expressions... I prefer the first version.

The A-Team, 2005