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

Wow. What are you thinking? You can't use FOR LOOP in this way with a ref cursor. You must explicity fetch into declare variables.

declare...
...
  vjob_id jobs.job_id%type;
..
begin

   open lresult for select * from jobs;
   loop
  fetch lresult into vjob_id;
 exit when lresults%notfound;
 dbms_output.put_line(vjob_id);

end loop;

end;

if you want to iterate through a cursor, not a ref cursor, using FOR then the cursor must be declared explictly in your declare block.

"Paul Tomlinson" <rubberducky703_at_hotmail.com> wrote in message news:boqho1$1hfdoo$1_at_ID-116287.news.uni-berlin.de...
> All,
>
> I have the following autonomous block. What I want to do is to iterate
> through each record in the result-set and simply output the job_id. I
have
> simplified the example greatly for your viewing:
>
>
> DECLARE
> TYPE recordset IS REF CURSOR;
> lresult recordset;
> BEGIN
> open lresult for select * from jobs;
> close lresult;
> for lrec in lresult
> loop
> dbms_output.put_line(lrec.job_id);
> end loop;
> END;
>
> I get the error: PLS-000221: 'LRESULT' is not a procedure or is undefined
> ORA-06550.
>
> Any ideas how I can get around this?
> PT
>
>
> P.S. Does anyone know of any good PL/SQL newsgroups/news servers?
>
>
Received on Tue Nov 11 2003 - 06:54:49 CST