SQLEXECUTE Operation in Oracle Database
The Microsoft BizTalk Adapter for Oracle Database surfaces a standard set of operations on Oracle database artifacts. By using these operations, you can do things like call an Oracle function or procedure, or perform basic SQL data manipulation language (DML) operations on tables. However, there may be scenarios driven by your business logic that require you to perform operations that the Oracle Database adapter does not surface. For example, you may want to:
Perform an operation on database artifacts that are not surfaced by the Oracle Database adapter; for example, get the CURVAL or NEXTVAL of an Oracle SEQUENCE.
Perform data definition language operations; for example, create a table.
Perform operations on a database artifact that was not present at design time; for example, update records in a temporary table that is created by your business logic.
Perform more complex DML operations on tables than the operations that the Oracle Database adapter surfaces; for example, to perform a query that includes a JOIN clause.
For these kinds of scenarios, the Oracle Database adapter surfaces the SQLEXECUTE operation. The SQLEXECUTE operation is surfaced under the root node (/) in the Select a category pane in the Add Adapter Service Reference Visual Studio Plug-in and Consume Adapter Service BizTalk Project Add-in.
By using the SQLEXECUTE operation, you can perform a parameterized SQL statement on the Oracle database. The SQLEXECUTE operation supports an input parameter block consisting of parameter sets that enable you to execute the same SQL statement once for each set. The SQLEXECUTE operation returns the results of the SQL statement in a generic record set.
Note
You can pass IN and IN OUT parameters to procedures, functions, and packages in the SQLEXECUTE operation. The invoked artifact will execute with the supplied parameters on the Oracle database; however, the SQLEXECUTE operation does not return the value of OUT and IN OUT parameters to the client. If you want to invoke procedures, functions, or packages, we recommend that you do so by invoking the dedicated operations that the Oracle Database adapter exposes for these Oracle artifacts.
For more information about:
Performing a SQLEXECUTE operation by using BizTalk Server, see Run SQLEXECUTE Operation by Using BizTalk Server.
Performing a SQLEXECUTE operation by using the WCF service model, see Run SQLEXECUTE Operation by Using the WCF Service Model.
Performing the SQLEXECUTE operation by using the WCF channel model, see Run SQLEXECUTE Operation by Using the WCF Channel Model.
Message structure and SOAP actions for performing a SQLEXECUTE operation, see Message Schemas for the SQLEXECUTE Operation.