Re: Update 100 rows and sleep for 60 sec

Great..Thanks all for your replies!!! Its working for me now

On Tue, Mar 28, 2017 at 3:15 PM, Le, Binh T. <Binh.Le_at_lfg.com> wrote:

> 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