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
Solved

Invalid Identfier Error (00904) in dup_val_on_index exception block

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Bash Script to Analyze Oracle Schemas 11 117
run sql script from putty 4 62
oracle date format checking 7 26
Need help on decision table structure 7 26
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

792 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