Message Schemas for Functions and Procedures
The Microsoft BizTalk Adapter for Oracle Database surfaces Oracle database functions and stored procedures as operations. This section describes the message structure and actions used to invoke functions and procedures.
Message Structure of Functions and Procedures
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.
| Operation | XML Message | Description |
|---|---|---|
| Stored Procedure Request | <[SP_NAME] xmlns="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure"> <[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="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure"> <[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="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Function"> <[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="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Function"> <[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. |
| Packaged Procedure or Function Request | <[SP_NAME] xmlns="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_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="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]"> <[PRM1_NAME]>value1</[PRM1_NAME]> <[PRM2_NAME]>value2</[PRM2_NAME]> … </[SP_NAME]Response> |
Same as Function or Stored Procedure |
[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 Database 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="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME]/overload1">
<[PRM1_NAME]>value1</[PRM1_NAME]>
<[PRM2_NAME]>value1</[PRM2_NAME]>
…
</[SP_NAME]>
Stored Procedure Overload 2:
<[SP_NAME] xmlns="http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME]/overload2">
<[PRM1_NAME]>value1</I_[PRM1_NAME]>
<[PRM2_NAME]>value1</I_[PRM2_NAME]>
…
</[SP_NAME]>
Message Actions of Functions and Procedures
The Oracle Database adapter uses the following message actions for stored procedure and function operations.
| Message | Action | Example |
|---|---|---|
| Stored Procedure Request | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure/[SP_NAME] |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Procedure/SP_INSERT |
| Stored Procedure Response | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure/[SP_NAME]/response |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Procedure/SP_INSERT/response |
| Function Request | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Function/[FN_NAME] |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Function/FN_GETID |
| Function Response | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Function/[FN_NAME]/response |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Function/FN_GETID/response |
| Packaged Stored Procedure Request | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME] |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/CUSTOMER/SP_INSERT |
| Packaged Stored Procedure Response | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[SP_NAME]/response |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/CUSTOMER/SP_INSERT/response |
| Packaged Function Request | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[FN_NAME] |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/CUSTOMER/FN_GETID |
| Packaged Function Response | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Package/[PACKAGE_NAME]/[FN_NAME]/response |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Package/CUSTOMER/FN_GETID/response |
| Overloaded Stored Procedure Request | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure/[SP_NAME]/[OVERLOAD] |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Procedure/SP_INSERT/overload1 |
| Overloaded Stored Procedure Response | http://Microsoft.LobServices.OracleDB/2007/03/[SCHEMA]/Procedure/[SP_NAME]/[OVERLOAD]/response |
http://Microsoft.LobServices.OracleDB/2007/03/SCOTT/Procedure/SP_INSERT/overload1/response |
[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 Database