RE: For update - Where current of

From: Nirmal Kumar Muthu Kumaran <NIRMALK_at_qtel.com.qa>
Date: Sun, 30 Sep 2001 04:06:29 -0700
Message-ID: <F001.0039CCF1.20010930040517@fatcity.com>

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

Received on Sun Sep 30 2001 - 06:06:29 CDT