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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/10
Message-ID: <349102f1.13498740@inet16>#1/1

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 Wed Dec 10 1997 - 00:00:00 CST