Re: Cursor, Fetch, and while...

From: Tony <andrewst_at_onetel.net.uk>
Date: 5 Dec 2003 03:42:45 -0800
Message-ID: <c0e3f26e.0312050342.19846cf1_at_posting.google.com>

Jeremy Moncho <jeremy.moncho_at_bluewin.ch> wrote in message news:<BBF542FD.14A4%jeremy.moncho_at_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! Received on Fri Dec 05 2003 - 12:42:45 CET