Re: Store Procedure Help

aaron_rouse_at_yahoo.com (Aaron Rouse) wrote in message news:<a57b6daf.0301101427.498cfc9e_at_posting.google.com>...
> Thanks for the help and that is correct on the MAX() function is not
> needed. That query was originally given to me in the spec and today I
> noticed it was unneeded. I had tried the fetch in one form or another
> yesterday, I honestly can not remember if it was a exact copy in
> syntax as was shown, regardless it would end up erroring out. The
> method that did end up working for me is this:
>
>
> CREATE OR REPLACE procedure SP_SLB_LOCORGSAP AS
> -- Cursor for Organizations
> CURSOR ORG IS
> SELECT CD_ORG, NM_LST_UPDT, DT_LST_UPDT, CD_SEG FROM
> ORGANIZATIONS WHERE CD_ORG NOT IN (SELECT CD_ORG FROM
> ORGANIZATIONS_DTL);
>
> -- Cursor for Locations
> 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);
>
> -- For Loopcount in Organizations
> LOOPORG_ID NUMBER;
>
> -- For Loopcount in Locations
> LOOPLOC_ID NUMBER;
>
> BEGIN
> SELECT CD_SYSKEY_NEXT INTO LOOPLOC_ID FROM TMS_SYSKEY WHERE
> NM_TABLE = 'LOCATIONS_DTL';
> FOR i IN LOC LOOP
> -- Insert in LOCATIONS_DTL
> INSERT INTO LOCATIONS_DTL
> (ID_LOC, CD_LOC, NM_LST_UPDT, DT_LST_UPDT, CD_SEG)
> VALUES
> (LOOPLOC_ID, i.CD_LOC, i.NM_LST_UPDT, i.DT_LST_UPDT, i.CD_SEG);
> -- Increment loop count
> LOOPLOC_ID := LOOPLOC_ID + 1;
> COMMIT;
> END LOOP;
> -- UPDATE new Max(CD_SYSKEY_NEXT)
> UPDATE TMS_SYSKEY SET CD_SYSKEY_NEXT = LOOPLOC_ID WHERE NM_TABLE =
> 'LOCATIONS_DTL';
> COMMIT;
>
> -- Organizations Code
> SELECT CD_SYSKEY_NEXT INTO LOOPORG_ID FROM TMS_SYSKEY WHERE
> NM_TABLE = 'ORGANIZATIONS_DTL';
> FOR i IN ORG LOOP
> -- Insert in ORGANIZATIONS_DTL
> INSERT INTO ORGANIZATIONS_DTL
> (ID_ORG, CD_ORG, CD_ORG_TYPE, NM_LST_UPDT, DT_LST_UPDT,
> CD_ORG_STATUS, CD_SEG)
> VALUES
> (LOOPORG_ID, i.CD_ORG, 8, i.NM_LST_UPDT, i.DT_LST_UPDT, 'A', 230);
> -- Increment loop count
> LOOPORG_ID := LOOPORG_ID + 1;
> COMMIT;
> END LOOP;
> -- UPDATE new Max(CD_SYSKEY_NEXT)
> UPDATE TMS_SYSKEY SET CD_SYSKEY_NEXT = LOOPORG_ID WHERE NM_TABLE =
> 'ORGANIZATIONS_DTL';
> COMMIT;
> END SP_SLB_LOCORGSAP;
First of all you have too many commits in there. No chance of rollback.

If this procedure is called from a client, then all commits and rollbacks should be handled by the client.

If this procedure is only called from a batch script then you need to handle dup_val_on_index exception for each loop using a savepoint. This is because you atleast want to insert good records rather then terminating on each error.

Just a thought.
/Rauf Sarwar Received on Fri Jan 10 2003 - 22:53:53 CST