2 Replies Latest reply: Sep 22, 2013 10:56 PM by MWshobz RSS

    DBAdapter retains sequence value causing unique constrain error

    MWshobz

      Hi BPEL Gurus,

      I am facing an issue in EBS to OTM Invoice integration.
      AP Invoice header is inserted, AP Invoice lines does not get inserted due to the below error in BPEL. Payment import fails, as there are no invoice lines.


      Non Recoverable System Fault :
      Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'insert' failed due to: DBWriteInteractionSpec Execute Failed Exception. insert failed. Descriptor name: [APInvoice.ApInvoicesInterface]. Caused by java.sql.BatchUpdateException: ORA-00001: unique constraint (AP.AP_INVOICE_LINES_INTERFACE_U1) violated . Please see the logs for the full DBAdapter logging output prior to this exception. This exception is considered not retriable, likely due to a modelling mistake. To classify it as retriable instead add property nonRetriableErrorCodes with value "-1" to your deployment descriptor (i.e. weblogic-ra.xml). To auto retry a retriable fault set these composite.xml properties for this invoke: jca.retry.interval, jca.retry.count, and jca.retry.backoff. All properties are integers. ". The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.

          
      SOA DB Connections seem to retain the used sequence in the cache and assign the same when Invoice line id is inserted. This is causing the Unique constraint error while inserting to AP Invoice lines table in EBS.

      I am calling the standard APInvoices.wsdl for doing the AP_INVOICE_LINES_INTERFACE insert. I notice that in the APInvoice-or-mappings.xml file contain the following code
         <sequence-name>AP_INVOICE_LINES_INTERFACE_S</sequence-name>
          <sequence-field table="AP_INVOICE_LINES_INTERFACE" name="INVOICE_LINE_ID"/>
      </sequencing>
      <caching>
          <cache-type>weak-reference</cache-type>
          <cache-size>-1</cache-size>
          <always-refresh>true</always-refresh>
      </caching>
      <remote-caching>
          <cache-type>weak-reference</cache-type>
          <cache-size>-1</cache-size>
      </remote-caching>

      Is their anything that I can change, something in the BPEL process to refresh the Sequence cache all the time.

      Alternative I am thinking now is the below.
      1. Check with client if they can make the sequence AP_INVOICE_LINES_INTERFACE_S as NOCACHE.
      2. Derive the invoice line id via pl/sql adapter and populate the invoice line id. But in this case I am not sure if the APInvoice webservice code will try to update with BPEL generated sequence.

      Any direction on this issue will be of great help.

      Thanks,
      Shobz

        • 1. Re: DBAdapter retains sequence value causing unique constrain error
          Anshul

          Hi MWshobz,

           

          try to pass the sequence value in BPEL using  "oraext:sequence-next-val("Sequence name as a string", "Datasource as a string") function.

           

          Regards,

          Anshul

          • 2. Re: DBAdapter retains sequence value causing unique constrain error
            MWshobz

            Hi Anushal,
            Thanks for the response.

             

            Hi All,
            The issue has been resolved with the setting of this DBAdpater "sequencePreallocationSize"
            Since this property was set to 50 by default for the  DB adapter, it did not match with the INCREMENT BY of the AP_INVOICE_LINES_INTERFACE_S sequence which was set to 1.

            What happens in the DB Adapter is, when the first record is created, it gets the nextval from the sequence(let's assume it's 20), however it then uses it's own internal sequence for the next 50 records by referring to the value set in this property(thereby creating records with primary keys from 20-70) and only when this internal sequence is exhausted does it go back to the database sequence. 
            Meanwhile any other AP inovoice created would retrive from DB which could be a value say 21, conflicting with the one inserted by BPEL, throwing a unique constraint exception.

            Setting this property to 1 or matching it to the INCREMENT BY of sequence solves the problem.

             

            Thanks,
            Shobz