Friday, July 15, 2016

SQL WHERE IN CLAUSE ORACLE SERVICE BUS EXECUTE PL-SQL

Hi,

I would like to post the code for using the Where in clause through PL - SQL statement

let $sqlStat := fn:concat('SELECT EMAIL_ID,EMAIL_ADDRESS,SUBJECT,BODY,TEMPLATE_ID FROM ESB_EMAIL_TB WHERE EMAIL_ID IN (',let $eml_ids := concat(for $eml_id in $body/ema:reSendEmailRequest/ema:email_ID return concat($eml_id,',')) return substring($eml_ids,1,string-length($eml_ids)-1),')')
return
<email_data>{
fn-bea:execute-sql('jdbc/ccsit_ds', xs:QName('emailRecords'), $sqlStat)
}
</email_data>

The select statement with the where in clause is simple. What tricky here is the use of the XQuery to populate the value of the email id which came as unbounded from the request.

Hence the select statement would eventually result like following

SELECT EMAIL_ID,EMAIL_ADDRESS,SUBJECT,BODY,TEMPLATE_ID FROM ESB_EMAIL_TB WHERE EMAIL_ID IN ('<email_id1>','<email_id2>','<email_id3>'....)

The PL-SQL is preferred for select usecases where DBADAPTER configuration is complicated when coding for multiple select ids.

Hope this post helps somewhere.


<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
<!-- MyAdUnit -->
<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-2141952672790293"
     data-ad-slot="6953290368"
     data-ad-format="auto"></ins>
<script>
(adsbygoogle = window.adsbygoogle || []).push({});
</script>

Thursday, November 19, 2015

Groovy Script to generate Multiple Response in SOAP UI

Recently I had a scenario where I had to generate multiple responses on the basis of number of request's element occurrence.

For example the request is as below containing 5 messageId element

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="***">
   <soapenv:Header/>
   <soapenv:Body>
      <web:getSmsStatusList>
        <SmsStatusRequest>
            <messageId>4869754</messageId>
            <messageId>3079075</messageId>
            <messageId>7129441</messageId>
            <messageId>2928236</messageId>
            <messageId>7682597</messageId>
         </SmsStatusRequest>
      </web:getSmsStatusList>
   </soapenv:Body>
</soapenv:Envelope>


I was expecting a response like 5 response element smsStatusItem,

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:web="****">
   <soapenv:Header/>
   <soapenv:Body>
      <web:getSmsStatusListResponse>
         <!--Optional:-->
         <SmsStatusResponse>
            <!--Zero or more repetitions:-->
            <smsStatusItem>
               <messageId>4869754</messageId>
               <smsStatus>
                  <statusCode>0</statusCode>
               </smsStatus>
            </smsStatusItem>
            <smsStatusItem>
               <messageId>3079075</messageId>
               <smsStatus>
                  <statusCode>0</statusCode>
               </smsStatus>
            </smsStatusItem>
            <smsStatusItem>
               <messageId>7129441</messageId>
               <smsStatus>
                  <statusCode>2</statusCode>
               </smsStatus>
            </smsStatusItem>
            <smsStatusItem>
               <messageId>2928236</messageId>
               <smsStatus>
                  <statusCode>0</statusCode>
               </smsStatus>
            </smsStatusItem>
            <smsStatusItem>
               <messageId>7682597</messageId>
               <smsStatus>
                  <statusCode>0</statusCode>
               </smsStatus>
            </smsStatusItem>
         </SmsStatusResponse>
      </web:getSmsStatusListResponse>
   </soapenv:Body>
</soapenv:Envelope>

For this purpose, SOAP UI supports groovy scripting, and I created the following groovy script.

import groovy.xml.MarkupBuilder

// An array from which county and city will be drawn randomly
def statusArray = [2,0]

def random = new Random()

// create XmlHolder for request content
def holder = new com.eviware.soapui.support.XmlHolder( mockRequest.requestContent )

// Get the name and age values from the request
def requestItems = holder.getNodeValues( "//*:getSmsStatusList/SmsStatusRequest/messageId")

def writer = new StringWriter()
def smsElements = new MarkupBuilder(writer)

// Build the response elements
for (int index = 0; index < requestItems.size(); index =index + 1) {

    smsElements.'smsStatusItem'() {
        'messageId'(Integer.parseInt(requestItems[index]))
         'smsStatus'()
         {
          def randomIndex = random.nextInt(statusArray.size())
       'statusCode'(statusArray[randomIndex])
         }
                                                     }
                                                                                                         }

// Add the newly created elements to the response
context.smsElements = writer.toString()

Below screenshot tells you where you can define the groovy script and how you can add its element to the response of the mock service.



 

Sunday, November 08, 2015

JSON to XML translation using Generic XML Format - Numeric XML Tags

Recently I came across a scenario where JSON to XML native builder format available in Oracle SOA Suite 12c produced Invalid XML with there Schema.

Every JSON Document can be represented by a generic XML where the JSON keys and values both can be represented as values of some XML tag. This would certainly avoid cases resulting in JSON to XML translation of Invalid XML when a json key is a numeric string and would result in numeric XML tag.

So to avoid those cases, I leveraged a java library to convert JSON to XML for which takes JSON input and gives generic XML output. Though this functionality can be achieved by various languages apart from java, the basic idea was to represent any JSON document with a generic XML structure. Here is an example.

{
"glossary": {
"4564": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"7001": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"4523": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}


OUTPUT

<json xmlns="http://json.to.xml.for.osb">

<object>

<field name="glossary">

<object>

<field name="4564">

<string value="example glossary"/>

</field>

<field name="GlossDiv">

<object>

<field name="title">

<string value="S"/>

</field>

<field name="GlossList">

<object>

<field name="GlossEntry">

<object>

<field name="ID">

<string value="SGML"/>

</field>

<field name="SortAs">

<string value="SGML"/>

</field>

<field name="GlossTerm">

<string value="Standard Generalized Markup Language"/>

</field>

<field name="7001">

<string value="SGML"/>

</field>

<field name="Abbrev">

<string value="ISO 8879:1986"/>

</field>

<field name="GlossDef">

<object>

<field name="para">

<string value="A meta-markup language, used to create markup languages such as DocBook."/>

</field>

<field name="4523">

<array>

<string value="GML"/>

<string value="XML"/>

</array>

</field>

</object>

</field>

<field name="GlossSee">

<string value="markup"/>

</field>

</object>

</field>

</object>

</field>

</object>

</field>

</object>

</field>

</object>

</json>


The schema for this generic format is as follows 

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://json.to.xml.for.osb"
           xmlns:tns="http://json.to.xml.for.osb" elementFormDefault="qualified">
  <xs:simpleType name="trueFalse">
    <xs:restriction base="xs:string">
      <xs:enumeration value="true"/>
      <xs:enumeration value="false"/>
    </xs:restriction>
  </xs:simpleType>
  <xs:complexType name="number">
    <xs:attribute name="value" type="xs:double"/>
  </xs:complexType>
  <xs:complexType name="string">
    <xs:attribute name="value" type="xs:string"/>
  </xs:complexType>
  <xs:complexType name="boolean">
    <xs:attribute name="value" type="tns:trueFalse"/>
  </xs:complexType>
  <xs:complexType name="null"/>
  <xs:complexType name="object">
    <xs:sequence>
      <xs:element name="field" type="tns:field" minOccurs="0" maxOccurs="unbounded"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="array">
    <xs:sequence minOccurs="0" maxOccurs="unbounded">
      <xs:choice>
        <xs:element name="object" type="tns:object"/>
        <xs:element name="array" type="tns:array"/>
        <xs:element name="string" type="tns:string"/>
        <xs:element name="number" type="tns:number"/>
        <xs:element name="boolean" type="tns:boolean"/>
        <xs:element name="null" type="tns:null"/>
      </xs:choice>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="field">
    <xs:choice>
      <xs:element name="object" type="tns:object"/>
      <xs:element name="array" type="tns:array"/>
      <xs:element name="string" type="tns:string"/>
      <xs:element name="number" type="tns:number"/>
      <xs:element name="boolean" type="tns:boolean"/>
      <xs:element name="null" type="tns:null"/>
    </xs:choice>
    <xs:attribute name="name" type="xs:string"/>
  </xs:complexType>
  <xs:element name="json">
    <xs:complexType>
      <xs:choice>
        <xs:element name="array" type="tns:array"/>
        <xs:element name="object" type="tns:object"/>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>


If we note here we see JSON can be represent by a JSON element where its child be a choice of an array or an object. An Object contains a field which can have a choice of its child being object, array, string, number, boolean or null and same happens with the array.

Thanks to Vladimir at https://bitbucket.org/vladimirdyuzhev for his concept.

XSL Transformation To Split XML Records in [OSB]

Recently I had a scenario where I was selecting more than 10000 records from a Database Table say X. And I had to transform it to the request of a particular SMS Gateway API Operation. The SMS gateway unfortunately can restraint 1000 records in an instance at 10 parallel threads. So my first activity was to Split 10000 records into 1000 records. Since it could have been achievable if I were using DATABASE ADAPTER polling feature where I could define MaxRaiseSize (number of rows per xml document ), I was using the select operation hence I couldn't leverage the MaxRaiseSize property. Anyways, all in all, I had to split my xml records into chunks of 1000 and here I chose XSLT since I researched it to be the fastest in compatible to Oracle Service Bus.

Here is the PROBLEM.

Input:

<?xml version="1.0" encoding="UTF-8" ?>
<ActionTableCollection xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData file:/C:/JDeveloper/mywork/Application1/Scheduler/xsd/QueryPendingSMSData_table.xsd" xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData">
   <ActionTable>
      <recordid>10</recordid>
      <msgid>1</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>13</recordid>
      <msgid>2</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>16</recordid>
      <msgid>3</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>8</recordid>
      <msgid>4</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>9</recordid>
      <msgid>5</msgid>
   </ActionTable>
.
.
.
.
.
   <ActionTable>
      <recordid>19</recordid>
      <msgid>9999</msgid>
   </ActionTable>
   <ActionTable>
      <recordid>36</recordid>
      <msgid>10000</msgid>
   </ActionTable>
</ActionTableCollection>


Expected Output

<tns:getSmsStatusList xmlns:tns="http://webservice.sendSMS.***.com/">
 <SmsStatusRequest>
  <messageId>1</messageId>
  <messageId>2</messageId>
  <messageId>3</messageId>
.
.
.
<messageId>1000</messageId>
 </SmsStatusRequest>

 <SmsStatusRequest>
  <messageId>1001</messageId>
  <messageId>1002</messageId>
  <messageId>1003</messageId>
.
.
.
<messageId>2000</messageId>
 </SmsStatusRequest>
.
.
.
 <SmsStatusRequest>
.
.
.
  <messageId>10000</messageId>
 </SmsStatusRequest>

</tns:getSmsStatusList>




So
Here is the XSLT I used.








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

<xsl:stylesheet version="1.0"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                xmlns:bpws="http://schemas.xmlsoap.org/ws/2003/03/business-process/"
                xmlns:aia="http://www.oracle.com/XSL/Transform/java/oracle.apps.aia.core.xpath.AIAFunctions"
                xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"
                xmlns:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"
                xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"
                xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:med="http://schemas.oracle.com/mediator/xpath"
                xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"
                xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"
                xmlns:xdk="http://schemas.oracle.com/bpel/extension/xpath/function/xdk"
                xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions"
                xmlns:tns="http://webservice.sendSMS.***.com/"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:ora="http://schemas.oracle.com/xpath/extension"
                xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"
                xmlns:ns0="http://xmlns.oracle.com/pcbpel/adapter/db/top/QueryPendingSMSData"
                xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"
                exclude-result-prefixes="xsi xsl xsd ns0 tns xp20 bpws aia mhdr bpel oraext dvm hwf med ids bpm xdk xref ora socket ldap">

 <xsl:param name="pGroupSize" select="3"/>
 <xsl:template match="node()|@*">
  <xsl:copy>
   <xsl:apply-templates select="node()|@*"/>
  </xsl:copy>
 </xsl:template>
 <xsl:template match="/*">
  <tns:getSmsStatusList>
   <xsl:apply-templates select="ns0:ActionTable[position() mod $pGroupSize = 1]"/>
  </tns:getSmsStatusList>
 </xsl:template>
 <xsl:template match="ns0:ActionTable">
  <SmsStatusRequest>
   <xsl:for-each select=".|following-sibling::*[not(position() > $pGroupSize -1)]">
    <messageId>
     <xsl:value-of select="ns0:msgid"/>
    </messageId>
   </xsl:for-each>
  </SmsStatusRequest>
 </xsl:template>
</xsl:stylesheet>


The technique here is not any grouping technique and not the use for for each group by which I found in most of the blogs in the internet. The technique here is the use of mod operator with position() and the following-sibling:: utilities. Basic idea is to get the xml records N1, N2, N3.. whose positions are mod of (number of records to which we want to split let us say  pGroupSize) and then to get all the XML records which exists before Ni which are its siblings.

I hope you would like this technique though the splitting can be achieved by for-each-group by position, unfortunately for-each-group was not supported in OSB.