Edit

Message Schemas for the SQLEXECUTE Operation

  • Article

Microsoft BizTalk Adapter for Oracle Database surfaces strongly-typed metadata for artifacts present in the LOB system and exposes standard operations on these artifacts. However, there are scenarios where an application might require the execution of an arbitrary SQL statement that is driven by the business logic in the application. 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 surfaced by the Oracle Database adapter; for example perform a query that includes a JOIN clause.

    The Oracle Database adapter surfaces a special operation called the SQLEXECUTE operation to support such scenarios. By using this operation, you can specify an arbitrary SQL statement for the Oracle Database adapter to execute on the Oracle database. You can also specify multiple blocks of input parameters to the SQL statement. The Oracle Database adapter executes the SQL statement once for each set of parameters and returns any output as a generic (weakly-typed) 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, Microsoft recommends that you do so by invoking the dedicated operations that the Oracle Database adapter exposes for these Oracle artifacts.

The following XML shows the structure of the SQLEXECUTE operation:

<SQLEXECUTE xmlns="SQLEXECUTE">  
  <SQLSTATEMENT> [STATEMENT] </SQLSTATEMENT>  
  <PARAMETERSCHEMA>[PARAM_SPEC]</PARAMETERSCHEMA>  
  <PARAMETERSET>  
    <PARAMETERDATA>  
      <PARAMETER xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
        <c:string>[PARAM_VAL_1]</c:string>  
      </PARAMETER>  
    </PARAMETERDATA>  
    …  
  </PARAMETERSET>  
</SQLEXECUTE>

[STATEMENT] = The SQL statement to execute; for example, "SELECT * from emp WHERE empno=:emp_no".

[PARAM_SPEC] = The list of the IN parameters in the SQL statement and their data types; for example, "emp_no NUMBER".

[PARAM_VAL_1] = The value of the first parameter.

Each <PARAMETERDATA> section contains a complete set of <PARAMETER> elements that match the schema in the <PARAMETERSCHEMA> section. The <PARAMETERSET> can contain multiple <PARAMETERDATA> sections. If this is the case, the SQL statement is executed multiple times, once against each parameter set.

See Also

Messages and Message Schemas for BizTalk Adapter for Oracle Database