Forum Stats

  • 3,770,355 Users
  • 2,253,099 Discussions
  • 7,875,414 Comments

Discussions

Oracle 11g SOA Composite - Write File Adapter - Excel File Output Issue

2734595
2734595 Member Posts: 11

The SOA Composite job having issue uses 'DB Read Adapter' (Select query) and gets columns/ data from DB table as Input.  Next the input from 'Read Adapter' is fed into BPEL process that contains Assign (row count) and then goes through Transform to map columns.  From there the payload is routed to a 'Write File Adapter' and generates an Excel spreadsheet.  More on this can be found at - http://orasoatech.blogspot.com/2012/12/generating.html

When the job is deployed and tested, all columns are mapped to the Excel spreadsheet correctly, however there are random rows where data is off-set.  The screenshot below is a sample file generated when tested in lower life-cycle.  The issue seems to be random, as far as rows it targets, but it consistently starts at the "FILE_CRT_DT" column.  As seen below that columns data is missing and that causes all data to shift to the right 1 column.

Note: The sample report below is generated in Non-Prod when tested and I do not believe the issue has to do with the last row/ record as seen in screenshot below.  I say this because our Prod job produces a report that does the same thing but not limited to the the last record - it occurs randomly throughout a much larger Excel file/ report.

Any thoughts on what could be causing issue?

UmRmViewReport_nonProdGeneratedFile.jpg

Screenshot of composite.xml:

pastedImage_3.png


Screenshot of bpel file:

pastedImage_4.png

Best Answer

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited May 22, 2018 2:01AM Accepted Answer

    Hi,

    Is the output a CSV file?

    Could it be that at the particular row the pvndr_nm column contains a comma, while it is not enclosed in quotes?

    Taking another look at you first screendump, I gues the problem could be in that third column, that is in your transformation the target element "PROTECTEDTEXTATTRIBUTE9".

    Could you show the faulty row in your target file with the row above and below?

    Regards,
    Martien

    2734595

Answers

  • 2734595
    2734595 Member Posts: 11
    edited May 14, 2018 3:49PM

    If there is a better place than "BPEL" to post this type of question please let me know.  Thanks.

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited May 16, 2018 9:08AM

    How does your transformation look like. Is the column/element filled conditionally?

    Regards,
    Martien

  • 2734595
    2734595 Member Posts: 11
    edited May 16, 2018 12:52PM

    Hey Martien ... all data types are being converted to a String, including the "FILE_CRT_DT" (protecteddateattribute2) column where this issue seems to start.  This is being done in the Transform via String Conversion Function.  All columns that are 'INT' and 'DATE' data types are being converted to a 'STRING' in the Transform.

    Does that answer your question?  If so, please let me know if you see a problem with the approach taken.

    Screenshot of Transform Design:

    pastedImage_0.png

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited May 17, 2018 1:45AM

    Can you show the part in the transform that fills the elements shown in the file? I don't see how the shown elements relate to the columns in the file. Is it a CSV file?

    Make sure that the element is created even if the source element is empty. What I meant was if there was an xsl:if or xsl:choose construct around that element.


    Regards,
    Martien

  • 2734595
    2734595 Member Posts: 11
    edited May 18, 2018 6:08PM

    Thanks for trying to help Martien.

    Yes the file Write_Adapter generates .CSV file.  Column names are updated manually after xsd creation according to this doc-  Oracle SOA, BPEL,BPM: Generating .csv file using FileAdapter in Oracle SOA 11g

    Elements renamed in CSV Header file:

    pastedImage_1.png

    Below is 'xsl' section of Transform source code that correlates to screenshot above:

    <xsl:template match="/">

        <imp1:Root-Element>

          <xsl:for-each select="/top:WftaskCollection/top:Wftask">

            <imp1:single-record>

              <imp1:PROTECTEDNUMBERATTRIBUTE3>

                <xsl:value-of select="string(top:protectednumberattribute3)"/>

              </imp1:PROTECTEDNUMBERATTRIBUTE3>

              <imp1:PROTECTEDTEXTATTRIBUTE14>

                <xsl:value-of select="top:protectedtextattribute14"/>

              </imp1:PROTECTEDTEXTATTRIBUTE14>

              <imp1:PROTECTEDTEXTATTRIBUTE9>

                <xsl:value-of select="top:protectedtextattribute9"/>

              </imp1:PROTECTEDTEXTATTRIBUTE9>

              <imp1:PROTECTEDTEXTATTRIBUTE10>

                <xsl:value-of select="top:protectedtextattribute10"/>

              </imp1:PROTECTEDTEXTATTRIBUTE10>

              <imp1:PROTECTEDDATEATTRIBUTE2>

                <xsl:value-of select="string(top:protecteddateattribute2)"/>

              </imp1:PROTECTEDDATEATTRIBUTE2>

              <imp1:PROTECTEDDATEATTRIBUTE1>

                <xsl:value-of select="string(top:protecteddateattribute1)"/>

              </imp1:PROTECTEDDATEATTRIBUTE1>

              <imp1:UPDATEDDATE>

                <xsl:value-of select="string(top:updateddate)"/>

              </imp1:UPDATEDDATE>

              <imp1:PROTECTEDTEXTATTRIBUTE12>

                <xsl:value-of select="top:protectedtextattribute12"/>

              </imp1:PROTECTEDTEXTATTRIBUTE12>

              <imp1:PROTECTEDTEXTATTRIBUTE4>

                <xsl:value-of select="top:protectedtextattribute4"/>

              </imp1:PROTECTEDTEXTATTRIBUTE4>

              <imp1:PROTECTEDNUMBERATTRIBUTE1>

                <xsl:value-of select="string(top:protectednumberattribute1)"/>

              </imp1:PROTECTEDNUMBERATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE3>

                <xsl:value-of select="top:protectedtextattribute3"/>

              </imp1:PROTECTEDTEXTATTRIBUTE3>

              <imp1:PROTECTEDDATEATTRIBUTE4>

                <xsl:value-of select="string(top:protecteddateattribute4)"/>

              </imp1:PROTECTEDDATEATTRIBUTE4>

              <imp1:PROTECTEDTEXTATTRIBUTE7>

                <xsl:value-of select="top:protectedtextattribute7"/>

              </imp1:PROTECTEDTEXTATTRIBUTE7>

              <imp1:PROTECTEDTEXTATTRIBUTE11>

                <xsl:value-of select="top:protectedtextattribute11"/>

              </imp1:PROTECTEDTEXTATTRIBUTE11>

              <imp1:PROTECTEDTEXTATTRIBUTE6>

                <xsl:value-of select="top:protectedtextattribute6"/>

              </imp1:PROTECTEDTEXTATTRIBUTE6>

              <imp1:PROTECTEDTEXTATTRIBUTE5>

                <xsl:value-of select="top:protectedtextattribute5"/>

              </imp1:PROTECTEDTEXTATTRIBUTE5>

              <imp1:PROTECTEDTEXTATTRIBUTE1>

                <xsl:value-of select="top:protectedtextattribute1"/>

              </imp1:PROTECTEDTEXTATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE8>

                <xsl:value-of select="top:protectedtextattribute8"/>

              </imp1:PROTECTEDTEXTATTRIBUTE8>

            </imp1:single-record>

          </xsl:for-each>

        </imp1:Root-Element>

      </xsl:template>

    </xsl:stylesheet>

  • 2734595
    2734595 Member Posts: 11
    edited May 18, 2018 6:17PM

    Transform updated to use Normalized function.  The idea is to remove whitespace or junk characters, which could cause CSV file output issues:

    <xsl:stylesheet version="1.0"

                    xmlns:imp1="http://TargetNamespace.com/Write_UmRmViewReport"

                    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:bpel="http://docs.oasis-open.org/wsbpel/2.0/process/executable"

                    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                    xmlns:bpm="http://xmlns.oracle.com/bpmn20/extensions"

                    xmlns:tns="http://xmlns.oracle.com/pcbpel/adapter/db/UmRmViewReport_master_updated/UmRmViewReport/Read_UmRmViewReport"

                    xmlns:plt="http://schemas.xmlsoap.org/ws/2003/05/partner-link/"

                    xmlns:ns0="http://xmlns.oracle.com/pcbpel/adapter/file/UmRmViewReport_master_updated/UmRmViewReport/Write_UmRmViewReport"

                    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"

                    xmlns:ora="http://schemas.oracle.com/xpath/extension"

                    xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator"

                    xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction"

                    xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"

                    xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue"

                    xmlns:hwf="http://xmlns.oracle.com/bpel/workflow/xpath"

                    xmlns:med="http://schemas.oracle.com/mediator/xpath"

                    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                    xmlns:jca="http://xmlns.oracle.com/pcbpel/wsdl/jca/"

                    xmlns:ids="http://xmlns.oracle.com/bpel/services/IdentityService/xpath"

                    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:xsd="http://www.w3.org/2001/XMLSchema"

                    xmlns:bpmn="http://schemas.oracle.com/bpm/xpath"

                    xmlns:top="http://xmlns.oracle.com/pcbpel/adapter/db/top/Read_UmRmViewReport"

                    xmlns:ldap="http://schemas.oracle.com/xpath/extension/ldap"

                    exclude-result-prefixes="xsi xsl tns plt wsdl xsd top imp1 ns0 jca xp20 bpws bpel bpm ora socket mhdr oraext dvm hwf med ids xdk xref bpmn ldap">

      <xsl:template match="/">

        <imp1:Root-Element>

          <xsl:for-each select="/top:WftaskCollection/top:Wftask">

            <imp1:single-record>

              <imp1:PROTECTEDNUMBERATTRIBUTE3>

                <xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>

              </imp1:PROTECTEDNUMBERATTRIBUTE3>

              <imp1:PROTECTEDTEXTATTRIBUTE14>

                <xsl:value-of select="normalize-space(top:protectedtextattribute14)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE14>

              <imp1:PROTECTEDTEXTATTRIBUTE9>

                <xsl:value-of select="normalize-space(top:protectedtextattribute9)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE9>

              <imp1:PROTECTEDTEXTATTRIBUTE10>

                <xsl:value-of select="normalize-space(top:protectedtextattribute10)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE10>

              <imp1:PROTECTEDDATEATTRIBUTE2>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute2))"/>

              </imp1:PROTECTEDDATEATTRIBUTE2>

              <imp1:PROTECTEDDATEATTRIBUTE1>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute1))"/>

              </imp1:PROTECTEDDATEATTRIBUTE1>

              <imp1:UPDATEDDATE>

                <xsl:value-of select="string(normalize-space(top:updateddate))"/>

              </imp1:UPDATEDDATE>

              <imp1:PROTECTEDTEXTATTRIBUTE12>

                <xsl:value-of select="normalize-space(top:protectedtextattribute12)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE12>

              <imp1:PROTECTEDTEXTATTRIBUTE4>

                <xsl:value-of select="normalize-space(top:protectedtextattribute4)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE4>

              <imp1:PROTECTEDNUMBERATTRIBUTE1>

                <xsl:value-of select="string(normalize-space(top:protectednumberattribute1))"/>

              </imp1:PROTECTEDNUMBERATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE3>

                <xsl:value-of select="normalize-space(top:protectedtextattribute3)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE3>

              <imp1:PROTECTEDDATEATTRIBUTE4>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute4))"/>

              </imp1:PROTECTEDDATEATTRIBUTE4>

              <imp1:PROTECTEDTEXTATTRIBUTE7>

                <xsl:value-of select="normalize-space(top:protectedtextattribute7)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE7>

              <imp1:PROTECTEDTEXTATTRIBUTE11>

                <xsl:value-of select="normalize-space(top:protectedtextattribute11)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE11>

              <imp1:PROTECTEDTEXTATTRIBUTE6>

                <xsl:value-of select="normalize-space(top:protectedtextattribute6)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE6>

              <imp1:PROTECTEDTEXTATTRIBUTE5>

                <xsl:value-of select="normalize-space(top:protectedtextattribute5)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE5>

              <imp1:PROTECTEDTEXTATTRIBUTE1>

                <xsl:value-of select="normalize-space(top:protectedtextattribute1)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE8>

                <xsl:value-of select="normalize-space(top:protectedtextattribute8)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE8>

            </imp1:single-record>

          </xsl:for-each>

        </imp1:Root-Element>

      </xsl:template>

    </xsl:stylesheet>

    =======================================================================================================================================

    Two (2) more attempts to fix Transform logic below.

    Solution #1 using 'IF Test = String-Length' logic on first column:

    <xsl:template match="/">

        <imp1:Root-Element>

          <xsl:for-each select="/top:WftaskCollection/top:Wftask">

            <imp1:single-record>

              <imp1:PROTECTEDNUMBERATTRIBUTE3>

              <xsl:if test = "string-length(normalize-space(top:protectednumberattribute3))>0">

             

                    <xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>

              </xsl:if>

              </imp1:PROTECTEDNUMBERATTRIBUTE3>

              <imp1:PROTECTEDTEXTATTRIBUTE14>

                <xsl:value-of select="normalize-space(top:protectedtextattribute14)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE14>

              <imp1:PROTECTEDTEXTATTRIBUTE9>

                <xsl:value-of select="normalize-space(top:protectedtextattribute9)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE9>

              <imp1:PROTECTEDTEXTATTRIBUTE10>

                <xsl:value-of select="normalize-space(top:protectedtextattribute10)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE10>

              <imp1:PROTECTEDDATEATTRIBUTE2>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute2))"/>

              </imp1:PROTECTEDDATEATTRIBUTE2>

              <imp1:PROTECTEDDATEATTRIBUTE1>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute1))"/>

              </imp1:PROTECTEDDATEATTRIBUTE1>

              <imp1:UPDATEDDATE>

                <xsl:value-of select="string(normalize-space(top:updateddate))"/>

              </imp1:UPDATEDDATE>

              <imp1:PROTECTEDTEXTATTRIBUTE12>

                <xsl:value-of select="normalize-space(top:protectedtextattribute12)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE12>

              <imp1:PROTECTEDTEXTATTRIBUTE4>

                <xsl:value-of select="normalize-space(top:protectedtextattribute4)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE4>

              <imp1:PROTECTEDNUMBERATTRIBUTE1>

                <xsl:value-of select="string(normalize-space(top:protectednumberattribute1))"/>

              </imp1:PROTECTEDNUMBERATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE3>

                <xsl:value-of select="normalize-space(top:protectedtextattribute3)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE3>

              <imp1:PROTECTEDDATEATTRIBUTE4>

                <xsl:value-of select="string(normalize-space(top:protecteddateattribute4))"/>

              </imp1:PROTECTEDDATEATTRIBUTE4>

              <imp1:PROTECTEDTEXTATTRIBUTE7>

                <xsl:value-of select="normalize-space(top:protectedtextattribute7)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE7>

              <imp1:PROTECTEDTEXTATTRIBUTE11>

                <xsl:value-of select="normalize-space(top:protectedtextattribute11)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE11>

              <imp1:PROTECTEDTEXTATTRIBUTE6>

                <xsl:value-of select="normalize-space(top:protectedtextattribute6)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE6>

              <imp1:PROTECTEDTEXTATTRIBUTE5>

                <xsl:value-of select="normalize-space(top:protectedtextattribute5)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE5>

              <imp1:PROTECTEDTEXTATTRIBUTE1>

                <xsl:value-of select="normalize-space(top:protectedtextattribute1)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE1>

              <imp1:PROTECTEDTEXTATTRIBUTE8>

                <xsl:value-of select="normalize-space(top:protectedtextattribute8)"/>

              </imp1:PROTECTEDTEXTATTRIBUTE8>

            </imp1:single-record>

          </xsl:for-each>

        </imp1:Root-Element>

      </xsl:template>

    </xsl:stylesheet>

    Note: This does deploy to E.M. and when tested results in same issue as originally reported.  It's also generating error when trying to view Transform Design.

    pastedImage_8.png

    =====================================================================================================================================

    Solution #2 - wraps each/all columns in logic below.  This is also deploying to E.M. but instance running in error & generating different error when trying to view Transform Design.

    TRANSFORM LOGIC:

    <xsl:choose>

           <xsl:when test="string-length(normalize-space(top:protectednumberattribute3))>0">

                 <xsl:value-of select="string(normalize-space(top:protectednumberattribute3))"/>

           </xsl:when>

           <xsl:otherwise>

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

           </xsl:otherwise>

      </xsl:choose>

    E.M. INSTANCE ERROR:

    Non Recoverable System Fault :

    <bpelFault><faultType>0</faultType><bindingFault xmlns="http://schemas.oracle.com/bpel/extension"><part name="summary"><summary>Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'Write' failed due to: Translation Error. Translation Error. Error while translating message to native format. Please make sure that the payload for the outbound interaction conforms to the schema. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution. </summary></part><part name="detail"><detail>Invalid text '29WXYZHH000026756LG ELECTRO2018-04-19T00:00:00.000-04:002018-04-26T00:00:00.000-04:002018-05-08T11:29:05.000-04:00UmRmD029UM25.52IN086339432018-03-27T00:00:00.000-04:0000896424415089DCRMCUST' in element: 'single-record'</detail></part><part name="code"><code>null</code></part></bindingFault></bpelFault>

    VIEW TRANSFORM DESIGN ERROR:

    pastedImage_25.png

    ** In regards to error above, there is one (1) <when> Element.  Why does mapper file indicate node already mapped?

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited May 22, 2018 2:01AM Accepted Answer

    Hi,

    Is the output a CSV file?

    Could it be that at the particular row the pvndr_nm column contains a comma, while it is not enclosed in quotes?

    Taking another look at you first screendump, I gues the problem could be in that third column, that is in your transformation the target element "PROTECTEDTEXTATTRIBUTE9".

    Could you show the faulty row in your target file with the row above and below?

    Regards,
    Martien

    2734595
  • 2734595
    2734595 Member Posts: 11
    edited May 23, 2018 10:13AM

    Not sure how you knew there was a comma messing things up in PVNDR_NM column but that was the issue.  Very impressed and appreciative.  Thank you!

    I tried to handle this with Replace logic in Transform source code but that didn't work.  Translate did though.

    <imp1:PROTECTEDTEXTATTRIBUTE10>

          <xsl:value-of select="translate(normalize-space(top:protectedtextattribute10),',','')"/>

    </imp1:PROTECTEDTEXTATTRIBUTE10>

  • Martien van den Akker
    Martien van den Akker Member Posts: 2,776 Bronze Crown
    edited May 24, 2018 2:35AM

    You're welcome.

    But it's in figuring how csv works (although you did not mention that it was a csv).

    If the columns aren't enclosed in quotes, then a comma in the text is interpretted as a seperation comma. And that will add a cell in that row.

    Glad it's sorted out.


    Regards,

    Martien

This discussion has been closed.