2 Replies Latest reply: Nov 6, 2011 11:33 AM by 898251 RSS

    Procedure looping when executes with 100k - 200k records

    898251
      Hi All,

      We have a requirement to move 200k records from a source table to a destination table where the data are stored in XML format. We have written a procedure which reads the XML data and store it in a temp table using BULK COLLECT and then using LOOP we inserted the record from the sourct to the destination table after performing some conditions.

      The logic works fine when we tested with 100 records but, with 2k records it tooks 7-9 hrs to complete where as with more than 10k it is looping without any error meassage and the process doesnt completed.

      I have attached the procedure below. Can anyone help to get the issue resolved. I would appreciate your thoughts on this?


      PROCEDURE DataTransfer
      IS
      xmlTbl srv.xmlTblProd;
      maxVol     TIMESTAMP;
      recCount PLS_INTEGER := 0;

      BEGIN

      SELECT MAX(udt_task) max_ INTO maxVol FROM source.table1;

      SELECT XMLELEMENT ("brokw",
      xmlattributes (act AS "action"),
      XMLELEMENT ("acct", accNo),
      XMLELEMENT ("orig", coOrigin),
      XMLELEMENT ("pend", pendOrder),
      XMLELEMENT ("user", NVL (EXTRACTVALUE (hose, '//product/Items/User'),
                                    EXTRACTVALUE (parts, '//Items/User'))),
      hose
      ) xml_
      BULK COLLECT INTO xmlTbl
      FROM (SELECT x.accNo, y.rec pendOrder, x.ROWID coOrigin, y.parts, y.hose,
      CASE
      WHEN y.collect = 300 THEN 'T'
      ELSE CASE
      WHEN y.collect = 100 THEN 'I'
      ELSE 'F'
      END
      END act
      FROM (SELECT a.ROWID rec, a.*, ROW_NUMBER () OVER (PARTITION BY accNo ORDER BY udt_task DESC) r
      FROM source.table1 a
      WHERE maxVol >= udt_task) y,
      dest.table2 x
      WHERE x.accNo(+) = y.accNo AND r = 1);


      recCount := SQL%ROWCOUNT;
      recCount := 0;

      IF xmlTbl.COUNT > 0
      THEN

      FOR rec IN (SELECT EXTRACTVALUE (COLUMN_VALUE, '//brokw/orig/text()') recid,
      EXTRACTVALUE (COLUMN_VALUE, '//brokw/user/text()') custDet,
      EXTRACT (COLUMN_VALUE, '/brokw/rec') reas
      FROM
                     TABLE (xmlTbl)
      WHERE
                     EXTRACTVALUE (COLUMN_VALUE, '//brokw/@action') = 'T'
      AND EXTRACTVALUE (COLUMN_VALUE, '//brokw/orig/text()') IS NOT NULL)
      LOOP

      DELETE FROM dest.table2 WHERE ROWID = rec.recid;

      END LOOP;

      recCount := SQL%ROWCOUNT;
      logex.collect_log ('Processed sql%rowcount = ' || recCount);
      recCount := 0;

      -- ===========================================================================================
      MERGE INTO dest.table2 t
      USING (SELECT *
      FROM source.table1
      WHERE ROWID IN (
      SELECT EXTRACTVALUE (COLUMN_VALUE, '//brokw/pend/text()') FROM TABLE (xmlTbl)
      WHERE EXTRACTVALUE (COLUMN_VALUE, '//brokw/@action') = 'F')) s
      ON (s.accNo = t.accNo)
      WHEN MATCHED THEN
      UPDATE
      SET t.collect = s.collect,
      t.hose = s.hose,
      t.parts = s.parts,
      t.udt_task = s.udt_task
      WHEN NOT MATCHED THEN
      INSERT VALUES (s.accNo, s.collect, s.hose, s.parts, s.udt_task);

      -- ===========================================================================================

      recCount := SQL%ROWCOUNT;
      recCount := 0;


      DELETE FROM source.table1 WHERE maxVol >= udt_task;

      recCount := SQL%ROWCOUNT;
      recCount := 0;

      END IF;
      xmlTbl.delete;

      EXCEPTION
      WHEN OTHERS
      THEN

           RAISE;
      END;



      Thanks
      Anand