Integration

Get Involved. Join the Conversation.

Topic

    Karthik Ramachandran
    Connecting Oracle Integration to Oracle ATP - Issue with...Answered
    Topic posted July 16, 2019 by Karthik RamachandranRed Ribbon: 250+ Points, tagged Database, Integration, PaaS, REST 
    55 Views, 9 Comments
    Title:
    Connecting Oracle Integration to Oracle ATP - Issue with Sequencing
    Summary:
    I am not able to understand from where the sequence is picked up.
    Content:

    In Oracle ATP DB : Created new sequence

    CREATE SEQUENCE ZEB_TEMP START WITH 1000000 INCREMENT BY 1

    so as per sequence when I call sequence number next val it give me next digit. 

    When we mentioned the same sequence in Oracle ATP connection step in integration, it is generating some different sequence number. Only one good thing is, it is not random. But not sure from where sequence is getting generated. It works fine from ATP database, only issue is when i call it from Oracle Integration Cloud. 

    Sequence number what it is generating is 1200214, 1200215, 1200216 etc

    Screenshot attached for the step in OIC where sequence is selected.

    Please let me know if you need more details.

    Image:

    Best Comment

    Sushma Yeleshwarapu

    The adapter maintains a cache of 50 IDs in a pool. Please let us know if you see any duplicate ID. Else there should be no problem.

    Comment

     

    • Sushma Yeleshwarapu

      The adapter generates sequence numbers in a batch of 50. It is suggested to configure sequences in increments of 50.

      Eg: CREATE SEQUENCE ZEB_TEMP START WITH 1000000 INCREMENT BY 50

      Please refer to the documentation below:

      https://docs.oracle.com/en/cloud/paas/integration-cloud/database-adapter/table-operation-properties.html#GUID-8FC4325B-1154-4A59-8D00-75A11DB9805B

    • Karthik Ramachandran

      Thanks Sushma for quick reply. 

      Now I changed the increment to 50, but i am facing another wierd issue. 

      I am getting response in sequence  but after every alternate trigger. I have mentioned response for each trigger.

      trigger 1

      {
      "seqNum": "200009"
      }

      trigger 2

      {
      "seqNum": "199959"
      }
       
      trigger 3
       
      {
      "seqNum": "200010"
      }
       
      trigger 4
      {
      "seqNum": "199960"
      }
       
      for every alternate trigger i am getting different sequence.

       

    • Karthik Ramachandran

      Sushma I understood, it is due to pool. But is it possible to get it fixed so that we get it in sequence. 

      Also is it possible can we send back ROWID itself to OIC after insert operation? Because ROWID is internal column in ATP, column is not visible in OIC.

      • Hemanth Lakkaraju

        I don't see an issue here to get it fixed. The purpose of pooling is good from performance standpoint. Also It is not possible to send back ROWID currently.

    • Karthik Ramachandran

      Thanks Hemanth. Understood. Only concern was sequence number will be visible in UI and business will see a gap of 50 between each sequence number.

      If you have any suggestion different approach for generating sequence number it will help.

      • Hemanth Lakkaraju

        Would it be possible to show the sequence number in UI in sorted way?? :)

        • Karthik Ramachandran

          nope.. latest created record should display first.. anyway thanks for quick response. i will try to see if i can write a trigger to populate the rowid into another custom column so that i can use that instead. 

    • Karthik Ramachandran

      Guys,

      Once again .. different queries but related to same topic.. i tried different approach but somehow it is not working as expected. Not able to find more details in the net.

      Please see the image.. 

      https://ibb.co/1JFLNCR