Re: 'SELECT nexval FROM sequence' in a PL/SQL loop: use a cursor ?

From: mcstock <mcstock_at_enquery.com>
Date: Thu, 23 Oct 2003 17:37:20 -0400
Message-ID: <Be6dneMKc-Sx1wWiRVn-sQ@comcast.com>

it really should never be necessary to select a sequence's value from dual (except, unfortunately, in a before insert row trigger)

the NEXTVAL is likely being used somewhere in the loop in an INSERT (or UPDATE) statement, so simply reference the sequence in the DML statement:

    insert into mytable (pk, other, stuff ...     values( mysequence.nextval, v_other, v_stuff ...

if the value of NEXTVAL needs to be reused (perhaps in another insert as an FK ), you can do one of two things:

1. use the mysequence.CURRVAL in the subsequence DML statements
2. use the RETURNING clause in the original DML statement to save the value in a variable, i,e.:

    insert into mytable (pk, other, stuff ...     values( mysequence.nextval, v_other, v_stuff ...     returning pk into v_pk;

---

Mark C. Stock
www.enquery.com
(888) 512-2048

"Spendius" <spendius_at_muchomail.com> wrote in message news:aba30b75.0310230603.50ef719a_at_posting.google.com...
> Hi,
> I found the following in the code of one of the developers
> here:
> > FOR r_get_tael IN c_get_tael LOOP
> > OPEN c_get_tael_seq;
> > FETCH c_get_tael_seq INTO r_get_tael_seq;
> > CLOSE c_get_tael_seq;
> > BEGIN
> > ...
> which loops 140 000 times. The c_get_tael_seq cursor
> only performs a simple
> > SELECT <seq.>NEXTVAL FROM DUAL;
> so I just would like to know whether the fact he uses
> a cursor (that gets opened and closed 140 000 times) will
> have a bad impact or not compared with the use of the mere
> SQL statement it refers to, i.e. instead of using:
> > FOR r_get_tael IN c_get_tael LOOP
> > SELECT <seq.>NEXTVAL into <variable> FROM DUAL;
> > BEGIN
> > ...
> ??

>

> What do you think ?
> Thanks.
> Regards,
> Spendius
Received on Thu Oct 23 2003 - 16:37:20 CDT