Running an Oracle Procedure in Background

In this Oracle tutorial, you will learn how to run a stored 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 Stored Procedure in Background Example

Suppose we have the following stored procedure in the 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 the 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 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.

You can even repeat the above PL/SQL code block to run the procedure for different items by changing the item ID parameter.