Home » SQL & PL/SQL » SQL & PL/SQL » dbms_jobs
dbms_jobs [message #244315] Tue, 12 June 2007 05:39 Go to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

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 Go to previous message
amit_nanote
Messages: 56
Registered: July 2005
Location: Indore
Member

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
Previous Topic: Date Functions
Next Topic: Hints Doc require
Goto Forum:
  


Current Time: Fri Dec 09 21:03:01 CST 2016

Total time taken to generate the page: 0.10516 seconds