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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Balwanth B
Sent: Tuesday, March 28, 2017 2:12 PM
To: Powell, Mark <mark.powell2@xxxxxxx>; Upendra nerilla <nupendra@xxxxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:balwanthdba@xxxxxxxxx>> 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@xxxxxxx<mailto:mark.powell2@xxxxxxx>> 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@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
<oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>> on behalf
of Balwanth B <balwanthdba@xxxxxxxxx<mailto:balwanthdba@xxxxxxxxx>>
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.**
