Re: Q: How to rewind cursor in PL/SQL?

Thanks!
The Problem is solved.

On Wed, 10 Dec 1997 21:06:08 GMT,
 tkyte_at_us.oracle.com (Thomas Kyte) wrote:

> On 10 Dec 1997 15:28:33 GMT, 101.41593_at_germanynet.de (Alexandre Ziablitsev)
> wrote:
>
> >Hi!
> >
> >I'm writing procedure in PL/SQL for ORACLE 8.
> >I need to run the same SQL statment in a loop, in witch I reopen the
> >cursor everytime I get to the head of loop. The cursor parameters
> >doesn't change.
> >
> >Is it possible to rewind opened cursor to save the time and don't
> >open the cursor for reading the database each time getting the head
> >of the loop after closing this cursor at the loop bottom ?
> >
> >thanks.
> >
> > Alex Ziablitsev
> >
> >
>
> You can't rewind a cursor and the overhead of closing/reopening is very small in
> pl/sql (especially if close_cached_open_cursors is set to FALSE -- this is an
> init.ora parameter).
>
> If there is a 'reasonable' number of records, you can put them into a PL/SQL
> table and just read the table as an array. For example:
>
> create or replace type empType as object
> ( empno number,
> ename varchar2(10),
> job varchar2(9),
> hiredate date,
> sal number,
> comm number
> );
> /
>
> create or replace type empArrayType as table of empType;
> /
>
> declare
> x empArrayType;
> begin
> select CAST( multiset( select empno, ename, job, hiredate, sal, comm
> from emp ) AS empArrayType )
> INTO x from dual;
>
> dbms_output.put_line( 'There were ' || x.COUNT || ' rows fetched' );
> for i in 1 .. x.COUNT loop
> dbms_output.put_line( x(i).ename || ' ' || x(i).hiredate );
> end loop;
> end;
> /
>
>
> The select ... into x from dual reads the query into the PL/SQL table for you in
> one statement and then you can just loop over it again and again....
>
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Fri Dec 12 1997 - 00:00:00 CST