Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Scheduler: job schedules itself based on circumstances that are not known until the job runs.

MWRA EnQualMar 20 2024

I am trying to create a job that starts on the 15th of a month and runs a stored procedure that checks to see if all data for the previous month are ready to report. The procedure has one argument: the 1st date of the month being checked. The job has one anydata argument: the date of 1st of the next month to be reviewed, which is passed to the stored procedure so it knows which month's data to check.

If the data are not ready, the procedure would modify its own next start date so that it runs again at 3 AM the next business day (I have a separate function that takes a date as its argument and returns the next business day, accounting for holidays.) Next business day checking should continue until all data are ready for the month being checked.

If the data are ready for the month being checked, the same stored procedure would start an ETL process for that month and send a notification email to a designated reviewer that the data are ready for review and lastly, change both the job's next run date to the 3 AM on the 15th of the second month after the month being checked and also thejob arguement to the 1st of the next month to be checked.

My question. Can a stored procedure modify attributes of a the job that is running the procedure without raising ‘ORA-27478: job "job_name" is running.’ (that is the exception I am hitting.) If so, what are the steps that need to be taken? If not, how else might this workflow be accomplished? Put another way, how can a job schedule itself based on circumstances that are not known until the job runs.

Thank you.

Doug

This post has been answered by GregV on Mar 20 2024
Jump to Answer
Comments
Post Details
Added on Mar 20 2024
4 comments
75 views