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

Posted on 2007-08-08
Medium Priority
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

ID: 19656322

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 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 19656545
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 35

Accepted Solution

Mark Geerlings earned 1800 total points
ID: 19656777
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');

Assisted Solution

LindaC earned 200 total points
ID: 19664710
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');

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

809 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