asked on

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;

seazodiac

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

ASKER CERTIFIED SOLUTION

yoren

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

atlvandyguy

ASKER

Thanks so much! That worked perfectly!

andrewst

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.)

seazodiac

atlvandyguy:

Which post answered your question? Please accept the answer and assign the points.