Re: Curious problem iterating through a ref cursor (recordset)

From: <jstern_at_fedex.com>
Date: 11 Nov 2003 07:34:33 -0600
Message-ID: <3fb0e569$1@ham>

Paul,

You need a fetch statement and a variable(s) defined to fetch the row into... Then use a loop (checking with a %NOTFOUND) to step through the result set.

Example.

Table jobs ( job_id varchar2(10));

DECLARE
    TYPE recordset IS REF CURSOR;
    lresult recordset;

tjobid jobs.job_id%TYPE;

BEGIN
    open lresult for select * from jobs;

      loop
                fetch lresult into tjobid; 
                exit when lresult%NOTFOUND;
 
                dbms_output.put_line (tjobid);
        end loop;

    close lresult;
END; This example will fetch the resultset row by row into the variable declared and display it using the dbms_output.put_line.. Please note that you might exhaust the dbms_output buffer if you have a large recordset.

Jim Stern Received on Tue Nov 11 2003 - 07:34:33 CST