schedule an update query as job in oracle 9i

Posted on 2006-05-11
Last Modified: 2008-01-09
I have an update (UPDATE SUBSCRIBERS TODATE=FROMDATE WHERE FROMDATE+3 < SYSDATE AND TODATE < FROMDATE) query ank I want to schedule this as a job that running every day..
Question by:gla
    1 Comment
    LVL 6

    Accepted Solution

    If you create a strored procedure to run your script you can use DBMS_JOB.SUBMIT to schedule it on the database.
    See info below.

    SUBMIT Procedure
    This procedure submits a new job. It chooses the job from the sequence sys.jobseq.

       job       OUT BINARY_INTEGER,
       what      IN  VARCHAR2,
       next_date IN  DATE DEFAULT sysdate,
       interval  IN  VARCHAR2 DEFAULT 'null',
       no_parse  IN  BOOLEAN DEFAULT FALSE,
       instance  IN  BINARY_INTEGER DEFAULT any_instance,
       force     IN  BOOLEAN DEFAULT FALSE);

    Table 41-9 SUBMIT Procedure Parameters
    Parameter Description
     Number of the job being run.
     PL/SQL procedure to run.
     Next date when the job will be run.
     Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.
     A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job. If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.

    For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.
     When a job is submitted, specifies which instance can run the job.
     If this is TRUE, then any positive integer is acceptable as the job instance. If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.

    Usage Notes
    You must issue a COMMIT statement immediately after the statement.
    The parameters instance and force are added for job queue affinity. Job queue affinity gives users the ability to indicate whether a particular instance or any instance can run a submitted job.
    This submits a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours:

    VARIABLE jobno number;
          ''DQUON'', ''ACCOUNTS'',
          ''ESTIMATE'', NULL, 50);'
          SYSDATE, 'SYSDATE + 1');
    Statement processed.
    print jobno


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Suggested Solutions

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now