4 Replies Latest reply: May 17, 2013 2:35 AM by Marwim RSS

    Commit after every 10 000 records inserted from one table to the other

    1009380
      What would be the best way to Commit after every 10 000 records inserted from one table to the other using the following script :


      DECLARE
      l_max_repa_id x_received_p.repa_id%TYPE;
      l_max_rept_id x_received_p_trans.rept_id%TYPE;
      BEGIN
      SELECT MAX (repa_id)
      INTO l_max_repa_id
      FROM x_received_p
      WHERE repa_modifieddate <= ADD_MONTHS (SYSDATE, -6);

      SELECT MAX (rept_id)
      INTO l_max_rept_id
      FROM x_received_p_trans
      WHERE rept_repa_id = l_max_repa_id;


      INSERT INTO x_p_requests_arch
      SELECT *
      FROM x_p_requests
      WHERE pare_repa_id <= l_max_rept_id;

      DELETE FROM x__requests
      WHERE pare_repa_id <= l_max_rept_id;