Re: Store Procedure Help

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 9 Jan 2003 22:47:17 -0800
Message-ID: <92eeeff0.0301092247.6b0d9d26@posting.google.com>

aaron_rouse_at_yahoo.com (Aaron Rouse) wrote in message news:<a57b6daf.0301091451.24d3b8b6_at_posting.google.com>...
> I am trying to create a pretty simple stored procedure but when I try
> to add my incrementing value I get errors on the = sign in the compile
> process. What I need to do is insert the CD_SYSKEY_NEXT value I pull
> from TMS_SYSKEY into my SLB_ABR_TEST table and then increment that by
> one for each loop instance then insert the last value into the
> TMS_SYSKEY table for future use. However I am to the point below and
> am unsure how to correct it and move on with the procedure:
>
> CREATE OR REPLACE procedure SP_SLB_TEST AS
> CURSOR LOC IS
> SELECT CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG FROM
> LOCATIONS WHERE CD_LOC NOT IN (SELECT CD_LOC FROM LOCATIONS_DTL);
>
> CURSOR NEWID IS
> SELECT MAX(CD_SYSKEY_NEXT) AS LOC_ID FROM TMS_SYSKEY WHERE
> NM_TABLE = 'LOCATIONS_DTL';
>
> loopLOC_ID NUMBER(3);
>
> loopLOC_ID := NEWID.LOC_ID;
>
> BEGIN
> FOR i IN LOC LOOP
> -- loopLOC_ID would be inserted into ID_LOC
> -- Insert in LOCATIONS_DTL
> INSERT INTO SLB_ABR_TEST
> (CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG)
> VALUES
> (i.CD_LOC, i.NM_LST_UPDT, i.DT_LST_UPDT, i.CD_SEG);
> -- loopLOC_ID := loopLOC_ID + 1;
> COMMIT;
> END LOOP;
> -- Need to insert the loopLOC_ID into TMS_SYSKEY
> END SP_SLB_TEST;
CREATE OR REPLACE procedure SP_SLB_TEST AS

   CURSOR LOC IS

      SELECT CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG 
      FROM LOCATIONS 
      WHERE CD_LOC NOT IN (SELECT CD_LOC 
                           FROM LOCATIONS_DTL);

   --
   CURSOR NEWID IS

      SELECT NVL(MAX(CD_SYSKEY_NEXT), 1) 
      FROM TMS_SYSKEY WHERE
      NM_TABLE = 'LOCATIONS_DTL';

   --
   loopLOC_ID NUMBER(3);
   dummy_ NUMBER(3);

• Incorrect syntax. This is set under BEGIN section by opening the cursor
• loopLOC_ID := NEWID.LOC_ID; BEGIN OPEN NEWID; FETCH NEWID INTO loopLOC_ID; CLOSE NEWID; dummy_ := loopLOC_ID; -- FOR i IN LOC LOOP SAVEPOINT TMP; BEGIN
  • Not sure where you are using loopLOC_ID in this insert INSERT INTO SLB_ABR_TEST (CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG) VALUES (i.CD_LOC, i.NM_LST_UPDT, i.DT_LST_UPDT, i.CD_SEG);
  • Exception Handling EXCEPTION WHEN OTHERS THEN BEGIN
    • This prevents incrementing loopLOC_ID on any Exception ROLLBACK TO TMP; END; END; loopLOC_ID := loopLOC_ID + 1; END LOOP;
• loopLOC_ID is incremented to last successfull loop occurance IF loopLOC_ID > dummy_ THEN INSERT INTO TMS_SYSKEY (CD_SYSKEY_NEXT, ............) VALUES (loopLOC_ID, .........); END IF; COMMIT; END SP_SLB_TEST; /

Regards
/Rauf Sarwar Received on Fri Jan 10 2003 - 00:47:17 CST