469,294 Members | 1,871 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,294 developers. It's quick & easy.

Cursor, Fetch, and while...

Hi guys,

I am having a comprehension problem for which I cannot seem to find an
answer (by banging my head on my keyboard.)

I have the following code which yields no results. I expect one result to
come out of it as I am using a while loop on a cursor set to retrieve one
single tuple.

Please be nice, it is my first day seeing cursors ;-)

Tia,

jeremy
CREATE TABLE people(
id int,
fname VARCHAR2(20),
lname VARCHAR2(20)
);

insert into people values (0, 'lois', 'lane');
insert into people values (1, 'clark', 'kent');
insert into people values (2, 'jimmy', 'olsen');

set serveroutput on;
CREATE OR REPLACE PROCEDURE show(curr_id INT)
AS
CURSOR curr_in IS SELECT fname, lname FROM people WHERE id=curr_id;
curr_fname VARCHAR2(20) := null;
curr_lname VARCHAR2(20) := null;

BEGIN
OPEN curr_in;

while curr_in%found loop
FETCH curr_in INTO curr_fname, curr_lname;
dbms_output.put_line('done');
dbms_output.put_line(curr_fname || ' ' || curr_lname);
end loop;

CLOSE curr_in;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode || ' ' || sqlerrm);
END;

/
Jul 19 '05 #1
2 22412
your WHILE loop never executes

-- mcs

"Jeremy Moncho" <je***********@bluewin.ch> wrote in message
news:BBF542FD.14A4%je***********@bluewin.ch...
| Hi guys,
|
| I am having a comprehension problem for which I cannot seem to find an
| answer (by banging my head on my keyboard.)
|
| I have the following code which yields no results. I expect one result to
| come out of it as I am using a while loop on a cursor set to retrieve one
| single tuple.
|
| Please be nice, it is my first day seeing cursors ;-)
|
| Tia,
|
| jeremy
|
|
| CREATE TABLE people(
| id int,
| fname VARCHAR2(20),
| lname VARCHAR2(20)
| );
|
| insert into people values (0, 'lois', 'lane');
| insert into people values (1, 'clark', 'kent');
| insert into people values (2, 'jimmy', 'olsen');
|
| set serveroutput on;
|
|
| CREATE OR REPLACE PROCEDURE show(curr_id INT)
| AS
| CURSOR curr_in IS SELECT fname, lname FROM people WHERE id=curr_id;
| curr_fname VARCHAR2(20) := null;
| curr_lname VARCHAR2(20) := null;
|
| BEGIN
| OPEN curr_in;
|
| while curr_in%found loop
| FETCH curr_in INTO curr_fname, curr_lname;
| dbms_output.put_line('done');
| dbms_output.put_line(curr_fname || ' ' || curr_lname);
| end loop;
|
| CLOSE curr_in;
|
| EXCEPTION
| WHEN OTHERS THEN
| dbms_output.put_line(sqlcode || ' ' || sqlerrm);
| END;
|
| /
|
|
Jul 19 '05 #2
Jeremy Moncho <je***********@bluewin.ch> wrote in message news:<BBF542FD.14A4%je***********@bluewin.ch>...
Hi guys,

I am having a comprehension problem for which I cannot seem to find an
answer (by banging my head on my keyboard.)

I have the following code which yields no results. I expect one result to
come out of it as I am using a while loop on a cursor set to retrieve one
single tuple.

Please be nice, it is my first day seeing cursors ;-)

Tia,

jeremy
CREATE TABLE people(
id int,
fname VARCHAR2(20),
lname VARCHAR2(20)
);

insert into people values (0, 'lois', 'lane');
insert into people values (1, 'clark', 'kent');
insert into people values (2, 'jimmy', 'olsen');

set serveroutput on;
CREATE OR REPLACE PROCEDURE show(curr_id INT)
AS
CURSOR curr_in IS SELECT fname, lname FROM people WHERE id=curr_id;
curr_fname VARCHAR2(20) := null;
curr_lname VARCHAR2(20) := null;

BEGIN
OPEN curr_in;

while curr_in%found loop
FETCH curr_in INTO curr_fname, curr_lname;
dbms_output.put_line('done');
dbms_output.put_line(curr_fname || ' ' || curr_lname);
end loop;

CLOSE curr_in;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlcode || ' ' || sqlerrm);
END;

/


You should not test curr_in%found until you have tried to FETCH a row.
Since you have not yet fetched a row, curr_in%found will be NULL not
TRUE and so the WHILE loop never executes.

You can avoid making these kind of bugs, and drastically simplify your
code, by avoiding the use of OPEN, FETCH, %FOUND and CLOSE altogether
like this:

CREATE OR REPLACE PROCEDURE show(curr_id INT)
AS
CURSOR curr_in IS SELECT fname, lname FROM people WHERE
id=curr_id;
BEGIN
for rec in curr_in loop
dbms_output.put_line('done');
dbms_output.put_line(rec.fname || ' ' || rec.lname);
end loop;
END;

I also removed the EXCEPTION section because it added nothing useful,
in fact if used in code where you were updating the database it would
constitute a bug!
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by robert | last post: by
1 post views Thread by teddysnips | last post: by
1 post views Thread by traceable1 | last post: by
2 posts views Thread by satishchandra999 | last post: by
2 posts views Thread by Chris Zopers | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
2 posts views Thread by Jeremy Moncho | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.