• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

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


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?
2 Solutions

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.
slightwv (䄆 Netminder) Commented:
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:
Mark GeerlingsDatabase AdministratorCommented:
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');
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');
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now