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.