Returning from Exception Handler

From: Kevin J. Donovan <kdonovan_at_edgewater.com>
Date: 1996/08/09
Message-ID: <897cc$f2b1d.175_at_ineter.edgewater.com>

Help diffculty returning to loop from exception handler

Im having some error handling headaches here... not really headaches, more like Im pulling my hair out ! Heres the problem, Within a stored procedure I have cursor that is populated via a select statement. I take the values of this cursor and then insert them into another table in a loop. My problem is that there may be records in the cursor that would be considered duplicate rows ie: (ORA-00001) in the table Im attempting to insert the records into. I have no problem trapping this error, my problem is after I have trapped the error my procedure ends! What I want to happen is when the exception is noted the procedure returns to the next item in the loop. Now as a work around I have put an IF construct in the middle of my loop that looks in the table Im trying to inset into for the record before I do it, I know there has got to be a better way ! HELP ! The exact syntax of my procedure is as follows:

Thanks in advance
Kevin Donovan kdonovan_at_edgewater.com Edgewater Technology (617) 246-3343 voice 20 Harvard Mill Square (617) 246-5903 fax Wakefield, MA 01880

/*
***********************************************************************

*** Creating Procedure LOADTAB_DAILY_LEG

---

• Procedure maintains Table DAILY_LEG

  ---

• Loads the daily_leg table based on

  ---

• values in sch_trip_plan and

  ---

• sch_leg_plan. Then update the tail

  ---

• number with the tail no for that

  ---

• same ROUTE / TRIP / LEG from the

  ---

• previous day. *****************************
• EDIT: K. Donovan *****************************
• DESC: Handle Overlapping TRIP & LEG

  ---

• Records. *****************************

  ---

  */ CREATE OR REPLACE PROCEDURE LOADTAB_DAILY_LEG (INVAR_NEXTDT IN DAILY_LEG.EFFECTIVE_DT%TYPE) AS LVAR_RTE DAILY_LEG.ROUTE%TYPE NULL; LVAR_TRP DAILY_LEG.TRIP%TYPE NULL; LVAR_EFD DAILY_LEG.EFFECTIVE_DT%TYPE NULL; LVAR_LEG DAILY_LEG.LEG%TYPE NULL; LVAR_ORI DAILY_LEG.ORIGIN%TYPE NULL; LVAR_DST DAILY_LEG.DESTINATION%TYPE NULL; LVAR_DCT DAILY_LEG.DISCONTINUE_DT%TYPE NULL; LVAR_DTZ DAILY_LEG.DESTINATION_TIME_ZONE%TYPE NULL; LVAR_OTZ DAILY_LEG.ORIGIN_TIME_ZONE%TYPE NULL; LVAR_STD DAILY_LEG.STD%TYPE NULL; LVAR_STA DAILY_LEG.STA%TYPE NULL; LVAR_STT DAILY_LEG.SCHEDULED_TENDER_TM%TYPE NULL; LVAR_SDT DAILY_LEG.SCHEDULED_DELIVERY_TM%TYPE NULL; LVAR_NET DAILY_LEG.NETWORK%TYPE NULL; LVAR_NOL DAILY_LEG.NO_OF_LEGS%TYPE NULL; LVAR_EQT DAILY_LEG.EQUIPMENT_TYPE%TYPE NULL; LVAR_CNT DAILY_LEG.CTS_NEXT_TRIP%TYPE NULL; LVAR_STY DAILY_LEG.SERVICE_TYPE%TYPE NULL; LVAR_IOI DAILY_LEG.IN_OUT_INDICATOR%TYPE NULL; ERR_DUP EXCEPTION; LVAR_CHK NUMBER; PRAGMA EXCEPTION_INIT (ERR_DUP, -00001); CURSOR NEW_LEGS IS SELECT SCH_TRIP_PLAN.ROUTE, SCH_TRIP_PLAN.TRIP, INVAR_NEXTDT, SCH_LEG_PLAN.LEG, SCH_LEG_PLAN.ORIGIN, SCH_LEG_PLAN.DESTINATION, SCH_TRIP_PLAN.DISCONTINUE_DT, FACILITY.TIME_ZONE AS "ORIGIN_TZ", FACILITY_DUP.TIME_ZONE AS "DESTN_TZ", DECODE (TO_CHAR(SCH_LEG_PLAN.STD,'DD-MON-YYYY'),'01-JAN-1980', TO_DATE(TO_CHAR(INVAR_NEXTDT, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.STD,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'), TO_DATE(TO_CHAR(INVAR_NEXTDT + 1, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.STD,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')) AS "STD_VAR", DECODE (TO_CHAR(SCH_LEG_PLAN.STA,'DD-MON-YYYY'),'01-JAN-1980', TO_DATE(TO_CHAR(INVAR_NEXTDT, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.STA,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'), TO_DATE(TO_CHAR(INVAR_NEXTDT + 1, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.STA,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')) AS "STA_VAR", DECODE (TO_CHAR(SCH_LEG_PLAN.SCHEDULED_TENDER_TM,'DD-MON-YYYY'), '01-JAN-1980', TO_DATE(TO_CHAR(INVAR_NEXTDT, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.SCHEDULED_TENDER_TM,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'), '02-JAN-1980', TO_DATE(TO_CHAR(INVAR_NEXTDT + 1, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.SCHEDULED_TENDER_TM,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'), NULL) AS "SCHED_TEND", DECODE (TO_CHAR(SCH_LEG_PLAN.SCHEDULED_DELIVERY_TM,'DD-MON-YYYY'), '01-JAN-1980', TO_DATE(TO_CHAR(INVAR_NEXTDT, 'DD-MON-YYYY') ||' '||

TO_CHAR(SCH_LEG_PLAN.SCHEDULED_DELIVERY_TM,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),

         '02-JAN-1980',
   TO_DATE(TO_CHAR(INVAR_NEXTDT + 1, 'DD-MON-YYYY') ||' '|| TO_CHAR(SCH_LEG_PLAN.SCHEDULED_DELIVERY_TM,'HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),

   NULL) AS "SCHED_DEL",

   SCH_TRIP_PLAN.NETWORK,
   SCH_TRIP_PLAN.NO_OF_LEGS,
   SCH_TRIP_PLAN.EQUIPMENT_TYPE,
   SCH_TRIP_PLAN.CTS_NEXT_TRIP,
   SCH_TRIP_PLAN.SERVICE_TYPE,
   SCH_TRIP_PLAN.IN_OUT_INDICATOR 

   FROM
   SCH_TRIP_PLAN,
   SCH_LEG_PLAN,
   FACILITY,
   FACILITY FACILITY_DUP
   WHERE(
   (INVAR_NEXTDT BETWEEN SCH_TRIP_PLAN.EFFECTIVE_DT AND SCH_TRIP_PLAN.DISCONTINUE_DT) AND
   (INVAR_NEXTDT BETWEEN SCH_LEG_PLAN.EFFECTIVE_DT AND

SCH_LEG_PLAN.DISCONTINUE_DT)   AND
   (SCH_TRIP_PLAN.ROUTE          =  SCH_LEG_PLAN.ROUTE)          AND
   (SCH_TRIP_PLAN.TRIP           =  SCH_LEG_PLAN.TRIP)           AND
   (SCH_TRIP_PLAN.EFFECTIVE_DT   =  SCH_LEG_PLAN.EFFECTIVE_DT)   AND
   (SCH_TRIP_PLAN.DISCONTINUE_DT =  SCH_LEG_PLAN.DISCONTINUE_DT) AND 
   (SCH_LEG_PLAN.ORIGIN          =  FACILITY.FACILITY_CODE)      AND
   (SCH_LEG_PLAN.DESTINATION     =  FACILITY_DUP.FACILITY_CODE));

BEGIN
   OPEN NEW_LEGS;
   LOOP

      FETCH NEW_LEGS
      INTO
      LVAR_RTE,	
      LVAR_TRP,	
      LVAR_EFD,	
      LVAR_LEG,	
      LVAR_ORI,	
      LVAR_DST,	
      LVAR_DCT,	
      LVAR_DTZ,	
      LVAR_OTZ,	
      LVAR_STD,	
      LVAR_STA,	
      LVAR_STT,	
      LVAR_SDT,	
      LVAR_NET,	
      LVAR_NOL,	
      LVAR_EQT,
      LVAR_CNT,
      LVAR_STY,	
      LVAR_IOI;	
      EXIT WHEN NEW_LEGS%NOTFOUND;
      SAVEPOINT PERFORM_INSERT;
      /* This is a less than desirable method of checking 
         for duplicates but very effective.               */
      SELECT COUNT(*) 
      INTO LVAR_CHK
      FROM DAILY_LEG
      WHERE (
      (DAILY_LEG.ROUTE          =  LVAR_RTE)    AND
      (DAILY_LEG.TRIP           =  LVAR_TRP)    AND
      (DAILY_LEG.EFFECTIVE_DT	=  LVAR_EFD)	AND
      (DAILY_LEG.LEG		=  LVAR_LEG));
      IF LVAR_CHK = 0 THEN
         INSERT INTO DAILY_LEG (
         ROUTE,
         TRIP,
         EFFECTIVE_DT,
         LEG,
         ORIGIN,
         DESTINATION,
         DISCONTINUE_DT,
         DESTINATION_TIME_ZONE,
         ORIGIN_TIME_ZONE,
         STD,
         STA,
         SCHEDULED_TENDER_TM,
         SCHEDULED_DELIVERY_TM,
         NETWORK,
         NO_OF_LEGS,
         EQUIPMENT_TYPE,
         CTS_NEXT_TRIP,
         SERVICE_TYPE,
         IN_OUT_INDICATOR )      
         VALUES (
         LVAR_RTE,	
         LVAR_TRP,	
         LVAR_EFD,	
         LVAR_LEG,	
         LVAR_ORI,	
         LVAR_DST,	
         LVAR_DCT,	
         LVAR_DTZ,	
         LVAR_OTZ,	
         LVAR_STD,	
         LVAR_STA,	
         LVAR_STT,	
         LVAR_SDT,	
         LVAR_NET,	
         LVAR_NOL,	
         LVAR_EQT,
         LVAR_CNT,
         LVAR_STY,	
         LVAR_IOI );	
         COMMIT;
      END IF;
   END LOOP; 
     EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
         ROLLBACK TO SAVEPOINT PERFORM_INSERT; 

   COMMIT;
END LOADTAB_DAILY_LEG;
/ Received on Fri Aug 09 1996 - 00:00:00 CEST