Home » SQL & PL/SQL » SQL & PL/SQL » loop error
loop error [message #38096] Wed, 20 March 2002 05:19 Go to next message
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 Go to previous message
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
Previous Topic: Accessing PL/SQL Tables from SQL*Plus
Next Topic: Locking when update record
Goto Forum:
  


Current Time: Thu Mar 28 13:08:49 CDT 2024