On Apr 21, 4:15 am, Sphenix <sphen...@gmail.com> wrote:
> ______________________________________
>
> CREATE PROCEDURE UPL_JC IS CURSOR C1 IS
>
> SELECT EMPLID
> ,P_UPL_START
> ,P_UPL_TAKE
> FROM PS_P_UPL_CALC_L_JC;
>
> V_EMPLID VARCHAR(10);
> V_P_UPL_START DATE;
> V_P_UPL_TAKE NUMBER;
> V_UPL_DATE DATE;
>
> BEGIN OPEN C1;
> LOOP
>
> FETCH C1 INTO V_EMPLID
> ,V_P_UPL_START
> ,V_P_UPL_TAKE;
>
> BEGIN WHILE V_P_UPL_TAKE > 0
>
> LOOP
>
> INSERT INTO PS_P_UPL_START_JC(EMPLID
> , P_UPL_START)
> VALUES(V_EMPLID
> , V_UPL_DATE);
>
> V_UPL_DATE:= V_P_UPL_START+V_P_UPL_TAKE;
> V_P_UPL_TAKE:= V_P_UPL_TAKE - 1;
>
> END LOOP;
>
> COMMIT;
> END;
> END LOOP;
> CLOSE C1;
> END;
>
> *Data in PS_P_UPL_CALC_L_JC*
> ______________________________________________
> EMPLID P_UPL_START P_UPL_TAKE
> 00998 01.01.2008 00:00:00 59
> 07243 22.10.2008 00:00:00 1
> 07661 17.03.2008 00:00:00 198
> ______________________________________________
> Select * from PS_P_UPL_CALC_L_JC
> ______________________________________________
>
> I'm having infinite loop on this PL/SQL, suspect is cause right here
> " BEGIN OPEN C1;
> LOOP "
This is, well, antiquated code, really because a FOR loop would be so
much cleaner. That being said you're missing the following code in
your cureor loop:
EXIT WHEN C!%NOTFOUND;
So where does that go? Here, actually:
BEGIN OPEN C1;
LOOP
FETCH C1 INTO V_EMPLID
,V_P_UPL_START
,V_P_UPL_TAKE;
EXIT WHEN C1%NOTFOUND;
That will terminate the endless loop you've constructed. Of course if
you want to take the group advice and rewrite this loop:
FOR C1_REC IN C1 LOOP
WHILE C1_REC.P_UPL_TAKE > 0
LOOP
INSERT INTO PS_P_UPL_START_JC(EMPLID
, P_UPL_START)
VALUES(C1_REC.EMPLID
, C1_REC.P_UPL_START);
...
END LOOP;
...
END LOOP;
Modify the example as you see fit.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
