How to Call an Oracle Procedure Returning Rowtype via ESQL Compute Node in IBM ACE

Discover how to successfully call an Oracle procedure returning a rowtype using ESQL in IBM App Connect Enterprise, addressing common errors and providing a clear solution.
---
This video is based on the question https://stackoverflow.com/q/66592587/ asked by the user 'rpj123' ( https://stackoverflow.com/u/15027358/ ) and on the answer https://stackoverflow.com/a/66664720/ provided by the user 'rpj123' ( https://stackoverflow.com/u/15027358/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: IBM ACE - Calling Oracle Procedure returning a rowtype (via ESQL compute node)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Calling an Oracle Procedure Returning a Rowtype in IBM App Connect Enterprise

In today's guide, we're going to dive into a common challenge faced by developers working with IBM App Connect Enterprise (IBM ACE) when calling Oracle procedures that return a rowtype. If you've ever encountered error messages while trying to execute SQL statements, you're not alone. Let's explore the issue, analyze the error, and walk you through the solution step by step.

Understanding the Problem

While working with an Oracle procedure designed to retrieve a single row of customer data based on a given parameter, many developers have experienced difficulties. Here’s a brief look at the procedure in question:

The Oracle Procedure

[[See Video to Reveal this Text or Code Snippet]]

The stated goal is to correctly represent the Oracle procedure within the Compute Node in IBM ACE. However, many developers face issues such as BIP2230E errors when invoking this procedure from their Compute Node.

Analyzing the Error

Common Error Messages

When trying to call the procedure, you may encounter error messages such as:

BIP2230E: Error detected whilst processing a message in node...

BIP2321E: Database error: ODBC return code...

SQL state 'HY000'; Native Error Code '0'; Error Text...

These errors indicate a mismatch between the expected SQL type and what has been defined in the Oracle procedure, particularly regarding the handling of rowtypes.

The Solution

Switch to Using a PL/SQL Function

To solve the issue effectively, the recommendation is to switch from using a procedure to a PL/SQL function that returns a reference cursor. This approach simplifies the execution and allows you to retrieve result sets more seamlessly.

Define the PL/SQL Function:
Here’s how your new function might look:

[[See Video to Reveal this Text or Code Snippet]]

Update the Compute Node:
Now you can define and invoke the function within your Compute Node:

[[See Video to Reveal this Text or Code Snippet]]

Call the Function:
When you call the function, make sure to properly capture the result into a suitable variable:

[[See Video to Reveal this Text or Code Snippet]]

Key Takeaway

By transitioning to a function that returns a reference cursor, you eliminate the problems of handling OUT and IN OUT parameters directly within the SQL context, enabling smoother integration with your IBM ACE setup.

Conclusion

In summary, transitioning from an Oracle procedure to a PL/SQL function is often necessary to avoid errors when working with IBM App Connect Enterprise. This guide outlines the key steps to address the challenges of invoking SQL statements, ensuring you can successfully call procedures and handle rowtypes effectively.

By following the guidelines provided, you should be able to overcome any related issues and improve the reliability of your data operations. Happy coding!

Видео How to Call an Oracle Procedure Returning Rowtype via ESQL Compute Node in IBM ACE канала vlogize