3 Replies Latest reply on Dec 15, 2017 4:02 PM by 3249625

    Bulk batch insert of records needed using oracle SOA

    3249625

      Dear friends

      we have soa 11.1.1.7

      and a requirement of insert 2 million records in a table,the input will be batch of 500 records in each xml. These xml files will be uploaded in file server location from where needs to fetch with file adapter.

      xsd structure for xml data is based on the database table structure

       

      kindly advice the best approach to fulfill this requirement in oracle soa.

       

      regards,

      jdev

        • 1. Re: Bulk batch insert of records needed using oracle SOA
          Srinu1477

          Hello Jdev,

           

          Since it doesn't need any modification of input data, only direct insert, better to go for ODI solution. Below are pros for this approach

          1. We can store all XML file data into temp table and we can insert all data at go instead of hitting the database for every XML.

          2. We can set the rules like skip duplicate data in different XML files, unlike in SOA where XML file will be failed if any integrity error comes during runtime.

          3. Also we can check the count of the records inserted, skipped from ODI editor itself.

           

          Thanks,

          Sreeni

          1 person found this helpful
          • 2. Re: Bulk batch insert of records needed using oracle SOA
            AnatoliAtanasov

            Hi Jdev,

             

            Here are my two cents on your question:

            1. Are you absolutely required to use SOA Suite for this requirement? And is it an one-shot insertion of these 2 M records or you will have to run it regularly?

             

            My experience with similar data integration tasks implemented with SOA Suite shows that you must very carefully plan, size and configure the implementation. Otherwise you will experience SOA Infra overload leading to eventual server crashes. If you host other mission critical services on the same server their SLAs will suffer for sure. In 12c you have more control over prioritisation of workload in the WLS managed server hosting SOA, but unfortunately this is not valid for 11g. Bottom line: be extremely careful when configuring the polling interval and how the records will be batched as you might cause overload of your server and ...

             

            2. If you still have to use SOA Suite here is how I would approach the case:

                 - user FTP adapter to poll a few files at a time. If possible try to mount the remote server to the local (soa_server's) file system and use the File Adapter to eliminate the complexity in configuring the JCA adapter;

                 - develop a PL/SQL procedure that will handle the actual insertion in the database. You even can make it accepting XML parameter holding batch of records and using the PL/SQL XML Parser packages to implement the looping and transformation in the database. Moreover, you will have tight control over the insertion process and more importantly will make your BPEL process much more simpler;

                 - use a BPEL just to split in 500 records batches in smaller ones that you will pass to the PL/SQL procedure.

             

            Remember SOA Suite is a SOA service implementation platform and not data integration one, so use it wisely, i.e. just to orchestrate invocations of backend functionality and not to process the files you are trying to load.

             

            HTH,

            A.

             

            P.S.: Check the post above advising to use ODI instead of SOA.

            1 person found this helpful
            • 3. Re: Bulk batch insert of records needed using oracle SOA
              3249625

              thanks guys for the response...will work on it as you guys suggested....