1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Execute stored procedure after interval

Discussion in 'SQL PL/SQL' started by christian.delbianco, Aug 14, 2015.

  1. christian.delbianco

    christian.delbianco Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    swiss
    Hello. I am new in ORACLE - PL/SQL.
    What I need to do is run in a separate call or sql statement (in a separate thread) a task, job or what ever that after an amount of seconds call a stored procedure.

    Also, this thread must be reset: this means that if I call again it, the timer must be restarted from zero, starting a new interval counting.

    Is there any oracle way to accomplish that ?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    you can describe the task in more detail ?


    in the oraсle of object a thread - no, therefore it is impossible to create a separate thread( suspend,resume and etc.)

    1) if it is necessary to launch simply some procedure through time interval - it is possible to use dbms_scheduler

    2) if it is necessary to realize interaction of processes, there can be some versions of decisions

    Additional links:
    Examples of Using the Scheduler

    Scheduler (DBMS_SCHEDULER) in Oracle Database
     
    Last edited: Aug 14, 2015
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I'm with Sergey -- it's not really clear what you are trying to accomplish. At least part of what you are requesting seems to call for the use of the DBMS_LOCK.SLEEP procedure. This causes a PL/SQL procedure to pause for a specified number of seconds before continuing. For example:

    Code (Text):

    BEGIN
      DBMS_OUTPUT.PUT_LINE('The time is now: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
      DBMS_LOCK.SLEEP(7);
      DBMS_OUTPUT.PUT_LINE('The time is now: ' || TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

    The time is now: 15:20:59
    The time is now: 15:21:06