dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DBMS_JOB

  1. #1
    Join Date
    Apr 2001
    Posts
    112
    How do I schedule a stored Procedure to run every day at 12 midnight.

    Thanks

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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.
    OCP 8i, 9i DBA
    Brisbane Australia

  3. #3
    Join Date
    Apr 2001
    Posts
    112
    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

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width