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.

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:

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_
> freelists.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_
> freelists.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_
> freelists.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