Message Schemas for Stored Procedures, Functions, and PL/SQL APIs
The Microsoft BizTalk Adapter for Oracle E-Business Suitesurfaces the underlying Oracle database stored procedures, functions, and PL/SQL APIs (stored procedures and functions within a package) as operations. This section describes the message structure and actions used to invoke stored procedures, functions, and PL/SQL APIs.
Message Structure of Stored Procedures, Functions, and PL/SQL APIs
The operations surfaced for functions and stored procedures follow a request-response message exchange pattern. The following table shows the structure of these request and response messages.
Note
See entity descriptions after the table.
| Operation | XML Message | Description |
|---|---|---|
| Stored Procedure Request | <[SP_NAME] xmlns="[VERSION]/Procedures/[SCHEMA]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]> |
Supports Oracle IN and IN OUT parameters in the message body |
| Stored Procedure Response | <[SP_NAME]Response xmlns="[VERSION]/Procedures/[SCHEMA]"> <[PRM1_NAME]>value1<[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]Response> |
Supports Oracle OUT and IN OUT parameters in the message body |
| Function Request | <[FN_NAME] xmlns="[VERSION]/Functions/[SCHEMA] "> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[FN_NAME]> |
Supports Oracle IN and IN OUT parameters in the message body |
| Function Response | <[FN_NAME]Response xmlns="[VERSION]/Functions/[SCHEMA]"> <[FN_NAME]Result>return_value</[FN_NAME]Result> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[FN_NAME]Response> |
Supports Oracle OUT and IN OUT parameters in the message body The function return value is returned in the <[FN_NAME]Result> element. This is the first element in the response message. It comes before any parameters. |
| PL/SQL API Request | <[SP_NAME] xmlns="[VERSION]/PackageApis/[SCHEMA]/[PACKAGE_NAME/[SP_NAME]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]> |
Same as Function or Stored Procedure |
| Packaged Procedure or Function Response | <[SP_NAME]Response xmlns="[VERSION]/PackageApis/[SCHEMA]/[PACKAGE_NAME]/[SP_NAME]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]Response> |
Same as Function or Stored Procedure |
Entity descriptions:
[VERSION] = http://schemas.microsoft.com/OracleEBS/2008/05.
[SCHEMA] = Collection of Oracle artifacts; for example, SCOTT.
[SP_NAME] = The stored procedure to be executed; for example, SP_INSERT.
[FN_NAME] = The function to be executed; for example, FN_GETID.
[PRM1_NAME] = The name of the Oracle parameter. See the Description column for supported parameter directions for each message.
[PACKAGE_NAME] = The name of the package that contains the targeted procedure or function.
The Oracle database supports overloading for stored procedures and functions. The Oracle E-Business adapter supports this capability by appending an overload string to the target namespace for each overloaded artifact. The value of this string is "overload1" for the first overload, "overload2" for the second overload, and so on. The following example shows the message structure for two overloaded stored procedures.
Stored Procedure Overload 1:
<[SP_NAME] xmlns="[VERSION]/PackageApis/[SCHEMA]/[PACKAGE_NAME]/[SP_NAME]/overload1">
<[PRM1_NAME]>value1</[PRM1_NAME]>
<[PRM2_NAME]>value1</[PRM2_NAME]>
…
</[SP_NAME]>
Stored Procedure Overload 2:
<[SP_NAME] xmlns="[VERSION]/PackageApis/[SCHEMA]/[PACKAGE_NAME]/[SP_NAME]/overload2">
<[PRM1_NAME]>value1</I_[PRM1_NAME]>
<[PRM2_NAME]>value1</I_[PRM2_NAME]>
…
</[SP_NAME]>
Message Actions of Stored Procedures, Functions, and PL/SQL APIs
The Oracle E-Business adapter uses the following message actions for stored procedure, function, and PL/SQL API operations.
Note
See entity descriptions after the table.
| Message | Action | Example |
|---|---|---|
| Stored Procedure Request | Procedures/[SCHEMA]/[SP_NAME] | Procedures/SCOTT/SP_INSERT |
| Stored Procedure Response | Procedures/[SCHEMA]/[SP_NAME]/response | Procedures/SCOTT/SP_INSERT/response |
| Function Request | Functions/[SCHEMA]/[FN_NAME] | Functions/SCOTT/FN_GETID |
| Function Response | Functions/[SCHEMA]/[FN_NAME]/response | Functions/SCOTT/FN_GETID/response |
| PL/SQL API Request | [SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME] | SCOTT/Package/CUSTOMER/SP_INSERT |
| Packaged Stored Procedure Response | [SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME]/response | SCOTT/Package/CUSTOMER/SP_INSERT/response |
| Packaged Function Request | [SCHEMA]/Package/[PACKAGE_NAME]/[FN_NAME] | SCOTT/Package/CUSTOMER/FN_GETID |
| Packaged Function Response | [SCHEMA]/Package/[PACKAGE_NAME]/[FN_NAME]/response | SCOTT/Package/CUSTOMER/FN_GETID/response |
| Overloaded Stored Procedure Request | [SCHEMA]/Procedure/[SP_NAME]/[OVERLOAD] | SCOTT/Procedure/SP_INSERT/overload1 |
| Overloaded Stored Procedure Response | [SCHEMA]/Procedure/[SP_NAME]/[OVERLOAD]/response | SCOTT/Procedure/SP_INSERT/overload1/response |
Entity descriptions:
[SCHEMA] = Collection of Oracle artifacts; for example, SCOTT.
[SP_NAME] = The stored procedure to be executed; for example, SP_INSERT.
[FN_NAME] = The function to be executed; for example, FN_GETID.
[PACKAGE_NAME] = The name of the package that contains the targeted procedure or function.
[OVERLOAD] = The Overload parameter. The possible values are overload1, overload2, and so on.
See Also
Messages and Message Schemas for BizTalk Adapter for Oracle E-Business Suite
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for