I think you would be better off to write a stored procedure in PL/SQL, and use another mechanism to start the procedure based on system time. The DBMS_JOB package in PL/SQL is designed to do what you appear to want. It allows a user to schedule a job to run at a specific time, or specific intervals. A job consists of PL/SQL code (ie, a stored procedure). The user also indicates via job parameter how often the code should be run.
The following example shows how to schedule a job to execute at midnight every night:
DBMS_JOB.SUBMIT (:v_jobnum, 'my_procedure;', trunc(sysdate) + 1, 'sysdate + 1');
The 'trunc(sysdate) + 1' se>ts the time back to the current day, midnight and informs the scheduler to start the job at the following midnight. The 'sysdate + 1' sets the interval to 1 day. This job would run the first time at midnight, then every midnight afterwards.
To schedule the job at 8 a.m., specify the next_date parameter as trunc(sysdate) + 1 + 8/24.
You may want to examine the following Notes on Oracle MetaLink:
Note 61730.1 Using the DBMS_JOB Package details how to use this package when scheduling a job.
Note 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals details examples of using the DBMS_JOB.SUBMIT.
This was first published in January 2004