Solved

Invalid Identfier Error (00904) in dup_val_on_index exception block

Posted on 2003-11-11
5
2,212 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now