DBMS_JOB

[tansdot]

How do I schedule a stored Procedure to run every day at 12 midnight.

Thanks

[grjohnson]

variable jobno number;

begin
dbms_job.submit(:jobno, 'package_name;',
trunc(sysdate) + 1 + 0/24, 'trunc(sysdate) + 1 + 0/24');
COMMIT;
end;
/

spool off


(Package name is a package owned by the owner of the job.

[tansdot]

Hi grjohnson

Is there any way I can put this statement in a script and when my user runs it the script asks him at run time the time he wants to schedule it.Can I do the following:

dbms_job.submit(:jobno, 'package_name;', &a,&b)

But then how would I interpret his values as following if he enters 12:00am.

dbms_job.submit(:jobno, 'package_name;',
trunc(sysdate) + 1 + 0/24, 'trunc(sysdate) + 1 + 0/24');

How can I interpret the trunc(sysdate) + 1 + 0/24 AND
'trunc(sysdate) + 1 + 0/24' in the above stmt.

I hope I havent caused any confusion.

Thanks

[grjohnson]

tansdot,

Hi, I'm sure you could implement this functionality, I haven't really got the time to look into it for you. It'd be a process of trial and error to get it working.

Also, there are added element to you changes. You'd be changing the interval, and also the start time of the procedure. You may even have to remove (execute dbms_job.remove(jobid); the job and start another.

Sorry I can't be of more help.

Cheers,