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

CLOSE curr_in;

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

/

[mcstock]

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;
|
| /
|
|

[Tony]

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!