Oracle SOA 12C (12.2.1) : Creating a Restful service with JSON Response using Service Bus 12c (OSB)

There are some changes made in Rest Adapter from previous Oracle SOA 12.1.3 version, in the new Oracle SOA version 12.2.1 they have removed a predefined request/response schema definition in WADL that we use while configuring rest adapter.

For Building a Rest GET Operation, there are two important ways.

Create params using Templates

We can get them from Inbound → user-metadata

Create params using QueryParameters

We can get them from inbound → query-parameters

Below is the Step By Step process for building a GET Operation for getting a JSON Response using Templates

Create a new Project

Name as “RestProject”

Now we will start creating a Rest Adapter

Name as “sayHello”

Adding a New “Resource path” as /{name}

Creating a New Method

Method: sayHello

Resource:/{name}

HTTP: GET

Now Create a New pipeline

Name as “HelloWorldPipeline”

Map them

DoubleClick on HelloWorldPipeline

Create a pipelinepairNode

Create a new Assign Activity and add below concat operation

---------------------------------------------------------------------------------------

concat('Hello ',$inbound/ctx:transport/ctx:request/tp:user-metadata/@value)

or

If you have more than one template use below xpath

concat('Hello ',$inbound/ctx:transport/ctx:request/tp:user-metadata[@name=’name’]/@value)

---------------------------------------------------------------------------------------

Now, for our scenario we need to send a JSON Response, so we need to create a NXSD to transform XML to JSON.

Create a new NXSD Schema

Add sample response { "Response":"Hello"}

Now In the response stage, insert nXSD Translate activity

In the input of nXSD, past below xml

--------------------------------------------------------------------

<Root-Element xmlns="http://TargetNamespace.com/ServiceName">

<Response>{$AssignRequest}</Response>

</Root-Element>

--------------------------------------------------------------------

Translate: XML to Native

nXSD Schema: xsd location

Output: Content of $body

Testing

Usefull Xquery's and XSLT's

Format Date

---------------------------------------------------------

<FormatRoot>

        <FormatDate>{ xs:date(fn:replace(fn:replace($Input/day,"(-|/| )",""),'(\d{2})(\d{2})(\d{4})', '$3-$1-$2')) } </FormatDate>

        <FormatInt>{fn-bea:decimal-truncate($Input/cnt)}</FormatInt>

       </FormatRoot>

---------------------------------------------------------

XSLT

xp20:format-dateTime(xp20:current-dateTime(),'[Y0001][M01][D01][h01][m01][s01][f01]')

OSB

fn-bea:dateTime-to-string-with-format("MM/dd/yyyy", xs:dateTime(data($clientApplication/ns0:planYearEffectiveDate))

Time in AM/PM

fn-bea:dateTime-to-string-with-format("MMM dd, YYYY hh:mm:ss a",fn:current-dateTime())

fn-bea:dateTime-from-string-with-format("MMM dd, YYYY hh:mm:ss a", "Dec 22, 2015 04:07:28 AM")

****************************************************************************************

Format Number in OSB

Xquery code to format any phone number to us format.

<ns0:phoneNbr>{fn:replace(fn:replace($phoneNbr,"\(|-|\)| ",""),'(\d{3})(\d{3})(\d{4})', '($1) $2-$3')}</ns0:phoneNbr>

****************************************************************************************

fn:string-join in OSB

Create a string from the node set in Assign using OSB

fn:string-join(

for $cmsep in $varDummiMTVResp1/gro:P_DIV_NUMBER_ITEM

return

data($cmsep/text()),',')

Create node set from delimited string

<BL4>{

for $id in fn:tokenize($FeinVariable/FEINList[1]/BL4,',')

return

<BL4List>{ $id }</BL4List>

    } </BL4>

**************************************************************************************** 

 local-name in OSB

<http:query-parameters>

{

 for $param in $body/request/*

 return

 <http:parameter name = "{local-name($param)}" value = "{data($param)}"/>

}

</http:query-parameters>

****************************************************************************************

Grouping in XQuery

*************************************************************************************

declare function xf:GroupTransform($groupSetup1 as element(ns1:GroupSetup))

    as element(ns0:GroupSetup) {

        <ns0:GroupSetup>

            <ns0:EmployerGroups>

                {

                    for $g in fn:distinct-values($groupSetup1/ns1:PartnerGroupInfo/ns1:Partner_Group_Number)

                    return

                        <ns0:EmployerGroupInformation>

                            <ns0:Employer>

                                <ns0:Name>{ $g }</ns0:Name>

                            </ns0:Employer>

                            <ns0:Plans>

                                {

                                for $grpset in $groupSetup1/ns1:PartnerGroupInfo

                                where $grpset[ns1:Partner_Group_Number eq $g]/ns1:Plan_ID/text() != ''

                                return

                                <ns0:plan>{ data($grpset[ns1:Partner_Group_Number eq $g]/ns1:Plan_ID/text()) }</ns0:plan>

                                }

                            </ns0:Plans>

                        </ns0:EmployerGroupInformation>

                }

            </ns0:EmployerGroups>

        </ns0:GroupSetup>

};

*************************************************************************************

 fn-bea:execute-sql

fn-bea:execute-sql('jdbc/soademoDB', xs:QName('ConfigData'),    'select ID,KEY,VALUE from demo_config where key = ?',

     $varKeyName)

fn-bea:execute-sql('jdbc/soademoDB',  xs:QName('ConfigData'),   $varKeyMulValues)

fn:concat("select ID,KEY,VALUE from demo_config where key like '",$body/ret:ConfigMultipleDataRequest/ret:KeyValueSearch/text(),"%'")

Adding Namespace

*************************************************************************************

xquery version "1.0" encoding "Cp1252";

(:: pragma parameter="$noNamespaceXML" type="xs:anyType" ::)

(:: pragma parameter="$namespaceURI" type="xs:string" ::)

(:: pragma type="xs:anyType" ::)

declare namespace xf = "<a href="http://tempuri.org/Resources/XQueries/addNamespace/">http://tempuri.org/Resources/XQueries/addNamespace/</a>";

declare function xf:addNamespaceToXML($noNamespaceXML as element(*),$namespaceURI as xs:string) as element(*)

{

element {fn:expanded-QName($namespaceURI,fn:local-name($noNamespaceXML))}

{

$noNamespaceXML/@*,

for $node in $noNamespaceXML/node()

return

if (exists($node/node())) then xf:addNamespaceToXML($node,$namespaceURI)

else if ($node instance of element()) then element {fn:expanded-QName($namespaceURI,fn:local-name($node))}{$node/@*}

else $node }

};

declare variable $noNamespaceXML as element(*) external;

declare variable $namespaceURI as xs:string external ; 

xf:addNamespaceToXML($noNamespaceXML, $namespaceURI)

*************************************************************************************

ora:setCompositeInstanceTitle($varTitle)

****************************************************************************************

oraext:query-database in xslt

oraext:query-database(concat("SELECT case_master_id FROM dbo.case_header WHERE case_header_number='",bpws:getVariableData('inputVariable','payload','/client:process/client:input'),"'"),false(),false(),"jdbc/SynergyDB")

concat("select isnull((select supplier_asn_nn from Supplier_Master where supplier_number='",$HeaderElement/ns1:HeaderElement/ns1:supplier_number,"'),0) next_num")

Update Sales_Order_Header Set Order_Status=90 Where Order_number=#Order_number and  0=(Select count(*) From Sales_Order_Header H,Sales_Order_Detail D Where H.Order_Number=D.Order_Number And  D.Order_Number=#Order_number_inner And D.Orderlinestatus Not In (330,900) ) 

concat(substring("00000",1,5-string-length(/client:process/client:input2)),/client:process/client:input2)

****************************************************************************************

oraext:max-value-among-nodeset in xslt

oraext:max-value-among-nodeset(oraext:query-database(("select delivery_number from dbo.home_delivery_header"),false(),false(),"jdbc/SynergyDB"))

****************************************************************************************

Grouping in XSLT

****************************************************************************************

  <xsl:template match="/">

    <ns1:POHeader>

    <xsl:variable name="distvalues"

             select="distinct-values(/ns0:PurchaseOrderDBOutputCollection/ns0:PurchaseOrderDBOutput/ns0:supplier_number)"/>

      <xsl:for-each select="$distvalues">  

          <ns1:PODetailCollection>

            <ns1:SupplierNumber>

              <xsl:value-of select="."/>

            </ns1:SupplierNumber>

            <xsl:variable name="supplierno" select="."/>

         <xsl:for-each select="/ns0:PurchaseOrderDBOutputCollection/ns0:PurchaseOrderDBOutput">

         <xsl:if test="ns0:supplier_number=$supplierno">

            <ns1:poDetailCollection>

              <ns1:PONumber>

                <xsl:value-of select="ns0:purchase_order_number"/>

              </ns1:PONumber>

              <ns1:asn>

                <xsl:value-of select="ns0:asn"/>

              </ns1:asn>

              <ns1:lpn>

                <xsl:value-of select="ns0:lpn"/>

              </ns1:lpn>

              <ns1:purchase_order_line_number>

                <xsl:value-of select="ns0:purchase_order_line_number"/>

              </ns1:purchase_order_line_number>

              <ns1:po_status>

                <xsl:value-of select="ns0:po_status"/>

              </ns1:po_status>

              <ns1:Sku>

                <xsl:value-of select="ns0:Sku"/>

              </ns1:Sku>

              <ns1:order_number>

                <xsl:value-of select="ns0:order_number"/>

              </ns1:order_number>

              <ns1:order_line_number>

                <xsl:value-of select="ns0:order_line_number"/>

              </ns1:order_line_number>

              <ns1:fulfilment_type>

                 <xsl:value-of select="ns0:fulfilment_type"/>

              </ns1:fulfilment_type>

            </ns1:poDetailCollection>

            </xsl:if>

         </xsl:for-each>

          </ns1:PODetailCollection>

        </xsl:for-each>

  

    </ns1:POHeader>

  </xsl:template>

</xsl:stylesheet>


OR

<?xml version="1.0"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
   <MailPayload>
<xsl:for-each-group select="/MailPayload/ActivityPayload" group-by="ProjectOwner">
    <ProjectOwnerWise>
<ProjectOwner>
  <xsl:value-of select="current-grouping-key()"/>
</ProjectOwner>
<Activities>
  <xsl:for-each select="current-group()">
   <IndActivity>
<xsl:copy-of                      select="ActivityId|ActivityName|ProjectId|ProjectName|ReviewerDecision|Comments"/>
   </IndActivity>
  </xsl:for-each>
</Activities>
</ProjectOwnerWise>
</xsl:for-each-group>
   </MailPayload>
  </xsl:template>
</xsl:stylesheet>

****************************************************************************************

oraext:create-nodeset-from-delimited-string

****************************************************************************************
<xsl:copy-of select='oraext:create-nodeset-from-delimited-string("{http://www.lauraashley.com/project42/DomainFailedFabricAllocation/CaseLines}CaseLineCollection",string(/client:process/client:Order_line_number),",")'>

****************************************************************************************

Custom Templates in xslt

****************************************************************************************

  </xsl:template> <!--  User Defined Templates  --> 

  <xsl:template name="CalculateTotalRegenOrderValue">

    <xsl:param name="ValueOfOrderLine"/>

    <xsl:param name="recursive_result"/>

    <xsl:choose>

      <xsl:when test="$ValueOfOrderLine">

        <xsl:variable name="OrderDetailType" select="$ValueOfOrderLine[1]"/>

        <xsl:call-template name="CalculateTotalRegenOrderValue">

          <xsl:with-param name="ValueOfOrderLine"

                          select="$ValueOfOrderLine[position() > 1]"/>

          <xsl:with-param name="recursive_result"

                          select="$recursive_result + $OrderDetailType/ns0:unit_price * $OrderDetailType/ns0:task_line_quantity"/>

        </xsl:call-template>

      </xsl:when>

      <xsl:otherwise>

        <xsl:value-of select="$recursive_result"/>

      </xsl:otherwise>

    </xsl:choose>

  </xsl:template>

calling

          <ns1:regenOrderValue>

          <xsl:call-template name="CalculateTotalRegenOrderValue">

            <xsl:with-param name="ValueOfOrderLine"

                            select="/ns0:GetRegenValuesOutputCollection/ns0:GetRegenValuesOutput"/>

            <xsl:with-param name="recursive_result" select="0.0"/>

          </xsl:call-template>

          <!--Here we are using custom function, To calculate the Total Tax Value from salesOrderDetails -->

        </ns1:regenOrderValue>

****************************************************************************************

Copying One Collection at a time in XSL

****************************************************************************************

  <xsl:param name="varCurrentInvoiceIndex"/>

  <xsl:variable name="varInvoiceIndex">

    <xsl:value-of select="$varCurrentInvoiceIndex/inp1:IndexCount/inp1:index"/>

  </xsl:variable>

  <xsl:template match="/">

  <xsl:comment>

        varInvoiceIndex : <xsl:value-of select="$varInvoiceIndex"/>

  </xsl:comment>

    <xsl:for-each select="/ns1:Transaction-810-InvoiceList/ns1:Transaction-810[position() = $varInvoiceIndex]">

      <xsl:copy-of select=".">

        <?oracle-xsl-mapper-position ns1:Transaction-810?>

      </xsl:copy-of>

    </xsl:for-each>

   

  </xsl:template>

</xsl:stylesheet>

****************************************************************************************

Java Embedded for opaque to String

****************************************************************************************

try{    

String inputStr = ((oracle.xml.parser.v2.XMLElement)getVariableData("receiveInput_B2BJMSConsume_Message_InputVariable","body","/ns3:opaqueElement")).getFirstChild().getNodeValue();   

addAuditTrailEntry("Input String : "+inputStr);   

oracle.soa.common.util.Base64Decoder decoder = new oracle.soa.common.util.Base64Decoder();       

String decodedData = decoder.decode(inputStr);   

addAuditTrailEntry("decodedData String : "+decodedData);   

setVariableData("varDecodedStr",decodedData);    

}catch(Exception e){    

}

****************************************************************************************

To Convert Opaque to String (Java Embedded)

****************************************************************************************

try{      

String opaqueDataToString=((oracle.xml.parser.v2.XMLElement)getVariableData("ReceiveCSVFile_Get_InputVariable","opaque","/ns2:opaqueElement")).getFirstChild().getNodeValue();      

addAuditTrailEntry("Input String : "+opaqueDataToString);       

oracle.soa.common.util.Base64Decoder decoder = new oracle.soa.common.util.Base64Decoder();           

String decodedData = decoder.decode(opaqueDataToString);       

addAuditTrailEntry("decodedData String : "+decodedData);       

setVariableData("csvData",decodedData);      

}catch(Exception E){      

   setVariableData("javaFlagVar", "E");       

}

****************************************************************************************

Changing the Namespace

****************************************************************************************

<?xml version="1.0" encoding="ISO-8859-1"?>

<myns:root xmlns:myns="http://zewaren.net/old_location/">

    <myns:jaime_les_patates>

        <myns:super_node> some content </myns:super_node>

    </myns:jaime_les_patates>

</myns:root>

However, the person who wrote it didn't know that the true identifier of the namespace of the document is not http://zewaren.net/old_location/, but rather http://zewaren.net/new_location/. So you need a style sheet to update the url.

<?xml version="1.0" encoding="iso-8859-1"?>

<!-- This sheet replaces the url of a namespace where needed -->

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns:oldmods="http://zewaren.net/old_location/"

    xmlns:myns="http://zewaren.net/new_location/">

     

    <xsl:template match="node()|@*">

        <xsl:copy>

            <xsl:apply-templates select="node()|@*"/>

        </xsl:copy>

    </xsl:template>

     

    <xsl:template match="@oldmods:*">

        <xsl:attribute name="myns:{local-name()}">

            <xsl:value-of select="."/>

        </xsl:attribute>

    </xsl:template>

     

    <xsl:template match="oldmods:*">

        <xsl:element name="myns:{local-name()}">

            <xsl:apply-templates select="node()|@*"/>

        </xsl:element>

    </xsl:template>

     

</xsl:stylesheet>

****************************************************************************************

Javascript for OSB to construct JSON

//Assign variables from pipeline
var $Request = process.actionRequest;

var $response = {
   "data": {
      "eid": "",
      "approvalMode": "",
      "systemId": "",
      "comments": "",
      "requestId": "",
      "approvalType": "",
      "approvalAction": ""
   }
};

//Populating json values
//Populating json values
$response.data.eid = $Request.eid.toString();
$response.data.approvalMode = $Request.approvalMode.toString();
$response.data.systemId = $Request.systemId.toString();
$response.data.comments = $Request.comments.toString();
$response.data.requestId = $Request.requestId.toString();
$response.data.approvalType = $Request.approvalType.toString();
$response.data.approvalAction = $Request.approvalAction.toString();


process.actionJSON = $response;