none
Calling SP using Oracle Adapter for BizTalk(Not using WCF adapter) RRS feed

  • Question

  • Hi,

    I want to know how to execute the sp of oracle using Oracle Adapter for BizTalk ( Not using WCF adapter).
    Please provide me any link which contains walk through.

    kumaraguru
    Saturday, March 28, 2009 9:38 AM

Answers

  • Here is an example of calling an Oracle SP using the BizTalk Adapter for Oracle Database: http://msdn.microsoft.com/en-us/library/aa578672.aspx. Here is a walkthrough for creating a schema for the SP: http://msdn.microsoft.com/en-us/library/aa560121.aspx.

    The starting point for all of this adapter's documentation is at http://msdn.microsoft.com/en-us/library/aa561124.aspx.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, March 30, 2009 1:33 AM
    Moderator
  • Below are the steps to call stored procedure for orcaledb receive adapters.

    This document shows how to use Events with a Stored Function that returns a Recordset using NativeSQL - it will also work when using the adapter as a two-way adapter – using the same statement that is used for the Poll Statement as the NativeSQL statement.  The screenshot shows that we are using the Oracle 10 client – it also works fine with the Oracle 9 client.

     

    Set up the function as follows (this script will run from SQLPlus):

     

    = = =

    CREATE OR REPLACE PACKAGE MSPKG AS

    TYPE ref_cursor IS REF CURSOR;

    FUNCTION SP_TEST RETURN ref_cursor;

    END MSPKG;

    /

     

    CREATE OR REPLACE PACKAGE BODY MSPKG AS

    FUNCTION SP_TEST RETURN ref_cursor IS MYRS ref_cursor;

    BEGIN

      OPEN MYRS FOR SELECT * FROM EMP;

      RETURN MYRS;

    END;

    END;

    /

    = = =

     

    This function will return all of the rows in the EMP table - - Make sure you have a valid EMP table or change the TABLE name in the Function.

     

    Set up the One-Way receive port and to call stored procedure put "Poll SQL Statement" as "{Call MSPKG.SP_TEST}".

    Choose "NativeSQL" in managing events.

    When you run it you will get XML output that looks like the following:

    <?

    xml version="1.0" encoding="utf-8" ?>

    <

    NativeSQL:SQLEvent xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:exposed="http://schemas.microsoft.com" xmlns:NativeSQL="http://schemas.microsoft.com/[OracleDb://EVTDBPSS/NativeSQL]">

    <

    NativeSQL:UpdatedRows>

    <

    NativeSQL:columnMetadata>

    <

    NativeSQL:columnMetadata>

    <

    exposed:colName>EMPNO</exposed:colName>

    <

    exposed:colType>string</exposed:colType>

    </

    NativeSQL:columnMetadata>

    <

    NativeSQL:columnMetadata>

    <

    exposed:colName>EMPNAME</exposed:colName>

    <

    exposed:colType>string</exposed:colType>

    </

    NativeSQL:columnMetadata>

    </

    NativeSQL:columnMetadata>

    <

    NativeSQL:rowData>

    <

    NativeSQL:columnData>

    <

    NativeSQL:string>902</NativeSQL:string>

    <

    NativeSQL:string>John Smith</NativeSQL:string>

    </

    NativeSQL:columnData>

    </

    NativeSQL:rowData>

    </

    NativeSQL:UpdatedRows>

    </

    NativeSQL:SQLEvent>

     

     

     


    Sanjay
    Monday, March 30, 2009 5:44 AM

All replies

  • Here is an example of calling an Oracle SP using the BizTalk Adapter for Oracle Database: http://msdn.microsoft.com/en-us/library/aa578672.aspx. Here is a walkthrough for creating a schema for the SP: http://msdn.microsoft.com/en-us/library/aa560121.aspx.

    The starting point for all of this adapter's documentation is at http://msdn.microsoft.com/en-us/library/aa561124.aspx.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Monday, March 30, 2009 1:33 AM
    Moderator
  • Below are the steps to call stored procedure for orcaledb receive adapters.

    This document shows how to use Events with a Stored Function that returns a Recordset using NativeSQL - it will also work when using the adapter as a two-way adapter – using the same statement that is used for the Poll Statement as the NativeSQL statement.  The screenshot shows that we are using the Oracle 10 client – it also works fine with the Oracle 9 client.

     

    Set up the function as follows (this script will run from SQLPlus):

     

    = = =

    CREATE OR REPLACE PACKAGE MSPKG AS

    TYPE ref_cursor IS REF CURSOR;

    FUNCTION SP_TEST RETURN ref_cursor;

    END MSPKG;

    /

     

    CREATE OR REPLACE PACKAGE BODY MSPKG AS

    FUNCTION SP_TEST RETURN ref_cursor IS MYRS ref_cursor;

    BEGIN

      OPEN MYRS FOR SELECT * FROM EMP;

      RETURN MYRS;

    END;

    END;

    /

    = = =

     

    This function will return all of the rows in the EMP table - - Make sure you have a valid EMP table or change the TABLE name in the Function.

     

    Set up the One-Way receive port and to call stored procedure put "Poll SQL Statement" as "{Call MSPKG.SP_TEST}".

    Choose "NativeSQL" in managing events.

    When you run it you will get XML output that looks like the following:

    <?

    xml version="1.0" encoding="utf-8" ?>

    <

    NativeSQL:SQLEvent xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:exposed="http://schemas.microsoft.com" xmlns:NativeSQL="http://schemas.microsoft.com/[OracleDb://EVTDBPSS/NativeSQL]">

    <

    NativeSQL:UpdatedRows>

    <

    NativeSQL:columnMetadata>

    <

    NativeSQL:columnMetadata>

    <

    exposed:colName>EMPNO</exposed:colName>

    <

    exposed:colType>string</exposed:colType>

    </

    NativeSQL:columnMetadata>

    <

    NativeSQL:columnMetadata>

    <

    exposed:colName>EMPNAME</exposed:colName>

    <

    exposed:colType>string</exposed:colType>

    </

    NativeSQL:columnMetadata>

    </

    NativeSQL:columnMetadata>

    <

    NativeSQL:rowData>

    <

    NativeSQL:columnData>

    <

    NativeSQL:string>902</NativeSQL:string>

    <

    NativeSQL:string>John Smith</NativeSQL:string>

    </

    NativeSQL:columnData>

    </

    NativeSQL:rowData>

    </

    NativeSQL:UpdatedRows>

    </

    NativeSQL:SQLEvent>

     

     

     


    Sanjay
    Monday, March 30, 2009 5:44 AM
  • Hi Sanjay,

    I implemented ur steps and i generated following schema. Instead of EMP table i used my table called "tblbiztalk". I called function in poll sql statement as {Call MSPKG.SP_BIZTALK}.

    I followed following steps.

    1) Created one way receive port and location (configured that with oracle connection string and service).
    2) In Managing Event property of same receive location selected Native SQL option.
    3) In Biztalk solution, generated schema using same receive location (right click on project-->add generated items --> Add Adapter metadata, in this wizard i m using "Native SQL" option in Service to import page) through this wizard i m using configured receive location and generating schema.

    But not able to see the column names, how do i do this? is their any way to do this through oracle procedure?

    <?xml version="1.0" encoding="utf-16" ?>

    - <xsd:schema xmlns:exposed="http://schemas.microsoft.com" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:NativeSQL="http://schemas.microsoft.com/[OracleDb://WORKFLOW/NativeSQL]" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/[OracleDb://WORKFLOW/NativeSQL]" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

    <xsd:import schemaLocation=".\NativeSQLService.xsd" namespace="http://schemas.microsoft.com" />

    - <xsd:annotation>

    - <xsd:appinfo>

    - <references xmlns="http://schemas.microsoft.com/BizTalk/2003">

    <reference targetNamespace="http://schemas.microsoft.com" />

    </references>

    </xsd:appinfo>

    </xsd:annotation>

    - <xsd:complexType name="parameterData">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="string" type="xsd:string" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:complexType name="parameters">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="parameterData" type="NativeSQL:parameterData" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:element name="SQLExecute">

    - <xsd:complexType>

    - <xsd:sequence>

    <xsd:element name="StatementText" type="xsd:string" />

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="ParameterSet" type="NativeSQL:parameters" />

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    - <xsd:complexType name="columnMetadataSeq">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="columnMetadata" type="exposed:columnMetadata" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:complexType name="columnData">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="string" type="xsd:string" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:complexType name="rowData">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="columnData" type="NativeSQL:columnData" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:complexType name="ResultSet">

    - <xsd:sequence>

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="columnMetadata" type="NativeSQL:columnMetadataSeq" />

    <xsd:element minOccurs="0" maxOccurs="unbounded" name="rowData" type="NativeSQL:rowData" />

    </xsd:sequence>

    </xsd:complexType>

    - <xsd:element name="SQLExecuteResponse">

    - <xsd:complexType>

    - <xsd:sequence>

    <xsd:element name="Return" type="NativeSQL:ResultSet" />

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    - <xsd:element name="SQLEvent">

    - <xsd:complexType>

    - <xsd:sequence>

    <xsd:element name="UpdatedRows" type="NativeSQL:ResultSet" />

    </xsd:sequence>

    </xsd:complexType>

    </xsd:element>

    - <xsd:element name="SQLEventResponse">

    - <xsd:complexType>

    <xsd:sequence />

    </xsd:complexType>

    </xsd:element>

    </xsd:schema>

    Regards
    Girish Fuluskar

    Monday, March 15, 2010 8:25 AM