loop error [message #38096] |
Wed, 20 March 2002 05:19 |
Lance Pris
Messages: 40 Registered: January 2002
|
Member |
|
|
Can someone look at this at see what I am doing wrong:
The following SQL returns to records:
50
333
select SC_USER_ID as parmSecurename from CP_USER_COMMENT where SC_ID = 107793;
If I include it in the following loop the result is
50
333
333
CREATE OR REPLACE Procedure SP_USER_COMMENT_2
as
--Set the User SECURE ID
parmSecurename CP_USER_COMMENT.SC_USER_ID%TYPE;
Cursor SC_SECURENAME is select SC_USER_ID as parmSecurename from CP_USER_COMMENT where SC_ID = 107793;
Begin
Open SC_SECURENAME;
--While SC_SECURE%rowcount < 20
Loop /* Add next three lines */
fetch SC_SECURENAME into parmSecurename;
dbms_output.put_line ('USER ID: '||parmSecurename);
exit when SC_SECURENAME%NOTFOUND;
End LOOP;
Close SC_SECURENAME;
Commit;
End;
/
PLease help
Thank you in advance
|
|
|
Re: loop error [message #38103 is a reply to message #38096] |
Wed, 20 March 2002 17:18 |
seng
Messages: 191 Registered: February 2002
|
Senior Member |
|
|
No thing is wrong in your source code, but this is caused by CURSOR logic only and cause you to get extra output (333).
In this cursor SC_SECURENAME loop, when it get 333 and return false in "exit when" and so your loop repeat again and output another 333 before your loop get true in "exit when" to exit loop . To prevent this, you should put output (DBMS_OUTPUT) after "exit when".
Hope this is helping
|
|
|