Solved

Invalid Identfier Error (00904) in dup_val_on_index exception block

Posted on 2003-11-11
5
2,228 Views
Last Modified: 2007-12-19
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;
0
Comment
Question by:atlvandyguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9726388
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
 
LVL 7

Accepted Solution

by:
yoren earned 500 total points
ID: 9727071
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
 

Author Comment

by:atlvandyguy
ID: 9731053
Thanks so much!  That worked perfectly!
0
 
LVL 15

Expert Comment

by:andrewst
ID: 9731072
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9732955
atlvandyguy:

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question