Oracle Scheduled Job
I have a table with a Status field, a Start Date and a End Date field.
I need the Status field to be updated to "Active" as soon as the Start Date is equal to today's date. Also, I want the Status to be updated to "InActive" as soon as the End Date is less than today's date.
The first thing that came to my mind was to schedule a job to run at 12am daily to update the Status field in the records of the table.
However, I am not sure how to do this. Also, if it is the best approach.
I'd appreciate if anyone could tell me how to go about this.
Don't do that.
If you are on 11g make Status a virtual column and derive the value as you wish.
If you are not on 11g expose your table thru a view and show Status on the view as you wish.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width