How to run oracle stored procedures asynchronously and independently from client application?

Question

I have implemented a stored procedure that generates a csv report based on the data in a transaction_table, and stores the generated report in report_table for future references.
I execute and pass arguments to this procedure using JPA in a java program and it works perfectly fine.

Problems are:

since we have huge amount of transaction data in transaction_table, it takes some time for report to be generated. And during this time the the pop-up thread responsible for generating the report is blocked.
If database connection for running the procedure gets broken in the middle of execution, not even we don't get the report , but also the database thread responsible for handling the request does not get completed and remains in the memory in some unknown state.So we need an active connection with database during execution time.

My questions are:

Is there any way to call procedure and return immediately, without having a thread in the application blocked for entire execution time of stored procedure.
Since there is a chance of losing the database connection, is there any way that database can run the procedure independently from the application that is calling it, so that it gets completed even in the absence active connection.

Note that I need to pass the report parameters from application to the procedure.

I have Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, running on the server.

Actually in my research I came a cross this answer [link](https://stackoverflow.com/questions/9179775/passing-arguments-to-oracle-stored-procedure-through-scheduler-job) , but I don't knows how to pass arguments from java application to it and execute it! — M-Soley, Mar 03 '19 at 08:34
Hmmm. At the start of your question you say *"I have implemented a stored procedure ... I execute and pass arguments to this procedure using JPA"*. So what is different about calling a stored procedure which kicks off a background job? — APC, Mar 03 '19 at 09:20

score 1 · Answer 1 · answered Mar 06 '19 at 10:37

I did come up with a solution for this problem. so I decided to share it, in case you have similar problem.

First all let me further explain the problem, and then share the solution.
problem was: I am using a connection pool in JPA to connect to database and I use JPA annotations to execute a procedure on database (I execute the procedure in a separate thread in application side). The query is dealing with transactions for generating a massive report, so it takes some time to be executed. When, for whatever reason, the database connection that has been obtained from the pool gets broken, not even that the database procedure does not get completed, but also it does not fail so that at least it frees up the resources that it has in hand.

Solution:
Short answer is: I created a another procedure (wrapper procedure) that creates and starts dbms_schedule job (with some random name) that runs a dbms_schedule program which runs the main procedure. Since the wrapper procedure finishes in matter of milliseconds, It does not block the db connection to long, so that it can fail.

Long answer:
Step 1: creating the program.

BEGIN
DBMS_SCHEDULER.create_program(
    program_name => 'DBUSER.PROG_NAME',
    program_action => 'DBUSER.MAIN_REPORT',
    program_type => 'STORED_PROCEDURE',
    number_of_arguments => 1, //number of passed arguments to procedure
    comments => NULL,
    enabled => FALSE);

//Do this for each argument    
DBMS_SCHEDULER.define_program_argument(
    program_name => 'DBUSER.PROG_NAME',
    argument_name => NULL,
    argument_position => 1,
    argument_type => 'VARCHAR2',
    out_argument => FALSE);

passing procedure arguments
DBMS_SCHEDULER.ENABLE(name=>'DBUSER.PROG_NAME');    
END;

Step 2: create the wrapper procedure.

create or replace PROCEDURE WRAPPER_PROC 
(
  FIRST_ARG IN VARCHAR2 
) 
IS
  job_name_var VARCHAR2(20);
BEGIN

  //creating a random job-name
  select DBMS_SCHEDULER.generate_job_name ('TEMP_JOB_') INTO job_name_var from dual;
  //creating the job
  dbms_scheduler.create_job(job_name      =>  job_name_var ,
                          program_name    =>  'PROG_NAME',
                          start_date      =>  systimestamp,
                          auto_drop       =>  true,
                          repeat_interval =>  null,
                          end_date        =>  null);
  //passing the argument to job                        
  dbms_scheduler.set_job_argument_value(job_name_var, 1, FIRST_ARG);
  //specifying the the dbms should drop the job after it has run
  dbms_scheduler.set_attribute(job_name_var,'max_runs',1);

  dbms_scheduler.enable(job_name_var);

  DBMS_OUTPUT.put_line('Job has successfully created');

END WRAPPER_PROC;

hope it helps!

How to run oracle stored procedures asynchronously and independently from client application?

1 Answers1