Job Scheduler

[kirc]

Hi,

I am using Oracle 9i. I want to submit a job scheduler for automatic updation of dates in one of the columns of a table. This is the PL/SQL Procedure that I have written

set serveroutput on
CREATE OR REPLACE PROCEDURE IRBMeetingScheduler AS

--

meetingDate VARCHAR2(20);

todaysDate VARCHAR2(20);

--

BEGIN



select to_char(SYSDATE,'DD-MON-YY') into todaysDate from dual;

select to_char(NEXT_MEETING_DATE,'DD-MON-YY') into meetingDate from t1 where to_char(NEXT_MEETING_DATE,'DD-MON-YY')=TO_CHAR(SYSDATE,'DD-MON-YY') and FREQUENCY='monthly';

dbms_output.put_line('meetingDate = ' || meetingDate);
dbms_output.put_line('todaysDate = ' || todaysDate);

IF meetingDate>=todaysDate THEN

dbms_output.put_line('meetingDate = ' || meetingDate);

UPDATE t1 SET NEXT_MEETING_DATE = ADD_MONTHS(SYSDATE,1)

WHERE FREQUENCY='monthly' AND to_char(NEXT_MEETING_DATE,'DD-MON-YY') = to_char(SYSDATE,'DD-MON-YY');
COMMIT;

ELSE
dbms_output.put_line('In else block');

UPDATE t1 SET FREQUENCY='monthly' WHERE FREQUENCY='monthly';

END IF;

--

END IRBMeetingScheduler;

I want to test if the Job Scheduler executes the above Procedure every 2 minutes. So this is the code that I have written for that:


DECLARE
jobNo Number;

BEGIN
dbms_output.put_line('jobNo = ' || jobNo);
DBMS_JOB.submit(
jobNo,
'IRBMeetingScheduler;',
TRUNC(SYSDATE,'MI'),
'TRUNC(SYSDATE,''MI'')+2/(24*60)');

COMMIT;

END;

I have set job_queue_processes = 20 and job_queue_interval = 20 in init.ora.

The procedure doesn't give any compliation errors and so does the Job when submitted. The problem is that, the job will never get executed on its own after submitting. If i do dbms_job.run(), the job will get executed(i.e., only manually). I am giving commit after I submit the job. In user_jobs, the next_sec column shows the time of submission....!!!What could be wrong.


Anybody please please help me out... It's very very important for me to make this work....

Please help as soon as possible.

[TimHall]

Hi.

I think you need to follow this thread in the DBA forum. It sounds like a similar problem.

http://www.dbasupport.com/forums/showthread.php?t=50670

Cheers

Tim...

[kirc]

Hi Tim,
Thanks for replying... I had a look at the thread which you sent me. In that the problem was solved by restarting the Oracle instance. I have restarted the Oracle instance and tried. But still no use. Can you just check whether my procedure is proper? It is not giving any compilation error though...

Waiting for your reply

Kiran C V

[TimHall]

Hi.

I think you are missing the point. From what you describe the job is not actually being started. You say it works fine when you call it manually, so you know the procedure works fine. I think you need to progress the issue with the job not running.

Have you worked through the Meatlink notes referenced in the other thread?

If you still feel the need to check whether the procedure is at fault, simply comment out the code in the procedure, compile it and see if the job runs.

Cheers

Tim...