Dynamically running procedures?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Dynamically running procedures?

  1. #1
    Join Date
    Aug 2000
    Location
    Toronto, Ontario Canada
    Posts
    52
    Cheers,

    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?

    Thanks,
    Steve

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You would have to code the logic into the procedure.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2000
    Posts
    417
    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.

  4. #4
    Join Date
    Aug 2000
    Location
    Toronto, Ontario Canada
    Posts
    52
    The procedure (basically inserts into table) could be executed any time between 9am and 5pm, however after this time,
    no inserts are allowed...


  5. #5
    Join Date
    Dec 2000
    Posts
    43
    Then you will just have to put the logic in your procedure to check the time before inserting.

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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


    Sam

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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.

  8. #8
    Join Date
    Jul 2000
    Posts
    296
    With
    if to_number(to_char(sysdate, 'HH24')) between 9 and 16
    then
    return;
    end if;
    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.

  9. #9
    Join Date
    Aug 2000
    Location
    Toronto, Ontario Canada
    Posts
    52
    thanks. akkerend

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