Re: Optimising PL/SQL Script. Takes 2 days to run.

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 22 Jan 2003 19:30:44 -0800
Message-ID: <92eeeff0.0301221930.75b2c131@posting.google.com>

david_at_fitzg.com (David FitzGerald) wrote in message news:<7f966bc5.0301220521.104eb3e4_at_posting.google.com>...
>
> On a sidenote -- if it errors during the insert, is it possible to
> tell Oracle to re-run picking up where it left off?

You can have exception handling inside your inner loop (because that is where you are doing inserts). The exception handling may not do anything but it will atleast keep the loop from breaking and will simply go to next record in the loop. This is usefull if you insert 200000 rows and routine breaks on one error and you have to start again.

Secondly, you can have a counter in your outer loop to do a commit after every e.g. 600 rows.

DECLARE
   cursor c_items is

      select ITEM, LOCATION from ITEM_LOCATIONS;    start_date DATE;
   counter_ NUMBER := 1;
BEGIN
   start_date := '23-Jan-2003';
   FOR this_item in c_items LOOP

      --	  	  
      FOR ctr in 0..6 LOOP
         INSERT INTO FORECAST 
            VALUES (this_item.item, item.location, start_date + ctr,
'0');
         BEGIN
            EXCEPTION
               WHERE OTHERS THEN
                  NULL;
         END;
      END LOOP;
      --
      counter_ := counter_ + 1;
      -- 100 * 6 inner loops is 600
      IF (counter_ = 100) THEN
         commit;
         counter_ := 1;
      ENDIF

   END LOOP;
   commit;
END;
/

You can modify this according to what others have suggested like no inner loop. In that case move the exception to outer loop.

Regards
/Rauf Sarwar Received on Wed Jan 22 2003 - 21:30:44 CST