OraFAQ Forum: SQL & PL/SQL » how to create a job which runs at every 4 minutes

Home » SQL & PL/SQL » SQL & PL/SQL » how to create a job which runs at every 4 minutes

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to create a job which runs at every 4 minutes [message #20792]

Thu, 20 June 2002 09:19

ksr
Messages: 112
Registered: January 2002

Senior Member

Hi,

I want to create a job which will run for every 4 minutes.

Can anyone give me the code for the job creation for the above.

Any help is appreciated..

Report message to a moderator

Re: how to create a job which runs at every 4 minutes [message #20794 is a reply to message #20792]

Thu, 20 June 2002 09:43

Amit Chauhan
Messages: 74
Registered: July 1999

Member

Hi,
Use this as an example :

DECLARE
  run_freq VARCHAR2 (10) := '4'; -- In minutes
BEGIN
  BEGIN
    dbms_job.remove (100);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_job.isubmit (100, 'my_procedure_name;', sysdate + 5/86400, 'sysdate + '||run_freq||'/1440');
      RAISE;
  END;
  dbms_job.isubmit (100, 'pack_dbk.pr_execute_txn;', sysdate + 5/86400, 'sysdate + '||run_freq||'/1440');
  dbms_job.run (100);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line (SUBSTR('Error occured : '||sqlerrm, 1, 255));
END;
/

You would have to set initialization parameter job_queue_processes to a value greater than 0 to make it work.

Hope that helps.
Amit

Report message to a moderator

Re: how to create a job which runs at every 4 minutes [message #20802 is a reply to message #20794]

Fri, 21 June 2002 02:44

ksr
Messages: 112
Registered: January 2002

Senior Member

This job does not work, why any idea..

declare
TEST_JOB pls_integer :=10;
BEGIN
dbms_job.submit( job => TEST_JOB,
what => 'jobtest;',
next_date => sysdate,
interval => 'sysdate + (5/(24*60))');

--dbms_job.submit(:jb,'BEGIN YOUR_PROC;END;',SYSDATE,'SYSDATE+(4/(24*60))');
--COMMIT;
END;

end;

CREATE OR REPLACE PROCEDURE jobtest as
begin
update test1 set col1= col1 + 1;
commit;
end;

Report message to a moderator

Re: how to create a job which runs at every 4 minutes [message #20809 is a reply to message #20794]

Fri, 21 June 2002 10:00

Amit Chauhan
Messages: 74
Registered: July 1999

Member

Hi,
Check that you have initialization parameter
JOB_QUEUE_PROCESSES
set in init.ora file.
It should be greater than 0.
e.g.
JOB_QUEUE_PROCESSES=10

If not set, then set it and restart oracle.

Also, run it forcefully for the first time, just to be sure that its running :
SQL>dbms_job.run (1);

Hope that helps
Amit

Report message to a moderator

Previous Topic:	How can i retrieve data
Next Topic:	sum func. inside a cursor, with group by ????

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 11:27:45 CDT 2024