Re: For update - Where current of

There are no docs or sites that I am aware of other than the standard Oracle docs.

Try Steven Feuerstein's books and Tom Kyte's book as well. asktom.oracle.com may have something.

As for the 'select ... for update part', you did notice didn't you that 'where current of' only requires locking a single row at a time?

Jared

On Sunday 30 September 2001 05:05, Nirmal Kumar Muthu Kumaran wrote:
> Hi jarad,
>
> Thanks. In SQLPLUS, we can lock the records using the select ... update
> of.... The same thing i can do it pl/sql
> using cursor ... select... update of and <dml>... where current of
> <cur_name>. This was i understood before regarding
> the same.
>
> Is i'm rt or not... Can you give more breif on this... i would be more
> thankful if u redirected me for some sites/docs
> to refer...
>
> Regards,
> Nirmal
>
> > -----Original Message-----
> > From: Jared Still [SMTP:jkstill_at_cybcon.com]
> > Sent: Sunday, September 30, 2001 7:55 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: For update - Where current of
> >
> > On Saturday 29 September 2001 07:50, Nirmal Kumar Muthu Kumaran wrote:
> > > Hi gurus
> > >
> > > Can any one give the usage of 'for update' clause, while declaring a
> > > cursor in Pl/sql?
> >
> > 'Where current of' can be very useful.
> >
> > You can for instance use it to avoid 'select for update'
> > on a large number of rows. You can use it to commit in
> > a loop while avoiding the dreaded 'fetch across commit' error.
> >
> > Here's an example from some real code.
> >
> > Jared
> >
> >
> > declare
> >
> > cursor cUpdatePersons( login_id_in persons.login_id%type )
> > is
> > select *
> > from persons
> > where login_id = login_id_in
> > for update;
> >
> > rPersons persons%rowtype;
> > rEmptabActive emptab_tmp%rowtype;
> >
> > cursor cEmpTabInactive
> > is
> > select distinct login_id
> > from emptab_tmp
> > where status = 'I';
> >
> > cursor cEmpTabActive( login_id_in persons.login_id%type )
> > is
> > select *
> > from emptab_tmp
> > where login_id = login_id_in
> > and status = 'A'
> > and effective_end_date > sysdate;
> >
> > begin
> >
> > for irec in cEmpTabInactive
> > loop
> >
> > open cEmpTabActive( irec.login_id );
> > fetch cEmpTabActive into rEmptabActive;
> >
> > if not cEmpTabActive%found then
> >
> > open cUpdatePersons( irec.login_id );
> > fetch cUpdatePersons into rPersons;
> >
> > if cUpdatePersons%found then
> >
> > update persons
> > set status = 'I'
> > where current of cUpdatePersons;
> >
> > end if;
> >
> > close cUpdatePersons;
> >
> > end if;
> >
> > close cEmpTabActive;
> >
> >
> > end loop;
> >
> > end;
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).

Content-Type: text/html; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 
----------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).