Re: Stored Procedure Help

The original poster did not say what Oracle version he was using, the code from Andrew may not work. If it doesnt, all that needs to be done is to separate the cursor within the loop to an explicit one.

Also, look at the LPAD commands to append leading characters to a string.

Mark

Andrew Allen <ajallen_at_mailhost.det.ameritech.net> wrote in message news:<3E4C4B13.F27371D3_at_mailhost.det.ameritech.net>...
> Aaron Rouse wrote:
> >
> > I am attempting to make a store procedure that will loop over the data
> > in my table and prepend some zeros to one columns value, convert it to
> > a varchar and then insert the new value in another table. Upon
> > running the script I created, my client(Toad) ends up just hanging and
> > I eventually end up just ending the task on it. The record set I am
> > looping over has 23,411 records, is the procedure just inefficient for
> > that many rows or is their a flaw in the syntax I used in creating it?
> >
> > CREATE OR REPLACE procedure SP_SLB_SAPMERGE AS
> > -- Cursor for SAP Org Numbers
> > CURSOR SAPORG IS
> > SELECT A.NO_EMP, A.NO_EMP_NUSER3
> > FROM EMPLOYEES_DTL A, EMPLOYEES B
> > WHERE A.NO_EMP_NUSER3 IS NOT NULL
> > AND A.NO_EMP = B.NO_EMP
> > AND B.NM_LST_UPDT != 'EGATE';
> >
> > -- For New Org Code
> > SAPORG_NEW VARCHAR2(10);
> >
> > BEGIN
> > FOR i IN SAPORG LOOP
> > IF LENGTH(i.NO_EMP_NUSER3) = 1 THEN
> > SAPORG_NEW := '0000000' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 2 THEN
> > SAPORG_NEW := '000000' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 3 THEN
> > SAPORG_NEW := '00000' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 4 THEN
> > SAPORG_NEW := '0000' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 5 THEN
> > SAPORG_NEW := '000' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 6 THEN
> > SAPORG_NEW := '00' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 7 THEN
> > SAPORG_NEW := '0' || to_char(i.NO_EMP_NUSER3);
> > ELSIF LENGTH(i.NO_EMP_NUSER3) = 8 THEN
> > SAPORG_NEW := to_char(i.NO_EMP_NUSER3);
> > END IF;
> >
> > -- UPDATE Employees Table
> > UPDATE EMPLOYEES
> > SET CD_LOC = SAPORG_NEW
> > WHERE NO_EMP = i.NO_EMP;
> >
> > END LOOP;
> > COMMIT;
> > END SP_SLB_SAPMERGE;
> > /
>
> Try the following. It should work just fine. Well... check the num
> mask in the TO_CHAR -- It has been a while and I do not have a FM
> with me right now.
>
> CREATE OR REPLACE procedure SP_SLB_SAPMERGE
> AS
> BEGIN
> FOR i IN (SELECT a.no_emp,
> TO_CHAR(a.no_emp_nuser3,'00000009') AS saporg_new
> FROM employees_dtl a
> employees b
> WHERE a.no_emp_urser3 IS NOT NULL
> AND a.no_emp = b.no_emp
> AND b.nm_lst_updt != 'EGATE')
> LOOP
> UPDATE employees
> SET cd_loc = i.saporg_new
> WHERE no_emp = i.no_emp;
> END LOOP;
> COMMIT;
>
> END SP_SLB_SAPMERGE;
Received on Fri Feb 14 2003 - 02:40:10 CST