Processing Comma-Separated Lookup Values in Oracle Integration Cloud Using the Functions Library

Abstract
In Oracle Integration (OIC), lookup values are usually treated as single values, even when a lookup field contains multiple comma-separated entries.
This article shows a practical way to split and process those values individually by using oraext:create-nodeset-from-delimited-string() XSLT. With this approach, you can convert a comma-separated string into iterable XML nodes and process each value sequentially in a For-Each loop.

Key Takeaways
• Process multiple values stored in a single lookup column.
• Convert a comma-separated string into iterable XML nodes.
• Use oraext:create-nodeset-from-delimited-string() in OIC mappings.
• Implement dynamic For-Each processing in OIC.
• Reduce hardcoding and improve reusability in integrations.

Introduction
OIC lookups are commonly used to externalize configuration and avoid hardcoding values inside integrations. However, a challenge arises when a single lookup field contains multiple values that need to be processed one by one. For example, a lookup may store the following value:

“Pay1,Pay2,Pay3,Pay4”

In scenarios like this, each value may need to be processed separately, such as triggering an API call for each job name. Since OIC treats the lookup value as a single string, we need additional transformation logic to split it into individual elements.
This article walks through a simple and effective approach to perform this scenario.

Solution Overview
In this scenario, the requirement is to:
• Fetch a comma-separated value from a lookup.
• Split that value into individual elements.
• Process each element sequentially using a For-Each loop.

Step 1: Fetch Lookup Value
Consider a use case where job names need to be passed to a target REST API.
First, retrieve the lookup value and store it in a variable:
$Var_ImportJobName
Example value stored in the variable:

Pay1,Pay2,Pay3,Pay4

At this point, OIC still treats this as a single string.

Step 2: Define Target Structure (XSD)

To support iteration, define the following XML structure that can hold multiple job elements:

Note: This structure allows dynamic creation of multiple <job> elements.

Save this schema as an XSD file, then go to your integration in OIC and configure a Stage File
action with the following settings:

• Operation: Write File
• Schema Type: XML Schema (XSD)
• Upload the XSD file
• Choose root element: jobs

Mapper Logic

Step 3: Read Values and Prepare for Iteration

Next, add another Stage File action with:

After reading, the data will be structured as:

Operation: Read File

After reading the file, the data will be structured as follows:

Input:

Output:

Jobs name: Pay1, Pay2,Pay3,Pay4

<jobs>

  <job>Pay1</job>

  <job>Pay2</job>

  <job>Pay3</job>

  <job>Pay4</job>

</jobs>

Now the values are available as individual XML elements, which makes them easy to process in a loop.

Step 4: Process Each Value Using For-Each

Use the structured output from the previous step as the input to a For-Each action:

• Pass the <job> nodes to the For-Each loop.
• Process each job individually.
• Trigger the required REST or API call for each job.

This approach ensures controlled and sequential execution of each value.

1. oraext:create-nodeset-from-delimited-string()

• Splits a string using a delimiter.
• Returns the result as a node set that can be iterated over.

Example:

oraext:create-nodeset-from-delimited-string('{}abc', $Var_ImportJobName, ',')

Explanation of the parameters:

• ‘{}abc’ -> A dummy QName placeholder. It is mandatory, but not functionally used in this scenario.
• $Var_ImportJobName -> Input string.
• ',' -> Delimiter.

2. xsl:for-each

This is used internally to iterate through each value returned by the split operation.

3. Current Node Value

Inside the mapping, you can use:

<xsl:value-of select="."/>

This represents the current node value.

For Example:

• First iteration –> Pay1
• Second iteration –> Pay2
• Third iteration –>Pay3
• Fourth iteration –> Pay4

This is how each <job> element gets its value.

Conclusion

When a lookup field in OIC contains multiple comma-separated values, additional logic is required because OIC treats the entire entry as a single string.
By using oraext:create-nodeset-from-delimited-string() along with a For-Each loop, you can split the lookup value into individual elements with a oraext:create-nodeset-from-delimited-string() For-Each
and process them sequentially. This makes the integration more dynamic, reusable, and easier to maintain, while also avoiding manual duplication of logic.
This approach is especially useful for batch processing, API orchestration, and other configurable execution flows.

Key Contacts

For questions, please contact Devkiran.tomar@oracle.com

References/Links for more Information

create-nodeset-from-delimited-string :https://docs.oracle.com/en/middleware/soa-suite/soa/14.1.2/develop/create-nodeset-delimited-string.html

 For each loop in OIC: https://docs.oracle.com/en/cloud/paas/application-integration/ftp-adapter/add-each-action-iterate-individual-records.html

XML Schema basics  :https://www.w3schools.com/xml/schema_intro.asp