Xpath to retrieve Group Attribute values from Oracle B2B message

 Some time back I had written a blog on How to Configure XPath Expression to retrieve more information from the Business Message in B2B Console .

It was on basic xpaths for retrieving the invoice id and other information based on segments.

//*[local-name()=' Element-143']/text()  -  This works for fetching data from the ST segment (Transaction Type– highlighted below in green).

B2B uses the information from edi file and agreement information to populate Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01).

But for fetching the Group Information – Group Control Number (GS06) , Group Functional Identifier (GS01) XPath expression is bit complex and I am listing them below.

So for example to list GroupSenderId value::

//*[local-name()='Property' and 'GroupSenderID'=@*[local-name()='Name']]/text()

So for example to list GroupReceiverID value ::

//*[local-name()='Property' and 'GroupReceiverID'=@*[local-name()='Name']]/text()

 

B2B message snippet

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

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

<Transaction-810  XDataVersion="1.0"  Standard="X12"  Version="5010"  GUID="{38353832-3932-3330-3134-323936313736}">

<ns1:Internal-Properties>

<ns1:Data-Structure  Name="Interchange">

<ns1:Lookup  Name="InterchangeSenderID">THO123</ns1:Lookup>

<ns1:Property  Name="InterchangeSenderID">THO123</ns1:Property>

<ns1:Lookup  Name="InterchangeReceiverID">GEO456</ns1:Lookup>

<ns1:Property  Name="InterchangeReceiverID">GEO456</ns1:Property>

<ns1:Data-Structure  Name="Group">

<ns1:Lookup  Name="GroupSenderID">THO123</ns1:Lookup>

<ns1:Property  Name="GroupSenderID">THO123</ns1:Property>

<ns1:Lookup  Name="GroupReceiverID">GEO456</ns1:Lookup>

<ns1:Property  Name="GroupReceiverID">GEO456</ns1:Property>

<ns1:Data-Structure  Name="Transaction"/>

</ns1:Data-Structure>

</ns1:Data-Structure>

</ns1:Internal-Properties>

<ns1:Segment-ST>

<ns1:Element-143>810</ns1:Element-143>

<ns1:Element-329>#ControlNumber#</ns1:Element-329>

</ns1:Segment-ST>

<ns1:Segment-BIG>

<ns1:Element-373_1>20120910</ns1:Element-373_1>

</ns1:Segment-BIG>

……………………………….

<ns1:Segment-TDS>

<ns1:Element-610_1>391262</ns1:Element-610_1>

</ns1:Segment-TDS>

……………………

<ns1:Segment-SE>

<ns1:Element-96>#SegmentCount#</ns1:Element-96>

<ns1:Element-329>#ControlNumber#</ns1:Element-329>

</ns1:Segment-SE>

</Transaction-810>

Thanks To Girish for the help.

Issue with DBAdapter Update due to char datatype & shouldTrimStrings property

Recently I looked into a technical issue with DB adapter where the update was not working even though it was not throwing any error. Everything seems to be working fine but the field is never updated.

All the settings looked fine but the update was not happening. So we checked the table which revealed that the all the string fields were of char type. It didn’t seem to value much as my understanding was that char will work similar to varchar. I was puzzled to see the Note from Oracle @Oracle DBAdapter documentation ,but it was true.

It reads as :: Oracle recommends that you use varchar instead of char for primary key columns, otherwise you must set the weblogic-ra.xml property shouldTrimStrings to false. The truncation of trailing spaces could cause the primary key to be read incorrectly, making it impossible to update read rows as processed.

Issue :: The Primary Key fields in Database were of char type and data contains trailing spaces. Since by default shouldTrimStrings property is set to true, weblogic will trim these spaces, there by it will not be able to fetch the correct rows from DB. So update fails as it will not be able to fetch the row properly.

Solution::

Set the shouldTrimStrings property to false in weblogic DBAdapter Outbound Connection Properties

By default the property shouldTrimStrings is not visible in Outbound Connection Properties, so we will not able to change it.

So to modify the value a new entry needs to added to the DBAdapter config  properties. This is one time configuration task that needs to done by updating the ra.xml from filesystem.

Steps

1.    Take a backup of DBAdapter.rar

2.     Extract the entire content.(use unzip tools)

3.     Edit the META-INF/ra.xml.

Add a property in ra.xml file

           <config-property>

               <config-property-name>shouldTrimStrings</config-property-name>

               <config-property-type>java.lang.Boolean</config-property-type>

               <config-property-value>false</config-property-value>

            </config-property>

   

4.    Re-Create the DbAdapter  rar file.

>>>>jar.exe -cvf DbAdapter.rar -C DbAdapter/ .

added manifest

adding: DBAdapter.jar(in = 551405) (out= 519781)(deflated 5%)

ignoring entry META-INF/

ignoring entry META-INF/MANIFEST.MF

adding: META-INF/ra.xml(in = 13473) (out= 2866)(deflated 78%)

adding: META-INF/weblogic-ra.xml(in = 6713) (out= 1775)(deflated 73%)

adding: toplink-grid.jar(in = 102529) (out= 91349)(deflated 10%)

5.    Update the DBAdapter from Deployments

From Console the property should be available for all the database adapter connection under Outbound Connection Properties.

Thanks to Thani, Lee, Sampath for helping with this issue.

Java utility to create/populate a DVM from XL sheet -SOA 11g

Today I am posting the java code which reads in a input XL with the dvm data and creates a dvm file. I had created a utility in 10g , now just modified it for 11g.This is very useful in scenarios where there is huge amount of data to be inserted into DVM before the interface can start working. Even though we have soa composer and JDev to enter the recods into DVM. manual entry of large number of DVM data is not a feasible solution For running the java program pass the 2 arguments
for eg: -inputXL=D:\Projects\DVMTest\Sites.xls
-outputDVM=D:\Projects\DVMTest\SiteTest.dvm
This will work for any number of columns.The first row in the XL sheet represents the column name that needs to appear in the DVM. If decimal points are not required modify the code to replace the decimal value. Hope this helps.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * This class has methods which will read the excel file and convert it into a
 * a DVM file.
 *
 *@author George Thomas
 *@version 1.0
 *
 */
public class DVMCreator {
    private final static Logger logger =
        Logger.getLogger(DVMCreator.class.getName());

    public DVMCreator() {
        super();
    }


    private String inputFile;

    /**
     * @param inputFile
     */
    public void setInputFile(String inputFile) {
        this.inputFile = inputFile;
    }


    /**
     * This method listens for incoming records and handles them as required.
     *
     * @return sheetData
     * @throws Exception
     */
    public List getSheetData() throws Exception {
        //
        // Create an ArrayList to store the data read from excel sheet.
        //
        List sheetData = new ArrayList();

        FileInputStream fis = null;
        try {
            //
            // Create a FileInputStream that will be use to read the
            // excel file.
            //
            fis = new FileInputStream(inputFile);

            //
            // Create an excel workbook from the file system.
            //
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            //
            // Get the first sheet on the workbook.
            //
            HSSFSheet sheet = workbook.getSheetAt(0);

            //
            // When we have a sheet object in hand we can iterator on
            // each sheet's rows and on each row's cells. We store the
            // data read on an ArrayList so that we can printed the
            // content of the excel to the console.
            //
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow)rows.next();
                Iterator cells = row.cellIterator();
                List data = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell)cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
            showExcelData(sheetData);
            // processExelData(sheetData);
            return sheetData;
        }
    }


     private static String convertExcelToDVMData(List sheetData,String dvmName ) {
        //
        // Iterates the data and print it out to the console.
        //
        StringBuffer xmlData = new StringBuffer();
      
      dvmName = dvmName.replaceAll(".dvm", "");

        
        Map map = new HashMap();
        xmlData.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
        xmlData.append("<dvm name=\"");
        xmlData.append(dvmName);
        xmlData.append("\"");
        xmlData.append(" xmlns=\"http://xmlns.oracle.com/dvm\">");
        
        xmlData.append("<description></description>");
        xmlData.append("<columns>");


        List columnNamelist = (List)sheetData.get(0);
        for (int j = 0; j < columnNamelist.size(); j++) {
            xmlData.append("<column name=\"");
            HSSFCell cell = (HSSFCell)columnNamelist.get(j);
            if (cell.getCellType() == 0) {
                logger.log(Level.INFO, " " + cell.getNumericCellValue());
                xmlData.append(cell.getNumericCellValue());

            } else if (cell.getCellType() == 1) {
                logger.log(Level.INFO,
                           "S " + cell.getRichStringCellValue().getString());
                xmlData.append(cell.getRichStringCellValue().getString());
            }
            xmlData.append("\"/>");

        }
        logger.log(Level.INFO, "");
        xmlData.append("</columns>");


        xmlData.append("<rows>");

        for (int i = 1; i < sheetData.size(); i++) {
            xmlData.append("<row>");
            List list = (List)sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {
                xmlData.append("<cell>");
                HSSFCell cell = (HSSFCell)list.get(j);
                if (cell.getCellType() == 0) {
                    logger.log(Level.INFO, " " + cell.getNumericCellValue());
                    xmlData.append(cell.getNumericCellValue());

                } else if (cell.getCellType() == 1) {
                    logger.log(Level.INFO,
                               "S " + cell.getRichStringCellValue().getString());
                    xmlData.append(cell.getRichStringCellValue().getString());
                }
                xmlData.append("</cell>");
                if (j < list.size() - 1) {
                    logger.log(Level.INFO, ", ");
                }
            }
            logger.log(Level.INFO, "");
            xmlData.append("</row>");
        }
        xmlData.append("</rows>");
        xmlData.append("</dvm>");

        return xmlData.toString();
    }

  



    

  public static boolean writeDVM(String xmlData, File file) throws Exception {

       
      try {
          FileOutputStream fOut = new FileOutputStream(file);
          fOut.write(xmlData.getBytes());
          fOut.flush();
          fOut.close();
          logger.log(Level.INFO, "File Created .::" + file.getName());

      } catch (Exception e) {
          logger.log(Level.INFO,
                     "!!BOOOM!! xlCreate() failed : " + e.getMessage());
          throw e;
      }
      return true;
  }
  
  
    /**
     * main method
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException {
        DVMCreator reader = new DVMCreator ();
        String spliter[] = null;
        String inputXL = null;
        String outputDVM = null;
        if (args.length == 0 || args.length == 1) {
            logger.log(Level.INFO, " USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else if (args[0].equals("-help")) {
            logger.log(Level.INFO, "USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else {
            if (args[0].contains("-inputXL") &&
                args[1].contains("-outputDVM")) {
                spliter = args[0].split("=");
                inputXL = spliter[1];
                reader.setInputFile(inputXL);
                spliter = args[1].split("=");
                outputDVM = spliter[1];
                File file = null;
              try {
                   file = new File(outputDVM);
                  logger.log(Level.INFO, "File Created .::" + outputDVM);

              } catch (Exception e) {
                  logger.log(Level.INFO,
                             "!!BOOOM!! xlCreate() failed : " + e.getMessage());
                
              }

                try {
                    List data = reader.getSheetData();
                    
                    String xmlData = convertExcelToDVMData(data,file.getName());
                    boolean flag = writeDVM(xmlData, file);
                    logger.log(Level.INFO, "XML data ::" + xmlData);


                } catch (Exception e) {
                    logger.log(Level.INFO, "Error occured" + e.getMessage());
                    e.printStackTrace();
                }
                logger.log(Level.INFO, "DVM  write done.");

            }


        }
    }
}

How to avoid Selection Failure in Assign activity

One of the queries I got recently was on how to handle selection failures in assign activity if the tags are not present as part of the payload.

To show case how it works I will just do a small sample program which assigns input variable to output variable. I am using PS5(11.1.1.6) environment and BPEL2.0 as specification.

I am using BPEL 2.0 spec, but the solution works in BPEL 1.1 also.

Example:-  I use a simple assign activity to assign 2 elements of input to output.

<assign name="Assign1" xml:id="id_11">             <copy xml:id="id_16">                 <from xml:id="id_17">$inputVariable.payload/client:input1</from>                 <to xml:id="id_18">$outputVariable.payload/client:result1</to>             </copy>             <copy xml:id="id_19" ignoreMissingFromData="no">                 <from xml:id="id_20">$inputVariable.payload/client:input2</from>                 <to xml:id="id_21">$outputVariable.payload/client:result2</to>             </copy>         </assign>

For testing this I pass only one element in payload. The element2 tag is not present in the payload.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bpel="http://xmlns.oracle.com/BPMLearning/Project1/BPELProcess1">    <soapenv:Header/>    <soapenv:Body>       <bpel:process>          <bpel:input1>hello</bpel:input1>         </bpel:process>    </soapenv:Body> </soapenv:Envelope>

In runtime it throws following selection failure error

Exception stack trace:

Exception is thrown because the from-spec at line 61 is evaluated to be empty -<details> -<from-spec> <from xml:id="id_20" xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"> $inputVariable.payload/client:input2 </from> </from-spec> -<variable> <message> inputVariable </message> </variable> -<fault> -<bpelFault> <faultType> 0 </faultType> <selectionFailure xmlns="http://docs.oasis-open.org/wsbpel/2.0/process/executable"/> </bpelFault> </fault> </details>

Solution

 To fix this there is an attribute ignoreMissingFromData

The ignoreMissingFromData attribute suppresses any bpel:selectionFailure standard faults.

BPEL 1.1  syntax	BPEL 2.0 syntax
<copy bpelx:ignoreMissingFromData="yes|no"/>	<copy ignoreMissingFromData="yes|no"/>

 

<assign name="Assign1" xml:id="id_11">             <copy xml:id="id_16">                 <from xml:id="id_17">$inputVariable.payload/client:input1</from>                 <to xml:id="id_18">$outputVariable.payload/client:result1</to>             </copy>             <copy xml:id="id_19" ignoreMissingFromData="yes">                 <from xml:id="id_20">$inputVariable.payload/client:input2</from>                 <to xml:id="id_21">$outputVariable.payload/client:result2</to>             </copy>         </assign>

The error gets suppressed since it ignores the element as the ignoreMissingFromData flag is set to yes.

BPEL instance execution stacktrace

Copy rule at line 64 is ignored because the evaluated from value is empty <payload>

<details> <from-spec> <from  xml:id="id_23">$inputVariable.payload/client:input2</from> </from-spec> <variable> <message>inputVariable</message> </variable> </details> That’s it .It’s an easy solution to selectionFailure instead of  using the count nodes function and taking headaches in  doing the mapping. References: SOA developers guide