Execute Procedure in Background - Dynamic Scheduling

To execute procedure in background, most of the time you will be advised to execute the procedure using OS shell. This is not practical if you need it frequently or you have no privilege on OS server.

In Data warehousing environment, often we want to execute several transformations based on same operational data. That is, after loading the operational data in staging area, we have to do several transformation on this raw data. The obvious solution of this problem is to put the transformations in different procedure and schedule them depending on raw data loading. In this case you have to estimate the raw data loading acutely. Otherwise, if you execute a single procedure and within that call the three procedures (one raw data load and two transformation) sequentially, then you have to wait for the other transformation.

In stead all of the above options, you can schedule the two transformation after the raw data load with the main procedure to run them in parallel. For this you have to schedule the two transformations and pass null value as interval parameter in DBMS_JOB.SUBMIT procedure call. As interval is set to null this procedure will be scheduled to run only once. So after execution of the procedure you will not find these jobs dba_jobs view.

You can use the following procedure for this -

CREATE OR REPLACE PROCEDURE proc_run_bg(proc_name_with_param varchar2, delay_time number default -1) IS
job_id NUMBER;
BEGIN
--schedule the procedure to run it after the delay time mentioned in parameter

DBMS_JOB.SUBMIT
(
job => job_id
,what => proc_name_with_param||';'
,next_date => sysdate+(delay_time/(24*60))
,interval => NULL
);

commit;

END proc_run_bg;
/

A sample procedure utilizing this proc_run_bg procedure -


CREATE OR REPLACE PROCEDURE proc_etl(refdate date) IS
BEGIN

load_operational_data(refdate);

proc_run_bg('proc_transform1('''||refdate||''')');

proc_run_bg('proc_transform2('''||refdate||''')');

/* proc_transform1 & proc_transform2 will run in parallel and proc_etl will be completed just after submitting of these two procedure to dbms_job. It does not execute these transformation directly.*/

END proc_etl;
/


I have named this procedure proc_run_bg as in essence it runs the procedure in background after the time (in minute) elapses that you passed as parameter. Thus you can execute two procedure within one session literally but not in reality!!

This is very useful to me for dynamic scheduling as dependent procedures can be scheduled at run time instead of using fixed expression. That is, without scheduling dependent procedures to run everyday at 7 am. (by estimating that staging data get loaded before 7 am.) , I use it to run two transformations just after loading the staging data.