Invalid Identfier Error (00904) in dup_val_on_index exception block

I am trying to run a procedure which will insert every record through a loop unless the primary key is violated in which case an update will run, but I am unable to pass my variables successfully to my exception block as I am getting ORA-00904 Invalid Identifier.  The declaration of my variables match the data types in the table.  Is my exception block written incorrectly?
Thanks so much!

CREATE or REPLACE Procedure SUM_METER_SUM_STAGE AS
 
BEGIN
DECLARE
 sCertSerNbr      VARCHAR2(10);
 sPsdVendId            VARCHAR2(2);
 sPsdModId            VARCHAR2(2);
 sPsdSerNbr            VARCHAR2(10);
 dtCurrDt            DATE;
 nPcsCnt            NUMBER(6);
 nPostgSum            NUMBER(6);
 sBatchName            VARCHAR2(15);
 dtDtRun            DATE;
 nRecsInsertedKntr NUMBER(6);
 
 CURSOR curMeterSumStage IS  
           SELECT MS.CERT_SER_NBR,
                   MS.PSD_VEND_ID,
                   MS.PSD_MOD_ID,
                   MS.PSD_SER_NBR,
                   MS.CURR_DATE,
                   SUM(NVL(MS.PCS_CNT, 0)),
                   SUM(NVL(MS.POSTG_VAL_SUM, 0)),
                   'MeterSummary',
                   SYSDATE
            FROM METER_SUM_STAGE MS
            GROUP BY MS.CERT_SER_NBR,
                   MS.PSD_VEND_ID,
                 MS.PSD_MOD_ID,
                 MS.PSD_SER_NBR,
                    MS.CURR_DATE;
 BEGIN
 nRecsInsertedKntr := 0;

 OPEN curMeterSumStage;
             FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
                   nPcsCnt,nPostgSum,sBatchName,dtDtRun;
            WHILE curMeterSumStage%FOUND
            LOOP
                  INSERT INTO METER_SUM COLUMNS(CERT_SER_NBR,PSD_VEND_ID,PSD_MOD_ID,PSD_SER_NBR,CURR_DATE,
                  PCS_CNT,POSTG_VAL_SUM,UPDT_USER_ID,LAST_UPDT_DTM)
                                                VALUES(sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,nPcsCnt,nPostgSum,sBatchName,dtDtRun);

                  nRecsInsertedKntr := nRecsInsertedKntr + 1;
                  IF nRecsInsertedKntr MOD 500 = 0 THEN    
                        COMMIT;
                  END IF;
      
            FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
                  nPcsCnt,nPostgSum,sBatchName,dtDtRun;
            END LOOP;      

CLOSE   curMeterSumStage;         
COMMIT;           
END;


EXCEPTION when dup_val_on_index THEN
      
         UPDATE METER_SUM
            SET PCS_CNT = PCS_CNT + nPcsCnt,
                  POSTG_VAL_SUM = POSTG_VAL_SUM + nPostgSum,
                  LAST_UPDT_DTM = sysdate
            WHERE CERT_SER_NBR = sCertSerNbr
            AND PSD_VEND_ID = sPsdVendId
            AND PSD_SER_NBR = sPsdSerNbr
            AND PSD_MOD_ID = sPsdModId
            AND CURR_DATE = dtCurrDt;
      COMMIT;
      

END;
atlvandyguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
seazodiacCommented:
yes, indeed, identifier is out of scope, you should put the exception block inside the inner "begin end" block.

try this one:

CREATE or REPLACE Procedure SUM_METER_SUM_STAGE AS

BEGIN
DECLARE
sCertSerNbr     VARCHAR2(10);
sPsdVendId          VARCHAR2(2);
sPsdModId          VARCHAR2(2);
sPsdSerNbr          VARCHAR2(10);
dtCurrDt          DATE;
nPcsCnt          NUMBER(6);
nPostgSum          NUMBER(6);
sBatchName          VARCHAR2(15);
dtDtRun          DATE;
nRecsInsertedKntr NUMBER(6);

 CURSOR curMeterSumStage IS  
         SELECT MS.CERT_SER_NBR,
                MS.PSD_VEND_ID,
                MS.PSD_MOD_ID,
                MS.PSD_SER_NBR,
                MS.CURR_DATE,
                SUM(NVL(MS.PCS_CNT, 0)),
                SUM(NVL(MS.POSTG_VAL_SUM, 0)),
                'MeterSummary',
                SYSDATE
         FROM METER_SUM_STAGE MS
         GROUP BY MS.CERT_SER_NBR,
               MS.PSD_VEND_ID,
              MS.PSD_MOD_ID,
              MS.PSD_SER_NBR,
                MS.CURR_DATE;
BEGIN
 nRecsInsertedKntr := 0;

OPEN curMeterSumStage;
          FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
               nPcsCnt,nPostgSum,sBatchName,dtDtRun;
         WHILE curMeterSumStage%FOUND
          LOOP
              INSERT INTO METER_SUM COLUMNS(CERT_SER_NBR,PSD_VEND_ID,PSD_MOD_ID,PSD_SER_NBR,CURR_DATE,
              PCS_CNT,POSTG_VAL_SUM,UPDT_USER_ID,LAST_UPDT_DTM)
                                       VALUES(sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,nPcsCnt,nPostgSum,sBatchName,dtDtRun);

              nRecsInsertedKntr := nRecsInsertedKntr + 1;
              IF nRecsInsertedKntr MOD 500 = 0 THEN    
                    COMMIT;
              END IF;
   
          FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
              nPcsCnt,nPostgSum,sBatchName,dtDtRun;
         END LOOP;    

CLOSE   curMeterSumStage;        
COMMIT;        


EXCEPTION when dup_val_on_index THEN
     
       UPDATE METER_SUM
          SET PCS_CNT = PCS_CNT + nPcsCnt,
               POSTG_VAL_SUM = POSTG_VAL_SUM + nPostgSum,
               LAST_UPDT_DTM = sysdate
         WHERE CERT_SER_NBR = sCertSerNbr
          AND PSD_VEND_ID = sPsdVendId
         AND PSD_SER_NBR = sPsdSerNbr
          AND PSD_MOD_ID = sPsdModId
          AND CURR_DATE = dtCurrDt;
     COMMIT;
   
END;
END;
/
0
 
yorenCommented:
Actually, I think you want something more like this:

CREATE or REPLACE Procedure SUM_METER_SUM_STAGE AS
 
BEGIN
DECLARE
 sCertSerNbr     VARCHAR2(10);
 sPsdVendId          VARCHAR2(2);
 sPsdModId          VARCHAR2(2);
 sPsdSerNbr          VARCHAR2(10);
 dtCurrDt          DATE;
 nPcsCnt          NUMBER(6);
 nPostgSum          NUMBER(6);
 sBatchName          VARCHAR2(15);
 dtDtRun          DATE;
 nRecsInsertedKntr NUMBER(6);
 
 CURSOR curMeterSumStage IS  
          SELECT MS.CERT_SER_NBR,
                 MS.PSD_VEND_ID,
                 MS.PSD_MOD_ID,
                 MS.PSD_SER_NBR,
                 MS.CURR_DATE,
                 SUM(NVL(MS.PCS_CNT, 0)),
                 SUM(NVL(MS.POSTG_VAL_SUM, 0)),
                 'MeterSummary',
                 SYSDATE
          FROM METER_SUM_STAGE MS
          GROUP BY MS.CERT_SER_NBR,
                MS.PSD_VEND_ID,
               MS.PSD_MOD_ID,
               MS.PSD_SER_NBR,
                 MS.CURR_DATE;
 BEGIN
 nRecsInsertedKntr := 0;

 OPEN curMeterSumStage;
           FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
                nPcsCnt,nPostgSum,sBatchName,dtDtRun;
          WHILE curMeterSumStage%FOUND
          LOOP
          BEGIN
               INSERT INTO METER_SUM COLUMNS(CERT_SER_NBR,PSD_VEND_ID,PSD_MOD_ID,PSD_SER_NBR,CURR_DATE,
               PCS_CNT,POSTG_VAL_SUM,UPDT_USER_ID,LAST_UPDT_DTM)
                                        VALUES(sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,nPcsCnt,nPostgSum,sBatchName,dtDtRun);
           
               nRecsInsertedKntr := nRecsInsertedKntr + 1;
               IF nRecsInsertedKntr MOD 500 = 0 THEN    
                    COMMIT;
               END IF;

           EXCEPTION when dup_val_on_index THEN
     
              UPDATE METER_SUM
                SET PCS_CNT = PCS_CNT + nPcsCnt,
                     POSTG_VAL_SUM = POSTG_VAL_SUM + nPostgSum,
                     LAST_UPDT_DTM = sysdate
                WHERE CERT_SER_NBR = sCertSerNbr
                AND PSD_VEND_ID = sPsdVendId
                AND PSD_SER_NBR = sPsdSerNbr
                AND PSD_MOD_ID = sPsdModId
                AND CURR_DATE = dtCurrDt;
                  
               COMMIT;
     

             END;

     
          FETCH curMeterSumStage INTO sCertSerNbr,sPsdVendId,sPsdModId,sPsdSerNbr,dtCurrDt,
               nPcsCnt,nPostgSum,sBatchName,dtDtRun;
          END LOOP;    

CLOSE   curMeterSumStage;        
COMMIT;          
END;


0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
atlvandyguyAuthor Commented:
Thanks so much!  That worked perfectly!
0
 
andrewstCommented:
If you have Oracle 9i you can use the new MERGE statement to do the entire job in one go, like this:

MERGE INTO meter_sum    
USING ( SELECT MS.CERT_SER_NBR,
               MS.PSD_VEND_ID,
               MS.PSD_MOD_ID,
               MS.PSD_SER_NBR,
               MS.CURR_DATE,
               SUM(NVL(MS.PCS_CNT, 0)) SUM_PCS_CNT,
               SUM(NVL(MS.POSTG_VAL_SUM, 0)) SUM_POSTG_VAL_SUM,
               'MeterSummary' BATCH_NAME
          FROM METER_SUM_STAGE MS
         GROUP BY MS.CERT_SER_NBR,
               MS.PSD_VEND_ID,
               MS.PSD_MOD_ID,
               MS.PSD_SER_NBR,
               MS.CURR_DATE
      ) source
ON    (   meter_sum.CERT_SER_NBR = source.CERT_SER_NBR
      AND meter_sum.PSD_VEND_ID  = source.PSD_VEND_ID
      AND meter_sum.PSD_SER_NBR  = source.PSD_SER_NBR
      AND meter_sum.PSD_MOD_ID   = source.PSD_MOD_ID
      AND meter_sum.CURR_DATE    = source.CURR_DATE
      )
WHEN MATCHED THEN
    UPDATE SET PCS_CNT = PCS_CNT + source.SUM_PCS_CNT,
               POSTG_VAL_SUM = POSTG_VAL_SUM + source.SUM_POSTG_VAL_SUM,
               LAST_UPDT_DTM = SYSDATE
WHEN NOT MATCHED THEN
    INSERT (CERT_SER_NBR,PSD_VEND_ID,PSD_MOD_ID,PSD_SER_NBR,CURR_DATE,
            PCS_CNT,POSTG_VAL_SUM,UPDT_USER_ID,LAST_UPDT_DTM)
    VALUES (source.CERT_SER_NBR,source.PSD_VEND_ID,source.PSD_MOD_ID,source.PSD_SER_NBR,source.CURR_DATE,
            source.SUM_PCS_CNT,source.SUM_POSTG_VAL_SUM,source.BATCH_NAME,SYSDATE);

(Hope I didn't make any mistakes there.)
0
 
seazodiacCommented:
atlvandyguy:

Which post answered your question? Please accept the answer and assign the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.