From: 101.41593@germanynet.de (Alexandre Ziablitsev)
Subject: Re: Q: How to rewind cursor in PL/SQL?
Date: 1997/12/12
Message-ID: <yx24233ny@germanynet.de>#1/1
References: <yx24233ny@germanynet.de> <349102f1.13498740@inet16> <66mcf1$pte$1@news.seicom.net>
Organization: germany.net Onlinesysteme
Newsgroups: comp.databases.oracle.server



Thanks!
The Problem is solved.

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

> On 10 Dec 1997 15:28:33 GMT, 101.41593@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@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.