OIC - difference between SOAP and REST

Difference between SOAP and REST:

SOAP (Simple Object Access Protocol)

Protocol: A strict protocol for message exchange with built-in standards for security, transactions, and error handling.

Message Format: Always uses XML, requiring a structured SOAP envelope with headers and body.

Transport: Can use multiple transport protocols like HTTP, SMTP, TCP, etc.

Features:

Supports WS-Security for secure message exchange.

Better suited for stateful operations (e.g., sessions).

Heavy and slower due to XML parsing and strict standards.

Use Cases: Enterprise applications requiring high security, reliability, and formal communication (e.g., financial transactions, payment gateways).

REST (Representational State Transfer)

Architecture: A lightweight architectural style, not a protocol, that uses standard HTTP methods (GET, POST, PUT, DELETE).

Data Formats: Supports multiple formats like JSON, XML, HTML, or plain text (JSON being the most common).

Transport: Relies exclusively on HTTP.

Features:

Stateless, meaning each request is independent of others.

Faster and more efficient due to lightweight communication and flexible data formats.

Easy to implement and widely used for modern APIs.

Use Cases: Web and mobile applications, microservices, and scenarios where performance and scalability are priorities.

In summary:

SOAP is rigid, heavy, and ideal for complex, secure operations.

REST is simple, flexible, and optimized for web-based services.

OIC - Can we send XML input in rest adapter?

Yes, the REST adapter in OIC can handle XML input.

1. Set the media type to application/xml.
2. Define an XML schema (XSD) for payload mapping.
3. Include Content-Type: application/xml in the request header.
4. Test with XML payloads using Postman or curl.

Ensure proper configuration and schema matching for seamless processing.

Screenshots:

TBD

OIC - Use of nested DVMs or lookups to implement conditional logic | Enhancing Error Handling in OIC with Conditional DVM Lookups

Here, we will demonstrate how nested DVMs or lookups can be utilized to implement conditional logic effectively.

Use Case:

Error handling is a critical component in integrations. In Oracle Integration Cloud (OIC), different error codes might require specific severity levels for better classification. For example:

Specific error codes from external systems (e.g., ERROR_CODE) map to severity levels like CRITICAL, HIGH, or LOW.

If an unknown or generic error occurs, it defaults to a predefined severity level (e.g., NA).

To achieve this, we use nested DVM lookups to dynamically retrieve severity levels based on the error context.

Code Explanation

The provided code handles dynamic severity mapping based on two levels of logic:

1. Primary Lookup: Retrieves the SEVERITY based on the error code received (ERROR_CODE) from the payload.

2. Fallback Logic: If the error code is missing or invalid, a second lookup retrieves a default severity (GENERICOICTECHNICALERROR) to ensure robustness.

Expression Used:

dvm:lookupValue("Common_Error_Details_Lookup","ERROR_CODE",$GlobalFaultObject/ns0:fault/ns0:errorCode,"SEVERITY",dvm:lookupValue("Common_Error_Details_Lookup","KEY","GENERICOICTECHNICALERROR","SEVERITY","NA"))

How It Works:

1. Outer Lookup:

File: Common_Error_Details_Lookup.

Uses ERROR_CODE from the payload ($GlobalFaultObject/ns0:fault/ns0:errorCode).

Retrieves the corresponding SEVERITY.

2. Inner Lookup (Fallback):

File: Common_Error_Details_Lookup.

Key: GENERICOICTECHNICALERROR.

Retrieves a default severity (SEVERITY) if the primary lookup fails or returns null.

Key Benefits

1. Dynamic Mapping: Avoids hardcoding severity levels by using reusable DVM files.

2. Robust Error Handling: Implements a fallback mechanism for unknown or generic errors.

3. Centralized Control: Simplifies maintenance by managing mappings in one place (DVM).

4. Seamless Integration: Ensures consistent error handling across systems and reduces potential failures.

Practical Example

DVM File Structure

Conclusion

This approach highlights how OIC's DVM lookup capability can be extended to include conditional logic, ensuring robust and scalable error handling mechanisms in integration flows. By leveraging nested DVMs, developers can streamline error classification while minimizing manual intervention.

OIC - XSLT - "Using format-number() to Add Leading Zeros to Single-Digit Numbers"

Use Case:

In data processing or reporting scenarios, leading zeros are crucial for ensuring consistency and accuracy in how numbers are displayed, especially for dates. 

For example, when formatting dates such as "Day 1" or "Day 9," it’s essential to present them as "01" and "09" to maintain uniformity, especially when aligning values in tables or spreadsheets. 

Using the format-number function, such as fn: format-number(ns28: Input Day, "00"), ensures that single-digit day values are always presented with a leading zero, improving both readability and data integrity in systems requiring consistent date formatting.

Code:

fn: format-number (ns28: Input Day, "00") 

OIC - XSLT - "Dynamic Payment Date Adjustment with XSLT: Handling Conditional Date Formatting"

Usecase:

This XSLT code dynamically adjusts the payment date (H_I_PAY_DATE) based on the comparison of the InputDay and the day part of the ValueDate from the source file. It formats the date by concatenating the year-month from ValueDate and the day from InputDay, or adjusts the date if necessary, ensuring proper formatting for payment processing.

Code used:

<xsl:choose>

<xsl:when test="number (ns28:InputDay) &lt; number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 2))">

<ns23:H_I_PAY_DATE xml:id="id_76">

<xsl:value-of select="concat (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 1, 6), fn:format-number (ns28:InputDay, '00'))"/>

</ns23:H_I_PAY_DATE>

</xsl:when>

<xsl:when test="(( number (ns28:InputDay)) &gt; ((number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 8)))  and ((number (substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 5, 2)) -1) =0))">

<ns23:H_I_PAY_DATE xml:id="id_209">

<xsl:value-of select="concat ((number(substring ($ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 1, 4)) -1), '12', fn:format-number (number (substring (ns28:InputDay, 1, 2)), '00'))"/>

</ns23:H_I_PAY_DATE>

</xsl:when>

<xsl:when test="((number (ns28:InputDay ) &gt; number (substring ($ReadSourceFile/nsmpre:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate, 7, 8 ) )) and ((number (substring ($ReadSourceFile/nsmpre: ReadResponse/ns28: Payments/ns28: FileHeader/ns28: ValueDate, 5, 2)) - 1) != 0))">

<ns23:H_T_PAY_DATE xml:id="id_76">

<xsl:value-of xml:id="id_215" select="concat (substring ($ReadSourceFile/nsmpro: ReadResponse/ns28: Payments/ns28: FileHeader/ns28:ValueDate, 1, 4), fn: format-number ((number (substring ($ReadSourceFile/nsmpre: ReadResponse/ns28: Payments/ns28: FileHeader/ns28:ValueDate, 5, 2)) - 1), &quot;00&quot;), fn: format-number (ns28: Input Day, &quot;00&quot;) )"/>

</ns23:H_T_PAY_DATE>

</xsl:when>

<xsl:otherwise>

<ns23:H_I_PAY_DATE xml:id="id_210">

<xsl:value-of select="$ReadSourceFile/nsmpr0:ReadResponse/ns28:Payments/ns28:FileHeader/ns28:ValueDate"/>

</ns23:H_I_PAY_DATE>

</xsl:otherwise>

</xsl:choose>

Screenshot:

OIC - Can we use/call plsql in OIC xslt for complex logic processing?

No, we cannot directly call PL/SQL within XSLT or use it for complex logic processing in Oracle Integration Cloud (OIC). However, there are workarounds to achieve this indirectly:

1. Use a REST/SOAP API to Call PL/SQL:

Expose your PL/SQL procedure or function as a REST or SOAP service from your Oracle database.

In OIC, you can invoke this service as part of your integration logic.

2. Invoke PL/SQL Using Database Adapter:

OIC provides a Database Adapter that allows you to call stored procedures or execute queries directly against your database.

This is an efficient way to leverage PL/SQL logic in your integrations.

3. Embed Complex Logic in XSLT (Limited):

While XSLT supports conditional processing and basic logic (using if, choose, etc.), it’s not designed for complex operations.

For anything beyond formatting or basic transformations, offload the logic to PL/SQL via a service or adapter.

4. Use Custom Functions or Scripts:

In scenarios where custom logic is necessary, consider JavaScript or Groovy scripting within OIC integrations, but these cannot directly call PL/SQL either. They can, however, manipulate the data post-PL/SQL call.

Recommendation:

For complex logic, it's best to keep it within the database using PL/SQL and expose the required operations via a service. Use OIC to orchestrate and connect the services, ensuring a clean separation of concerns.

OIC - Commonly Used XSLT Functions in Oracle Integration Cloud (OIC)

In Oracle Integration Cloud (OIC), the most commonly used XSLT functions are primarily focused on transforming, filtering, and manipulating XML data. Here's a list of the most widely used XSLT functions:

1. string()

Converts a value to a string.

Example:

<xsl:value-of select="string(123)"/>

2. concat()

Concatenates multiple strings together.

Example:

<xsl:value-of select="concat('Hello ', 'World')"/>

3. substring()

Extracts a substring from a string.

Example:

<xsl:value-of select="substring('Hello', 1, 3)"/>

4. contains()

Checks if a string contains a specified substring.

Example:

<xsl:value-of select="contains('Hello World', 'World')"/>

5. starts-with()

Checks if a string starts with a specified substring.

Example:

<xsl:value-of select="starts-with('Hello', 'Hel')"/>

6. normalize-space()

Removes leading and trailing whitespace and replaces sequences of whitespace characters with a single space.

Example:

<xsl:value-of select="normalize-space('  Hello    World  ')"/>

7. substring-before()

Returns the part of the string before the first occurrence of a specified substring.

Example:

<xsl:value-of select="substring-before('Hello World', ' ')"/>

8. substring-after()

Returns the part of the string after the first occurrence of a specified substring.

Example:

<xsl:value-of select="substring-after('Hello World', ' ')"/>

9. translate()

Replaces characters in a string according to a mapping.

Example:

<xsl:value-of select="translate('abcABC', 'abc', '123')"/>

10. number()

Converts a value to a number.

Example:

<xsl:value-of select="number('123')"/>

11. format-number()

Formats a number according to a specified pattern.

Example:

<xsl:value-of select="format-number(12345.678, '#,##0.00')"/>

12. string-length()

Usage: Returns the length of a string.

Example:

<xsl:value-of select="string-length('Hello World')"/>

13. upper-case() / lower-case()

Usage: Converts a string to uppercase or lowercase.

Example:

<xsl:value-of select="upper-case('hello')"/>

14. sum()

Usage: Returns the sum of numeric values in a node-set.

Example:

<xsl:value-of select="sum(/root/item/price)"/>

15. key()

Usage: Retrieves nodes from a key value pair, often used for fast lookups.

Example:

<xsl:value-of select="key('mykey', 'keyname')"/>

16. format-dateTime()

Usage: Formats a date-time value in a specified pattern.

Example:

<xsl:value-of select="format-dateTime(current-dateTime(), '[Y0001]-[M01]-[D01]')"/>

These functions are essential for transforming data in OIC, especially when handling XML data. They allow for dynamic manipulation of string, numeric, date, and node-set data, making XSLT a powerful tool in integration scenarios.