Oracle: how to run a stored procedure "later"

Question

We have a system that allows users interfacing data into the database to set up various rules that are used to alter data before it is merged in to the main table. For example, an order might have a rule that sets up what delivery company to use based on a customer's address.

This is originally intended to operate only on the data being loaded in, so it's limited to functions that you can call from a select statement. An important point to note is that the data is not in the destination table yet.

Now, I have a project that requires an update to another table (fine - I can use an autonomous_transaction pragma for that). However, there are some functions I need to run that require the data to be inserted before they run (i.e. they are aggregating data).

So, I really want to just queue up running my procedure till some time later (it's not time dependent).

How do I do that in Oracle? The wealth of documentation is rather overwhelming when I just want to do something simple.

Answer 1

The standard aproach for this would be to use dbms_jobs to schedule a job calling the procedure.

If there is some precondition, the job could check the precondition. If it is fulfilled, the job continues, if not it reschedules itself and exit.

Answer 2

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name => 'daily_tasks_job',
    job_type              => 'STORED_PROCEDURE',
    job_action            => 'prc_daily_tasks',
  repeat_interval       => 'FREQ=DAILY; INTERVAL=1',
  enabled               => TRUE,
  comments              => 'Calls stored procedure once a day'
  );
END;

BEGIN
     DBMS_SCHEDULER.create_job(
    job_name        => 'SHELL_JOB',
    repeat_interval  => 'FREQ=DAILY; BYHOUR=2',
    job_type         => 'EXECUTABLE',
    job_action       => '/u01/app/oracle/admin/tools/shell_job.sh',
    enabled          => TRUE,
    comments         => 'Perform stuff'
);
END;

Answer 3

You may also want to see the answer to this:

http://stackoverflow.com/questions/576802/can-we-use-threading-in-pl-sql/