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).