How to right a schuduled trigger that runs a stored procedure (in a package)

Posted on 2007-08-08
Last Modified: 2013-12-19

Wonder if you can help me?

Im new to oracle, so please bear with me. I need to create a trigger to run at 02:00am and it should run a stored procedure (which is located in a package)

Can someone please give me the code to do this?
Question by:mousemat24
    LVL 29

    Expert Comment


    You cannot "run" a trigger, a trigger is a special procedure that executes each time the corresponding DML statement (INSERT/UPDATE/DELETE) is applied to the table. (Read about triggers in the manual).

    You can write a procedure and schedule it to execute at any time using either the DBMS_JOBS package or the DBMS_SCHEDULER package.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    Mike is correct, you need to use DBMS_JOB.  I'm not sure when the new DBMS_SCHEDULER appeared but I think it wasn't until 10g.

    I suggest you take a look at the docs:
    LVL 34

    Accepted Solution

    Since you have Oracle9, you cannot use DBMS_SCHEDULER - that is new in Oracle10.  You have to use DBMS_JOB to run a stored procedure on a particular schedule.  One of the disadvantages of DBMS_JOB though is the tendency for the start time of a job to "creep" later, if you use a simple interval like: sysdate +1 (which Oracle understands to mean: "current time plus one day" or "tomorrow at this time").  The work-around for that is to use an interval that always converts to the same time of the day, like: "trunc(sysdate) + 26/24" instead of the simpler "sysdate +1".

    The explanation of: "trunc(sysdate) + 26/24" is:
    "trunc(sysdate)" - This means: midnight today (not tonight) or the first second of the current day
    "+ 26/24" - Oracle supports date arithmetic, that is: adding or subtracting numbers to or from a "date" value or variable. The number is assumed to be a day, so "sysdate +1" means: "this same time tomorrow", or "sysdate - 7" means: "this time one week ago".  You can use fractions instead of whole numbers in date arithmetic to get Oracle to add hours (or minutes or seconds) to a particular date value or variable, so "26/24" mean: one whole day (24 hours) plus two hours.  When that is added to "trunc(sydate)" the result is a consistent time of 2:00am on the following day.

    (And yes, you cannot schedule a trigger to be executed, you can schedule a procedure to be executed.)

    Here is the script I used in SQL*Plus to set up a scheduled job using DBMS_JOB to run a procedure I wrote named: "analyze_tables".  (Note the semi-colon at the end of the procedure name in the example - that is important.)  This should also work in TOAD, or any other GUI tool that can execute Oracle SQL and/or PL\SQL statements.  If you use SQL*Plus, run this statement first:
    set serveroutput on;

    -- The parameters for "dbms_job.submit" are:
    --   1. job number; 2. the procedure to run;  3. the first time to run it; 4. the interval
    --  In the following example "trunc(sysdate + 1) + 1/48" means it will run for the first
    --   time the day after this statement is issued at 12:30 am (1/48 of a day).
    --  Rather than a fixed interval like "sysdate +1" which can cause the next run at an
    --   unexpected time if a run is ever delayed for any reason (or run manually during the day)
    --   the interval of "trunc(sysdate) + 49/48" guarantees that each successive run will be
    --   scheduled for 12:30am on the following day, regardless of the current start time.
      job_no number;
      dbms_job.submit(job_no,'analyze_tables;',trunc(sysdate + 1) + 1/48,'trunc(sysdate) + 49/48');
    LVL 8

    Assisted Solution

    Execute this.  Be sure to include your procedure name:
    The 999 is the number of your job.  You can put the number you like.

    EXEC DBMS_JOB.ISUBMIT(999,'begin <your procedure name>;end;',TRUNC(SYSDATE)+2/24, 'trunc(sysdate+1)+2/24');

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    761 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

    7 Experts available now in Live!

    Get 1:1 Help Now