RE: RE: Update 100 rows and sleep for 60 sec

? He didn’t when-others null, he’s documenting the error.  

Since this looks like a pseudo daemon process that needs to keep running until it is manually stopped, that is probably the desired behavior.  

I usually suggest that this sort of pseudo daemon with a pause check some table-row-column value for a status that means “stop” or “continue” so you can control it by setting a database value rather than killing it and possibly log intentional starts and stops, but continuing after dumping the error seems reasonable to me.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rogel_at_web.de Sent: Wednesday, March 29, 2017 2:56 AM
To: Binh.Le_at_lfg.com
Cc: ORACLE-L
Subject: Aw: RE: Update 100 rows and sleep for 60 sec  

http://tkyte.blogspot.de/2012/05/pokemon-and-when-others.html   

Gesendet: Dienstag, 28. März 2017 um 21:15 Uhr Von: "Le, Binh T." <Binh.Le_at_lfg.com>
An: "balwanthdba_at_gmail.com" <balwanthdba_at_gmail.com>, "Powell, Mark" <mark.powell2_at_hpe.com>, "Upendra nerilla" <nupendra_at_hotmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Betreff: RE: Update 100 rows and sleep for 60 sec

declare

    cursor rec_cur is

    select serialnumber

      from hdm_mon.temp_cdserial;

    n_count integer := 1;

begin

    for c_val in rec_cur loop

        update device set activated = 1

          where activated = 0

            and managed = 1

            and id = n_count;

        if mod(n_count,100) = 0 then

            commit;

            dbms_lock.sleep(30);

        end if;

      n_count := n_count + 1;

    end loop;

    commit;

exception

    when others then

        dbms_output.put_line('Error '|| SQLERRM);

end;  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Balwanth B Sent: Tuesday, March 28, 2017 2:12 PM
To: Powell, Mark <mark.powell2_at_hpe.com>; Upendra nerilla <nupendra_at_hotmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Subject: Re: Update 100 rows and sleep for 60 sec  

getting below error  

ERROR at line 10:

ORA-06550: line 10, column 19:

PLS-00225: subprogram or cursor 'REC_CUR' reference is out of scope

ORA-06550: line 10, column 1:

PL/SQL: Statement ignored    

On Tue, Mar 28, 2017 at 1:04 PM, Balwanth B <balwanthdba_at_gmail.com> wrote:

I have modified something like below.. application operation takes place with that update device gets activated so i need to give some time gap for that.  

declare

CURSOR REC_CUR IS select serialnumber from hdm_mon.TEMP_CDSERIAL

TYPE ROWID_T IS TABLE OF VARCHAR2(50); ROWID_TAB ROWID_T; BEGIN OPEN REC_CUR; LOOP FETCH REC_CUR BULK COLLECT INTO ROWID_TAB LIMIT 100; EXIT WHEN REC_CUR.COUNT=0; FORALL I IN 1.. ROWID_TAB.COUNT update device set activated=1 where activated =0 and managed =1 and id= ROWID_TAB(I);

COMMIT; dbms_lock.sleep(30);

END LOOP; CLOSE REC_CUR; commit;

END;     Does this look good?  

On Tue, Mar 28, 2017 at 12:59 PM, Powell, Mark <mark.powell2_at_hpe.com> wrote:

See DBMS_LOCK.SLEEP(n)  

where N is how many seconds you want the process to sleep. Though what is the point of sleeping.

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Balwanth B <balwanthdba_at_gmail.com> Sent: Tuesday, March 28, 2017 12:16:07 PM To: ORACLE-L
Subject: Update 100 rows and sleep for 60 sec  

I am looking for script which will update    

Update 100 rows

commit

sleep

then next update 100 rows

follow the same pattern  

I have something like below but how do I make sure it sleeps for mentioned time and does the next 100 rows.  

declare

CURSOR REC_CUR IS select statement;

TYPE ROWID_T IS TABLE OF VARCHAR2(50); ROWID_TAB ROWID_T; BEGIN OPEN REC_CUR; LOOP FETCH REC_CUR BULK COLLECT INTO ROWID_TAB LIMIT 5000; EXIT WHEN REC_CUR%NOTFOUND; FORALL I IN 1.. ROWID_TAB.COUNT update statement

COMMIT; END LOOP; CLOSE REC_CUR; END;         Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

• http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l