| dbms_jobs [message #244315] |
Tue, 12 June 2007 05:39  |
|
|
Hi all,
I am executing a job every 15 minutes which have to delete the rows created in every 30 minutes.
========================================
Jobs Submitted as -
declare
job number;
begin
--job:=101;
--job created to execute for every minute.
sys.dbms_job.submit(job => job,
what => ' declare
i number;
begin
del_test_info_p;
end;',
next_date => sysdate,
interval => 'sysdate + 15/1440');
commit;
end;
/
==================================
Procedure that is running in Job -
CREATE OR REPLACE procedure del_test_info_p is
begin
delete from test where created_at+30/1440<=systimestamp;
commit;
end del_test_info_p;
/
=====================================
PROBLEM - Job is running in every 15 minutes as per user_jobs but rows are not deleteing from the test table whose created time is more then 30 minutes from systimestamp.
Checked the queries and run the job Manually using dbms_job.run.. gives correct output and deletes the row.
Please suggest where is the problem.
With Regards
Amit Nanote
|
|
|
|
| Re: dbms_jobs [message #244320 is a reply to message #244315] |
Tue, 12 June 2007 05:46  |
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
|
Senior Member |
|
|
Try to create the job like this
declare
job number;
begin
--job:=101;
--job created to execute for every minute.
sys.dbms_job.submit(job => job,
what => 'del_test_info_p;',
next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
interval => 'TRUNC(SYSDATE,''MI'')+15/(24/60));
commit;
end;
[Updated on: Tue, 12 June 2007 05:48] Report message to a moderator |
|
|
|
| Re: dbms_jobs [message #244342 is a reply to message #244320] |
Tue, 12 June 2007 07:22 |
|
|
Hello Sankya..
Thank you for reply.
I have submitted job as per your suggestion but its still not working.
Job I have submmitted.
===================================
declare
job number;
begin
--job:=101;
--job created to execute for every minute.
sys.dbms_job.submit(job => job,
what => 'del_test_info_p;',
next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
interval => 'sysdate + 15/1440');
commit;
end;
===================================
Do I went wrong any where?
Regards
Amit Nanote
[Updated on: Tue, 12 June 2007 09:14] Report message to a moderator |
|
|
|
| Re: dbms_jobs [message #244362 is a reply to message #244342] |
Tue, 12 June 2007 08:06 |
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
|
Senior Member |
|
|
Amit,
I think you are not passing any values in 'job' parameter.
Create a job sequence first then select it on this job parameter.
declare
job number;
begin
--job:=101;
--job created to execute for every minute.
select job_seq.nextval into job from dual;
sys.dbms_job.submit(job => job,
what => 'del_test_info_p;',
next_date => TRUNC(SYSDATE, 'HH')+ 1/24,
interval => 'TRUNC(SYSDATE,''MI'')+15/(24/60)');
commit;
end;
[Updated on: Tue, 12 June 2007 08:07] Report message to a moderator |
|
|
|
| Re: dbms_jobs [message #244380 is a reply to message #244362] |
Tue, 12 June 2007 09:13 |
|
|
That job get created with Id 82 on db,same problem occuring. Runing with dbms_job.run(82) executing it but scheduled job not executing that procedure.
|
|
|
|
| Re: dbms_jobs [message #244884 is a reply to message #244315] |
Thu, 14 June 2007 07:59 |
|
|
HI All,
I have found solution for this. Dont use Systimestamp in the DML's if using in a job (scheduled).
There is a statement in procedure del_test_info_p
delete from test where ((created_at+30/1440)<systimestamp);
Here usage of systimestamp restricting that statement to execute.
Create procedure as -
CREATE OR REPLACE procedure del_test_info_p is
tstamp timestamp;
begin
select systimestamp into tstamp from dual;
delete from test where ((created_at+30/1440)<tstamp);
commit;
end del_test_info_p;
Thank You.
Amit Nanote
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
