Scheduled Job or Stored Procedure [message #165048] |
Tue, 28 March 2006 03:14 |
kianwatt
Messages: 1 Registered: March 2006
|
Junior Member |
|
|
Hi
I have a simple tasks. I need to perform the following sql statement on 01-Apr-2006 @ 12am.
Update myTable set nextvalue=1
How do i go about doing that? I am using Oracle 9.1.0.6 on Windows 2000 . Thanks
|
|
|
|
Re: Scheduled Job or Stored Procedure [message #165112 is a reply to message #165048] |
Tue, 28 March 2006 06:29 |
kmsalih
Messages: 197 Registered: March 2006
|
Senior Member |
|
|
Hi,
Using DBMS_JOB Package you can Schedule a job.
See the Example which worked successfully.
-- create a table for testing
create table salih.a
(
a number,
b date default sysdate
);
--create a procedure for inserting
create or replace procedure sample_ins_job
as
begin
insert into salih.a(a) values(1);
commit;
end;
/
-- Submission of Job (Here the date and time is 28th March 2006 6 PM
variable j number;
begin
dbms_job.submit(job=>:j,what=>'begin sample_ins_job; end;',next_date=>to_date('28-MAR-2006 18:00:00','DD-MON-YYYY HH24:MI:SS'));
commit;
end;
/
--Viewing the changes
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select * from salih.a;
Regards
SALIH
|
|
|