Run SQLEXECUTE operation in Oracle Database using the WCF Service Model

TheMicrosoft 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. By using the SQLEXECUTE operation, you can perform a parameterized SQL statement on the Oracle database. The SQLEXECUTE operation supports an input parameter block comprised 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.

About the Examples Used in this Topic

The examples in this topic use an Oracle SEQUENCE named TID_SEQ. A script to generate this SEQUENCE is supplied with the SDK samples. For more information about the SDK samples, see Samples in the SDK.

The WCF Client Class

The WCF service model generates a dedicated WCF client, SQLEXECUTEClient, for the SQLEXECUTE operation. The following code shows the SQLEXECUTEClient and the signature of the method that you call to invoke the SQLEXECUTE operation.

public partial class SQLEXECUTEClient : System.ServiceModel.ClientBase<SQLEXECUTE>, SQLEXECUTE {  
  
    ...  
  
    public microsoft.lobservices.oracledb._2007._03.GenRecordRow[] SQLEXECUTE(string SQLSTATEMENT, string PARAMETERSCHEMA, microsoft.lobservices.oracledb._2007._03.PARAMETERDATA[] PARAMETERSET);   
}  

The SQLEXECUTE operation returns a generic record set. This record set contains the values (if any) that are returned by the statements that the SQLEXECUTE operation executes. You can pass sets of input parameters to the SQLEXECUTE operation in a collection of PARAMETERDATA objects, each of which contains a collection of input parameters represented as strings. The following code shows the definition of a PARAMETERDATA set.

namespace microsoft.lobservices.oracledb._2007._03 {  
    using System.Runtime.Serialization;  
  
    [System.CodeDom.Compiler.GeneratedCodeAttribute("System.Runtime.Serialization", "3.0.0.0")]  
    [System.Runtime.Serialization.DataContractAttribute()]  
    public partial class PARAMETERDATA : object, System.Runtime.Serialization.IExtensibleDataObject {  
  
        ...  
  
        private string[] PARAMETERField;  
  
        ...  
  
        [System.Runtime.Serialization.DataMemberAttribute()]  
        public string[] PARAMETER {  
            get {  
                return this.PARAMETERField;  
            }  
            set {  
                this.PARAMETERField = value;  
            }  
        }  
    }  
}  

Invoking the SQLEXECUTE Operation

To invoke the SQLEXECUTE operation by using a WCF client, perform the following steps.

1. Generate a SQLEXECUTEClient class for the target table or view.

   Important

   The SQLEXECUTE operation is surfaced under the root node (/) in the Select a category pane in the Add Adapter Service Reference dialog box.

2. Create an instance of the SQLEXECUTEClient class, and invoke the SQLEXECUTE method to execute SQL statements on the Oracle database.

   For more detailed information about how to create a WCF client class and invoke operations on the Oracle Database adapter, see Overview of the WCF service model with the Oracle Database adapter.

   The following example uses the SQLEXECUTEClient to get the next value of an Oracle SEQUENCE, TID_SEQ, by executing the following SQL statement: SELECT tid_seq.nextval id from DUAL. The output is then written to the console.

using (SQLEXECUTEClient sqlClient = new SQLEXECUTEClient("OracleDBBinding_SQLEXECUTE"))  
{  
    sqlClient.ClientCredentials.UserName.UserName = "SCOTT";  
    sqlClient.ClientCredentials.UserName.Password = "TIGER";  
    try  
    {  
        sqlClient.Open();  
    }  
    catch (Exception ex)  
    {  
        Console.WriteLine("Error opening SQL client " + ex.Message);  
        throw;  
    }  
    microsoft.lobservices.oracledb._2007._03.GenRecordRow[] sequenceRec =   
        new microsoft.lobservices.oracledb._2007._03.GenRecordRow[0];  
  
    try  
    {  
        sequenceRec = sqlClient.SQLEXECUTE("SELECT tid_seq.nextval id from DUAL", null, null);  
    }  
    catch (Exception ex)  
    {  
        Console.WriteLine("Error executing SQL client " + ex.Message);  
        throw;  
    }  
  
    if (sequenceRec.Length > 0)  
    {  
        Console.WriteLine("TID_SEQUENCE value is {0}", sequenceRec[0].GenRecordColumn[0].ColumnValue);  
    }  
    else  
    {  
    Console.WriteLine("Couldn't get next TID_SEQUENCE value");  
    }  
}  

See Also

Develop Oracle Database applications using the WCF Service Model