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

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 17 Nov 2003 10:38:07 -0000
Message-ID: <3fb8a50f$0$259$ed9e5944@reading.news.pipex.net>

"Jeff Smith" <jsmit234 at ford dot com> wrote in message news:boqm6p$nss1_at_eccws12.dearborn.ford.com...
> 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.

Not true. You don't even need a declare block (though I'll admit that earlier in this session dbms_output and sqlplus serveroutput had been enabled).

SQL> ed
Wrote file afiedt.buf

  1 begin
  2 for i in (select object_name from all_objects where rownum < 10) loop   3 dbms_output.put_line(i.object_name);   4 end loop;
  5* end;
SQL> /

/1005bd30_LnkdConstant
/10076b23_OraCustomDatumClosur
/10297c91_SAXAttrList
/103a2e73_DefaultEditorKitEndP
/1048734f_DefaultFolder
/10501902_BasicFileChooserUINe
/105072e7_HttpSessionBindingEv
/106ba0a5_ArrayEnumeration
/106faabc_BasicTreeUIKeyHandle

PL/SQL procedure successfully completed. Received on Mon Nov 17 2003 - 04:38:07 CST