Is it possible to turn on/off a package/procedure?
My client would like the procedure to be executed only during business hours?
ie 9am-5pm, based on the server time.
I thought about perhaps dynamically altering the table to read only mode or
a trigger based on the table based on the system time.
Has anybody done something similar to this?
You would have to code the logic into the procedure.
Is the procedure time based (execute every 5 mins between 9-5), or event based (execute when someone inserts into this table, but only between 9-5)?
If it's time based, you might be able to get away with using dbms_job if it's as configurable as unix cron. I'm not too familiar with it so I can't say for sure. If it's not you probably have to put the logic somewhere, either use cron to execute the procedure, or put the logic to check the time in the procedure itself.
If it's event based you probably just want a trigger that has logic to check the time.
The procedure (basically inserts into table) could be executed any time between 9am and 5pm, however after this time,
no inserts are allowed...
Then you will just have to put the logic in your procedure to check the time before inserting.
O.K one of the ways to get going is grant the execute on the procedure to the user at 9am then revoke the execute permission at 5pm. I hope that you could do it through dbms_jobs
I think you can do it with triggers, but if the numbers of tables is very high you probably better implement into logic or as sambavan suggested.
if to_number(to_char(sysdate, 'HH24')) between 9 and 16
at the beginning of your procedure you prevent the rest of the procedure to be executed between 9 and 5. You can also use raise_application_error instead of return to raise an error.
Jobs with DBA_JOBS are never executed exactly at the given time, sometimes much later.
Click Here to Expand Forum to Full Width