Cursor, Fetch, and while...

[Jeremy Moncho]

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_fnam e || ' ' || curr_lname);
end loop;

CLOSE curr_in;

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

/

[mcstock]

Re: Cursor, Fetch, and while...

your WHILE loop never executes

-- mcs

"Jeremy Moncho" <jeremy.moncho@ bluewin.chwrote in message
news:BBF542FD.1 4A4%jeremy.monc ho@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_fnam e || ' ' || curr_lname);
| end loop;
|
| CLOSE curr_in;
|
| EXCEPTION
| WHEN OTHERS THEN
| dbms_output.put _line(sqlcode || ' ' || sqlerrm);
| END;
|
| /
|
|

[Tony]

Re: Cursor, Fetch, and while...

Jeremy Moncho <jeremy.moncho@ bluewin.chwrote in message news:<BBF542FD. 14A4%jeremy.mon cho@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_fnam e || ' ' || 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!