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