none
Oracle Adapter Question RRS feed

  • Question

  • Hi,
     
    I'm using Oracle adapter to fetch the records from the oracle database table. Like SQL Adapter,Can I use stored procedure to extract the schema from the database? if yes, What extra needs to be written in stored proc to fetch as an schema (like We have for XML auto, xmldata in sqL adapater).

    Another question, how could I have where clause if oracle adapter is configured using Managed Event?

    .Nilesh
    Tuesday, January 13, 2009 10:26 AM

Answers

  • Here is the complete description on how to receive data from oracle using oracle adapter with NativeSQL and TableChangeEevent.I hope this solves your problem.

     

    There are two ways of receiving the data from Oracle - - both of which involve us Polling Oracle in order to get the data.  One way is to use the NativeSQL SQLEvent method and the other way is to use the TableChangeEvent method.  The SQLEvent method allows for more flexibility, however the format of the data that is received may involve more steps to process.

     

    Here is a sample of what the data coming back from the TableChangeEvent looks like:

     

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

    - <VTEMP_TARGET:TableChangeEvent xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:exposed="http://schemas.microsoft.com" xmlns:VTEMP_TARGET="http://schemas.microsoft.com/[OracleDb://RCVDBPSS/PSSUSR/Tables/VTEMP_TARGET]">

    - <VTEMP_TARGET:UpdatedRows>

    - <VTEMP_TARGET:EventRecord>

      <VTEMP_TARGET:EMPNO>287</VTEMP_TARGET:EMPNO>

      <VTEMP_TARGET:EMPNAME>Abbott</VTEMP_TARGET:EMPNAME>

      <VTEMP_TARGET:UPDATE_TYPE>UPDATED</VTEMP_TARGET:UPDATE_TYPE>

      <VTEMP_TARGET:ROWID>AAAYmaAAJAAAAAwAAA</VTEMP_TARGET:ROWID>

      </VTEMP_TARGET:EventRecord>

      </VTEMP_TARGET:UpdatedRows>

      </VTEMP_TARGET:TableChangeEvent>

     

    Here is a sample of what the data coming back from the SQLEvent looks like:

     

      <?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>

     

    Note that the TableChangeEvent includes the Column names with the data where the SQLEvent includes a list of the column names at the beginning of the data and then the data for each of those columns follows.  In the sample data above, the events were processed against the same table – the TableChangeEvent data includes ALL of the columns in the table – including the ROWID information, where the data for the SQLEvent only includes the columns that I specify in my Poll statement.  Furthermore, with the SQLEvent I am able to process a Post Poll statement where I can update a column so it will not be returned in another Poll - - with the TableChangeEvent my only option is to delete the rows that have been Polled.


    Steps to use the TableChangeEvent:

     

    In the BizTalk Manager – create a new application named OracleEvents

    Right-click Receive Ports – choose New – One-way Receive Port…

    Name it OracleRcvPort

    Click on Receive Locations in the left pane

    Click New… in the right pane

    Name: OraRcvLoc

    Type: OracleDB Adapter

    Receive pipeline: XMLReceive

     

    Click the Configure… button

     

    Enter the Password, PATH, Service name and User name

    Click OK to save the configuration

    Go back into the configuration (click the Configure… button)

    Scroll down to Managing Events – click in the text box and click on the … button

    Browse out to the table that you will be getting the information from - - note – choose the actual table and not one of the methods under the table.

    Click OK

     

    In the configuration panel there is an option for Delete after poll - - set that value to True (although, for testing purposes you may want to leave it at False – that will mean that every time a Poll is done, the entire contents of the Table will be returned – for a production application you will not want that since it would mean that you would be processing records twice).

     

    Note that there is also a Poll and Post Poll SQL Statement in the configuration – if you click on one of those you will notice it states that these apply only if using the NativeSQL SQLEvent method (which will be described later in this document).

     

    Click on OK – OK – OK

     

    Now you have the Receive Port prepared and are ready to create the BizTalk Application in Visual Studio.

     

    Open an Empty BizTalk Application within Visual Studio – name it OracleEvents.

     

    Go to the Properties for OracleEvents –

      Common Properties – Assembly – Assembly Key File --- set this to a valid .snk file

      Configuration Properties – Deployment – Application Name --- OracleEvents (the same name you created in the BizTalk manager where you created the Receive Port)

    Click OK

     

    Now go into Add – Add Generated Items…

    Point to the Receive Port that was created – and go to the same table that you entered for the Managing Events entry – Click Finish

     

    There will be an Orchestration and two .xsd files created – you can delete the tblname_obj.xsd file.

     

    Open the Orchestration -

     

    On the right port surface create a New Configured Port – name: ReceiveFromOracle

    Make it a One Way port that will be Receiving messages and specify Port Bindings later.

    Click on ‘Request’ inside of the ReceiveFromOracle port – in the Properties, set the Message Type to Multi-part Message Types – OracleEvents.TableChangeEvent

     

    On the left port surface create a New Configured Port – name: ToDisk

    Make it a One Way port that will be Sending messages and specify Port Bindings later.

     

    Insert a Receive component into the orchestration – name: FromOracle  Set Activate: True

    Connect the ReceiveFromOracle Port location to this Receive Component

      (Note – the message type will be Multi-part Message Types – OracleEvents.TableChangeEvent)

     

    Insert a Send component after the Receive – name: SendToDisk

    Connect SendToDisk to the ToDisk Port

       

      

    Now compile and deploy the Orchestration.  In the BizTalk Manager you will need to refresh the application and then configure it to point to the Oracle receive port and create a new Send port that uses the FILE adapter and writes to a folder.

     

    Then start the BizTalk Application.

     

    If there is any data in the table – it will be pulled down and you will see an xml document show up in the folder.  If you did not state to delete the rows after a poll, then you will continue to see data pulled down every 60 seconds.

     


    Steps to use the SQLEvent:

     

    In the BizTalk Manager – create a new application named OraEvent2

    Right-click Receive Ports – choose New – One-way Receive Port…

    Name it OraSQLEventPort

    Click on Receive Locations in the left pane

    Click New… in the right pane

    Name: OraSQLEventLoc

    Type: OracleDB Adapter

    Receive pipeline: XMLReceive

     

    Click the Configure… button

     

    Enter the Password, PATH, Service name and User name

    Click OK to save the configuration

    Go back into the configuration (click the Configure… button)

    Scroll down to Managing Events – click in the text box and click on the … button

    Open the top item (the name of your Oracle adapter)

    Open the second item (the name of your ODBC connection)

    Scroll to the bottom and highlight NativeSQL – click on the right arrow button

    Click OK

     

    In the configuration panel there is an option for Poll SQL Statement – here is where the SQL statement to be used for Polling should go – for example, my statement is:

     

    SELECT EMPNO, EMPNAME FROM VTEMP_TARGET WHERE UPDATE_TYPE='INSERT'

     

    Then go to the Post Poll SQL Statement – this is where you can issue the Post Poll statement which is only executed if the Poll statement was successful and no problems occurred in the Orchestration.  My statement is:

     

    UPDATE VTEMP_TARGET SET UPDATE_TYPE='PROCESSED' WHERE UPDATE_TYPE='INSERT'

     

    Notice – this statement changes the entry so that I will not keep retrieving back the same rows when I poll the table.  This way I can keep the rows to see what HAS been processed.

     

    When the Poll statements have been configured, click on OK – OK

     

    Now you have the Receive Port prepared and are ready to create the BizTalk Application in Visual Studio.

     

    Open an Empty BizTalk Application within Visual Studio – name it OraEvent2.

     

    Go to the Properties for OraEvent2 –

      Common Properties – Assembly – Assembly Key File --- set this to a valid .snk file

      Configuration Properties – Deployment – Application Name --- OraEvent2 (the same name you created in the BizTalk manager where you created the Receive Port)

    Click OK

     

    Now go into Add – Add Generated Items…

    Point to the Receive Port that was created – and go to the same NativeSQL you entered for the Managing Events entry – Click Finish

     

    There will be an Orchestration and three .xsd files created – you can delete the NativeSQLService_obj.xsd file.

     

    Open the Orchestration -

     

    On the right port surface create a New Configured Port – name: FromOracle

    Make it a One Way port that will be Receiving messages and specify Port Bindings later.

    Click on ‘Request’ inside of the FromOracle port – in the Properties, set the Message Type to Multi-part Message Types – OraEvent2.SQLEvent

     

    On the left port surface create a New Configured Port – name: ToDisk

    Make it a One Way port that will be Sending messages and specify Port Bindings later.

     

    Insert a Receive component into the orchestration – Set Activate: True

    Connect the FromOracle Port location to this Receive Component

      (Note – the message type will be Multi-part Message Types – OraEvent2.SQLEvent)

     

    Insert a Send component after the Receive

    Connect the Send component to the ToDisk Port

     

     

     

    Now compile and deploy the Orchestration.  In the BizTalk Manager you will need to refresh the application and then configure it to point to the Oracle receive port and create a new Send port that uses the FILE adapter and writes to a folder.

     

    Then start the BizTalk Application.

     

    If there is any data in the table – it will be pulled down and you will see an xml document show up in the folder.  Any changes made to the Oracle table where the data matches the Poll statement will be pulled down every 60 seconds when the poll occurs.

     

    • Marked as answer by a.Nilesh Thursday, January 22, 2009 6:57 AM
    Wednesday, January 21, 2009 7:48 AM

All replies

  • If you're using the WCF Oracle Adapter (as part of the BizTalk Adapter Pack), see if this helps ...http://www.topxml.com/code/cod-491_15138_biztalk-and-wcf-part-vii-about-the-biztalk-adapter-pack.aspx.  If you're using the classic Oracle adapter, you can find a number of examples in the documentation and online (http://msdn.microsoft.com/en-us/library/aa560681.aspxhttp://seroter.wordpress.com/2007/03/12/a-walk-through-the-biztalk-2006-oracle-adapter/).
    Tuesday, January 13, 2009 8:07 PM
  •   Hi,

    I have the following scenario in the project;

    1) Receive the newly added data from the Oracle Database table. I'm using Oracle Adapter for the same.
    2) Map the retrieved data to the schema using transform.

    I'm using NativeSQL of Managing event available in ORacle adapter. I didn't get any fields for mapping when I used NAtiveSQL. I could see NativeSQLResponse on the left side of the map.

    How could I get the newly added rows and map them to the schema?
    .Nilesh
    Wednesday, January 14, 2009 11:23 AM
  • Here is the complete description on how to receive data from oracle using oracle adapter with NativeSQL and TableChangeEevent.I hope this solves your problem.

     

    There are two ways of receiving the data from Oracle - - both of which involve us Polling Oracle in order to get the data.  One way is to use the NativeSQL SQLEvent method and the other way is to use the TableChangeEvent method.  The SQLEvent method allows for more flexibility, however the format of the data that is received may involve more steps to process.

     

    Here is a sample of what the data coming back from the TableChangeEvent looks like:

     

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

    - <VTEMP_TARGET:TableChangeEvent xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:exposed="http://schemas.microsoft.com" xmlns:VTEMP_TARGET="http://schemas.microsoft.com/[OracleDb://RCVDBPSS/PSSUSR/Tables/VTEMP_TARGET]">

    - <VTEMP_TARGET:UpdatedRows>

    - <VTEMP_TARGET:EventRecord>

      <VTEMP_TARGET:EMPNO>287</VTEMP_TARGET:EMPNO>

      <VTEMP_TARGET:EMPNAME>Abbott</VTEMP_TARGET:EMPNAME>

      <VTEMP_TARGET:UPDATE_TYPE>UPDATED</VTEMP_TARGET:UPDATE_TYPE>

      <VTEMP_TARGET:ROWID>AAAYmaAAJAAAAAwAAA</VTEMP_TARGET:ROWID>

      </VTEMP_TARGET:EventRecord>

      </VTEMP_TARGET:UpdatedRows>

      </VTEMP_TARGET:TableChangeEvent>

     

    Here is a sample of what the data coming back from the SQLEvent looks like:

     

      <?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>

     

    Note that the TableChangeEvent includes the Column names with the data where the SQLEvent includes a list of the column names at the beginning of the data and then the data for each of those columns follows.  In the sample data above, the events were processed against the same table – the TableChangeEvent data includes ALL of the columns in the table – including the ROWID information, where the data for the SQLEvent only includes the columns that I specify in my Poll statement.  Furthermore, with the SQLEvent I am able to process a Post Poll statement where I can update a column so it will not be returned in another Poll - - with the TableChangeEvent my only option is to delete the rows that have been Polled.


    Steps to use the TableChangeEvent:

     

    In the BizTalk Manager – create a new application named OracleEvents

    Right-click Receive Ports – choose New – One-way Receive Port…

    Name it OracleRcvPort

    Click on Receive Locations in the left pane

    Click New… in the right pane

    Name: OraRcvLoc

    Type: OracleDB Adapter

    Receive pipeline: XMLReceive

     

    Click the Configure… button

     

    Enter the Password, PATH, Service name and User name

    Click OK to save the configuration

    Go back into the configuration (click the Configure… button)

    Scroll down to Managing Events – click in the text box and click on the … button

    Browse out to the table that you will be getting the information from - - note – choose the actual table and not one of the methods under the table.

    Click OK

     

    In the configuration panel there is an option for Delete after poll - - set that value to True (although, for testing purposes you may want to leave it at False – that will mean that every time a Poll is done, the entire contents of the Table will be returned – for a production application you will not want that since it would mean that you would be processing records twice).

     

    Note that there is also a Poll and Post Poll SQL Statement in the configuration – if you click on one of those you will notice it states that these apply only if using the NativeSQL SQLEvent method (which will be described later in this document).

     

    Click on OK – OK – OK

     

    Now you have the Receive Port prepared and are ready to create the BizTalk Application in Visual Studio.

     

    Open an Empty BizTalk Application within Visual Studio – name it OracleEvents.

     

    Go to the Properties for OracleEvents –

      Common Properties – Assembly – Assembly Key File --- set this to a valid .snk file

      Configuration Properties – Deployment – Application Name --- OracleEvents (the same name you created in the BizTalk manager where you created the Receive Port)

    Click OK

     

    Now go into Add – Add Generated Items…

    Point to the Receive Port that was created – and go to the same table that you entered for the Managing Events entry – Click Finish

     

    There will be an Orchestration and two .xsd files created – you can delete the tblname_obj.xsd file.

     

    Open the Orchestration -

     

    On the right port surface create a New Configured Port – name: ReceiveFromOracle

    Make it a One Way port that will be Receiving messages and specify Port Bindings later.

    Click on ‘Request’ inside of the ReceiveFromOracle port – in the Properties, set the Message Type to Multi-part Message Types – OracleEvents.TableChangeEvent

     

    On the left port surface create a New Configured Port – name: ToDisk

    Make it a One Way port that will be Sending messages and specify Port Bindings later.

     

    Insert a Receive component into the orchestration – name: FromOracle  Set Activate: True

    Connect the ReceiveFromOracle Port location to this Receive Component

      (Note – the message type will be Multi-part Message Types – OracleEvents.TableChangeEvent)

     

    Insert a Send component after the Receive – name: SendToDisk

    Connect SendToDisk to the ToDisk Port

       

      

    Now compile and deploy the Orchestration.  In the BizTalk Manager you will need to refresh the application and then configure it to point to the Oracle receive port and create a new Send port that uses the FILE adapter and writes to a folder.

     

    Then start the BizTalk Application.

     

    If there is any data in the table – it will be pulled down and you will see an xml document show up in the folder.  If you did not state to delete the rows after a poll, then you will continue to see data pulled down every 60 seconds.

     


    Steps to use the SQLEvent:

     

    In the BizTalk Manager – create a new application named OraEvent2

    Right-click Receive Ports – choose New – One-way Receive Port…

    Name it OraSQLEventPort

    Click on Receive Locations in the left pane

    Click New… in the right pane

    Name: OraSQLEventLoc

    Type: OracleDB Adapter

    Receive pipeline: XMLReceive

     

    Click the Configure… button

     

    Enter the Password, PATH, Service name and User name

    Click OK to save the configuration

    Go back into the configuration (click the Configure… button)

    Scroll down to Managing Events – click in the text box and click on the … button

    Open the top item (the name of your Oracle adapter)

    Open the second item (the name of your ODBC connection)

    Scroll to the bottom and highlight NativeSQL – click on the right arrow button

    Click OK

     

    In the configuration panel there is an option for Poll SQL Statement – here is where the SQL statement to be used for Polling should go – for example, my statement is:

     

    SELECT EMPNO, EMPNAME FROM VTEMP_TARGET WHERE UPDATE_TYPE='INSERT'

     

    Then go to the Post Poll SQL Statement – this is where you can issue the Post Poll statement which is only executed if the Poll statement was successful and no problems occurred in the Orchestration.  My statement is:

     

    UPDATE VTEMP_TARGET SET UPDATE_TYPE='PROCESSED' WHERE UPDATE_TYPE='INSERT'

     

    Notice – this statement changes the entry so that I will not keep retrieving back the same rows when I poll the table.  This way I can keep the rows to see what HAS been processed.

     

    When the Poll statements have been configured, click on OK – OK

     

    Now you have the Receive Port prepared and are ready to create the BizTalk Application in Visual Studio.

     

    Open an Empty BizTalk Application within Visual Studio – name it OraEvent2.

     

    Go to the Properties for OraEvent2 –

      Common Properties – Assembly – Assembly Key File --- set this to a valid .snk file

      Configuration Properties – Deployment – Application Name --- OraEvent2 (the same name you created in the BizTalk manager where you created the Receive Port)

    Click OK

     

    Now go into Add – Add Generated Items…

    Point to the Receive Port that was created – and go to the same NativeSQL you entered for the Managing Events entry – Click Finish

     

    There will be an Orchestration and three .xsd files created – you can delete the NativeSQLService_obj.xsd file.

     

    Open the Orchestration -

     

    On the right port surface create a New Configured Port – name: FromOracle

    Make it a One Way port that will be Receiving messages and specify Port Bindings later.

    Click on ‘Request’ inside of the FromOracle port – in the Properties, set the Message Type to Multi-part Message Types – OraEvent2.SQLEvent

     

    On the left port surface create a New Configured Port – name: ToDisk

    Make it a One Way port that will be Sending messages and specify Port Bindings later.

     

    Insert a Receive component into the orchestration – Set Activate: True

    Connect the FromOracle Port location to this Receive Component

      (Note – the message type will be Multi-part Message Types – OraEvent2.SQLEvent)

     

    Insert a Send component after the Receive

    Connect the Send component to the ToDisk Port

     

     

     

    Now compile and deploy the Orchestration.  In the BizTalk Manager you will need to refresh the application and then configure it to point to the Oracle receive port and create a new Send port that uses the FILE adapter and writes to a folder.

     

    Then start the BizTalk Application.

     

    If there is any data in the table – it will be pulled down and you will see an xml document show up in the folder.  Any changes made to the Oracle table where the data matches the Poll statement will be pulled down every 60 seconds when the poll occurs.

     

    • Marked as answer by a.Nilesh Thursday, January 22, 2009 6:57 AM
    Wednesday, January 21, 2009 7:48 AM
  • Thanks a lot for the detailed information.

    Genuinely appreciate.
    .Nilesh
    Thursday, January 22, 2009 6:57 AM