Integration

Get Involved. Join the Conversation.

Topic

    Abhinav Prasoon
    Multiple rows are not updating in database using ICS adapter
    Topic posted June 2, 2019 by Abhinav Prasoon, tagged Adapters, Cloud, Database, Integration, Mapping, PaaS 
    49 Views, 2 Comments
    Title:
    Multiple rows are not updating in database using ICS adapter
    Summary:
    Multiple rows are not updating in database using ICS adapter
    Content:

    Issue while updating multiple rows in the database:

    #1: I have selected 'Update' operation in the database adapter.

       Result: The interface is getting pass but failed to update the database, though the generated request payload is correct.

       Database adapter response:

       <WorkerResponse xmlns="http://xmlns.oracle.com/ics/scheduler/worker">
       <numBatchesProcessed />
       <numItemsSuccess />
       <numItemsError />
       <isTerminated>false</isTerminated>
       <isFailed>false</isFailed>
       <failedResult>
          <payload />
          <errorMessage />
          <errorEndpoint />
          <errorLocation />
       </failedResult>
       <outputDirs />
       <EssRequestExecutionContext />

     

    #2: I have selected 'Insert or Update (Merge)' operation in database adapter

       Result: The interface is getting failed while invoking the database adapter, though the generated request payload is correct.

      error:

    <reason>oracle.tip.adapter.sa.api.JCABindingException: DBWriteInteractionSpec Execute Failed Exception. merge failed. Descriptor name: [UpdateDB.TwWmsInventoryItemMaster]. Caused by java.sql.SQLSyntaxErrorException: ORA-00936: missing expression . 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 "-936" 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. ORA-00936: missing expression Fault Details : &lt;err:serviceInvocationError xmlns:err="http://xmlns.oracle.com/cloud/generic/service/fault"&gt; &lt;err:type&gt;DBWriteInteractionSpec Execute Failed Exception&lt;/err:type&gt; &lt;err:title&gt;merge failed. Descriptor name: [UpdateDB.TwWmsInventoryItemMaster].&lt;/err:title&gt; &lt;err:detail&gt;Caused by java.sql.SQLSyntaxErrorException: ORA-00936: missing expression .&lt;/err:detail&gt; &lt;err:errorCode&gt;serviceInvocationError&lt;/err:errorCode&gt; &lt;err:remedialAction&gt;Please see the logs for the full DBAdapter logging output prior to this exception.&lt;/err:remedialAction&gt; &lt;/err:serviceInvocationError&gt; :</reason>
          <operation>merge</operation>

     

    #3: Even I tried using 'Run SQL query' option in the database adapter

         Result: The interface is getting passed but updating only the first row. It's unable to update the second row onwards. In this case, the generated request payload contains the data for the first row only even though I have used for-each statement/loop.

     

    Version:
    19.1.3.0.0

    Comment

     

    • Hemanth Lakkaraju

      Very little information to analyse.

      #1 - The provided xml is not Database response. That's a scheduler response which tells me you are using a scheduled integration here and the update operation went through fine. It depends on your update query to determine if the request is functionally correct but this response says technically it is correct.

      #2 - The query considered for this operation seems incorrect. Need to see the configuration of adapter to figure this out.

      #3 - This again depends on the query and the loop used which can be determined by looking at the flow.

    • Moon Ray Lo

      As far as I have tried the current DB Adapter - it is unable to insert/update bulk data in one single request or single DB call (execute for-each on mapper level). You need to utilize the For-Each Loop activity in orchestration level - but this will result to 1:1 activity ratio, like 1 insert/update activity means 1 DB call. This needs to be monitored especially on a heavy/bulk load of data.