none
REST service to connect to Oracle Database RRS feed

  • Question

  • Hello Experts,

    I have created the Rest Endpoint service with the query parameter. Now that it will be that we will be connecting to their Oracle database and retrieve the data. I am not sure how this will be done. Do I create a Oracle DB binding and select the database and create the schema. Do I have to change the endpoint service and publish the schema that is created with the Orcale DB Binding.

    Thanks

    Monday, June 6, 2016 2:57 PM

Answers

  • Yes, you're on the right track.

    Keep in mind, the Oracle side of this has absolutely nothing to do with REST, JSON or how you've exposed that endpoint.  It doesn't know or care about any of that.  It's completely irrelevant.

    You accept you JSON Request, Map it to the Oracle Request Message, call Oracle, then Map the results to your JSON Response.

    This is as basic a BizTalk app as you can get.  Don't over think it.

    • Marked as answer by Sid_2014 Tuesday, June 7, 2016 6:07 PM
    Monday, June 6, 2016 3:13 PM
    Moderator
  • 1. No need to create a new service. Use your current schema with ID element. I believe this will be a GET request over webHttp adapter? In that case, the ID property in your property schema will be a promoted field in the input message. You can access this property from within an orchestration and set the required field/element in your Oracle request schema accordingly.

    2. Yes it can.


    Thanks Arindam


    Monday, June 6, 2016 3:51 PM
    Moderator

All replies

  • Hi Sid

    Refer these on how to query/interface with Oracle from BizTalk-

    http://soa-thoughts.blogspot.in/2011/04/table-operation-on-oracle-11g-xe-with.html

    http://biztalk2010changes.blogspot.in/2011/04/insert-update-delete-select-operation.html

    http://www.aajtech.com/blog/oracle-connectivity-in-biztalk-server-2013/

    Try out a similar POC yourself - you will have to run the Consume Adapter Service wizard to generate the Oracle schemas. Then using the binding created by the wizard, you can create the SendPort to Oracle. Note that all of this communication will happen over XML.

    Once you can get the communication with Oracle going, adapting that to your REST/JSON project will be pretty simple.

    You can send back this response from Oracle to the client in any format - if they require JSON, you can use the JSONEncode pipeline component in the Send pipeline of your 2-way ReceiveLocation.

    Also note that you should create separate a XML schema for sending back response from Oracle to client. The Oracle response schema should not be directly exposed to the calling client. Before sending the Oracle response, you can apply a map that converts from the Oracle schema to the XML schema - this new schema should closely mirror the fields actually needed by the calling client. 

    Regarding your query-

    Do I have to change the endpoint service and publish the schema that is created with the Orcale DB Binding.

    No. Keep the Oracle schemas internal to BizTalk. Create separate schemas for input message to BizTalk and output from BizTalk. You can use maps on both inbound/outbound side to convert to/from your client schema and Oracle schema.

    Refer this on installing the adapter, the WCF LOB SDK, and the ODAC client libraries.


    Thanks Arindam

    Monday, June 6, 2016 3:01 PM
    Moderator
  • Yes, you're on the right track.

    Keep in mind, the Oracle side of this has absolutely nothing to do with REST, JSON or how you've exposed that endpoint.  It doesn't know or care about any of that.  It's completely irrelevant.

    You accept you JSON Request, Map it to the Oracle Request Message, call Oracle, then Map the results to your JSON Response.

    This is as basic a BizTalk app as you can get.  Don't over think it.

    • Marked as answer by Sid_2014 Tuesday, June 7, 2016 6:07 PM
    Monday, June 6, 2016 3:13 PM
    Moderator
  • Yes I have done the Oracle DB connection before for selecting the records from the table. I understand that the Response from the Oracle Binding will be XML an from there we can easily convert in to JSON With the use of JSON encoder right. But what I usuall do is have a receive port which is scheduler with the query.

    But what I little confused is how I am going to publish the service for this. 

    1. Do I have to create new service by Just publish the schema that has been created from Oracle DB connection with the query parameter. Or can the existing schema be used, if it can be used how do we map them to the Oracle schema. Because current what my service is exposing the below schema

    2.Can this be a just a one way Request port instead of the Request Response if we not sending any response back to the client.

    Monday, June 6, 2016 3:47 PM
  • 1. No need to create a new service. Use your current schema with ID element. I believe this will be a GET request over webHttp adapter? In that case, the ID property in your property schema will be a promoted field in the input message. You can access this property from within an orchestration and set the required field/element in your Oracle request schema accordingly.

    2. Yes it can.


    Thanks Arindam


    Monday, June 6, 2016 3:51 PM
    Moderator
  • Sorry, I don't understand.

    1. For the Oracle side, there's nothing to publish.  You've already published your REST endpoint for the caller.

    The Oracle call behaves exactly the same as when triggered from a Scheduled task.  In that case, the Scheduled Task is the client.

    2. Well, you're not sending any response back to the REST caller?  If not, then yes, both can be one way.

    Monday, June 6, 2016 4:08 PM
    Moderator

  • 1. Do I have to create new service by Just publish the schema that has been created from Oracle DB connection with the query parameter. Or can the existing schema be used, if it can be used how do we map them to the Oracle schema. Because current what my service is exposing the below schema

    2.Can this be a just a one way Request port instead of the Request Response if we not sending any response back to the client.

    1)  You don't need to publish anything else now. You have have REST endpoint publish already. That will act as entry point into BizTalk. 

    Yes, The same schema can be used for further processing within BizTalk. You can have an orchestration wherein you can map the promoted property in input message to oracle request message which can then be processed to oracle. You can distinguish promote the field in oracle schema and assign it using xpath.

    OracleMsg.ID = MSG_IN(PropertySchema.ID);

    or

    xpath(OracleMsg,"xpath") = MSG_IN(PropertySchema.ID);

    2) Yes why not. If you are not sending any response message back to client you can just use one-way port.


    Rachit Sikroria (Microsoft Azure MVP)

    Monday, June 6, 2016 4:24 PM
    Moderator