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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
