Re: Update 100 rows and sleep for 60 sec

From: Balwanth B <balwanthdba_at_gmail.com>
Date: Tue, 4 Apr 2017 13:34:40 -0400
Message-ID: <CAL72EnD3xubXOOEGAhOns4Ko14L60vMYyfL1Fic+8iOKAZHY4g_at_mail.gmail.com>



Powell

Thanks for noticing that, I changed it now tested and it worked for me now

from

EXIT WHEN REC_CUR%NOTFOUND; ---- rec_cur is cursor

to

EXIT WHEN ROWID_TAB.COUNT = 0; making the collection empty

Thanks,

Balwanth

On Mon, Apr 3, 2017 at 2:07 PM, Powell, Mark <mark.powell2_at_dxc.com> wrote:

> Instead of using a cursor variable, why not exist when the collection is
> empty?
>
>
> Mark Powell
> DXC Technology
> (313) 592-5148
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Balwanth B <balwanthdba_at_gmail.com>
> *Sent:* Monday, April 3, 2017 1:53:36 PM
> *To:* Tim Gorman
> *Cc:* Michael.J.Tefft_at_snapon.com; mwf_at_rsiz.com; rogel_at_web.de;
> Binh.Le_at_lfg.com; ORACLE-L
>
> *Subject:* Re: Update 100 rows and sleep for 60 sec
>
> 1) I have tried below for maximum of 20000 records and I did not get any
> errors atleast for now.
>
>
> declare
> CURSOR REC_CUR IS
> select id from yyyyyyy;
> 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 200;
> EXIT WHEN REC_CUR%NOTFOUND;
> FORALL I IN 1.. ROWID_TAB.COUNT
> update xxxxx set activated=1 where activated =0 and managed =1 and id=
> ROWID_TAB(I);
> COMMIT;
> dbms_lock.sleep(60);
> END LOOP;
> CLOSE REC_CUR;
> END;
>
> 2) 2) I was trying to use EXIT WHEN REC_CUR.count=0 but was
>
> 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
>
>
> 3) I made sure my data is multiple of 200 as per Steven Feuerstein's
> article
> <http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html>
>
>
> Thanks,
>
> Balwanth
>
>
> On Wed, Mar 29, 2017 at 12:40 PM, Tim Gorman <tim.evdbt_at_gmail.com> wrote:
>
>> I recall having a process like this a looonnnnngggg time ago. It was
>> intended to "poll" a table that was part of a pipeline of processes, and
>> perform some task whenever rows showed up.
>>
>> To get around the inevitable ORA-01555 errors, I defined an exception
>> named SNAPSHOT_TOO_OLD, then added an additional loop around the overall
>> logic to simply loop back around and close/re-open the cursor when that
>> exception was encountered.
>>
>>
>>
>>
>>
>> On 3/29/17 08:11, Tefft, Michael J wrote:
>>
>> As I read this, that cursor is going to stay open for a long time, with
>> commits going on along the way….
>>
>> Aren’t you setting yourself up for ORA-1555?
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freeli
>> sts.org <oracle-l-bounce_at_freelists.org>] *On Behalf Of *Mark W. Farnham
>> *Sent:* Wednesday, March 29, 2017 8:06 AM
>> *To:* rogel_at_web.de; Binh.Le_at_lfg.com
>> *Cc:* 'ORACLE-L' <oracle-l_at_freelists.org> <oracle-l_at_freelists.org>
>> *Subject:* 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_freeli
>> sts.org <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_freeli
>> sts.org <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
Received on Tue Apr 04 2017 - 19:34:40 CEST

Original text of this message