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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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;
/
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;


Experts Exchange Solution brought to you by

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!
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.)
seazodiacCommented:
atlvandyguy:

Which post answered your question? Please accept the answer and assign the points.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.