In this Oracle tutorial, you will learn how to run a procedure in the background using DBMS_SCHEDULER.
Running a procedure in the background means that the stored procedure will run asynchronously.
You can run multiple procedures in the background, which will run parallel in different threads using the different sessions.
To do this, we will use Oracle’s DBMS_SCHEDULER.CREATE_PROGRAM and DBMS_SCHEDULER.CREATE_JOB methods.
Running a Procedure in Background Example
Suppose we have a following stored procedure in Oracle database which accepts two parameters to perform some inventory update task:
Create or Replace Procedure proc_inventory_update (i_item_id in number, i_qty in number)
is
Begin
/* Here your code goes. */
End;
/Create a dbms_scheduler Program
Now to call the above procedure “proc_inventory_update” using the 2 arguments, create a program in Oracle database using the DBMS_SCHEDULER.CREATE_PROGRAM method as shown in the below example:
Begin
/* Create a program and specify the procedure name
and set number of arguments */
dbms_scheduler.create_program
(
program_name=>'invprog',
program_action=>'proc_inventory_update',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>2, enabled=>FALSE
) ;
/* Setting for both arguments */
dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'invprog',
argument_position=>1,
argument_type=>'NUMBER'
);
dbms_scheduler.DEFINE_PROGRAM_ARGUMENT(
program_name=>'invprog',
argument_position=>2,
argument_type=>'NUMBER'
);
/* Enable the program */
dbms_scheduler.enable('invprog');
End;
/After running the above PL/SQL block a DBMS_SCHEDULER program named “invprog” will be created and enabled.
But it will not run until you will call it using the DBMS_SCHEDULER.CREATE_JOB method.
Running a dbms_scheduler Program using dbms_scheduler Job
Below is an example to call the above DBMS_SCHEDULER program using the DBMS_SCHEDULER.CREATE_JOB method:
Declare
v_item_id varchar2(30) := 10001;
v_qty varchar2(30) := 45;
v_jobname varchar2(50) := 'invjob'||v_item_id;
Begin
/* Below we will give reference of the dbms_schedule program invprog */
dbms_scheduler.create_job(v_jobname,program_name=> 'invprog');
/* set the values for 2 arguments */
dbms_scheduler.set_job_argument_value(v_jobname, 1, v_item_id);
dbms_scheduler.set_job_argument_value(v_jobname, 2, v_qty);
/* This will enable the job and run once. */
dbms_scheduler.enable(v_jobname);
/* You can write more code here it will not wait for the above task to complete. */
End;
/It will run the DBMS_SCHEDULER program “invprog” and the “invprog” will run the stored procedure “proc_inventory_update” in the background.
You can use the above PL/SQL code in any of your existing stored procedure.
And you can even repeat the above PL/SQL code block to run the procedure for different items by changing the item ID parameter.
I gave up and decided to use repetitive job. But, probably, issue in my case was due to I tried to use procedure from package.
Yes, it should be a standalone procedure because we are specifying program_type as STORED_PROCEDURE.
Hi! Very good article! But doesn’t work for me. 🙁 Oracle created all without issues, but nothing happend. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production.
I have tested this on Oracle database 12c onwards, and it is working fine.
Please note that when I am running the scheduled procedure, I have taken the v_item_id and v_qty as varchar2, not as a number. Because when I declared them as the number, it was not working.
So please check if you declare the parameters as numbers.