Parallel processing in oracle

Question

I had a 'procedure A' in which it has to return a null cursor to front end and then immediately it should continue with the next step in which it will call a procedure which will take 20 min to complete the proc.

Procedure A(cur_out refcursor)
begin

 OPEN cur_out  for
 select 
 null empname,
 null empid
 from dual;

procedure B();//Will take 20 min

end;

Here the 'Procedure A' should return the null cursor without waiting for the 'Procedure B' to complete.

How to implement this in oracle.

Thanks in advance.

Answer 1

Assuming that you don't need procedure B to run in the same session as A, you can schedule the job to run asynchronously, i.e.

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(); END;' );
  commit;
END a;

You'll return from A and a job will be scheduled immediately to run procedure B in a separate session (note that the job won't start until the current session commits which is why I added a commit here-- if your code is going to commit elsewhere, you can eliminate that). All dbms_job.submit is doing is creating the job-- the job will not start executing until after the procedure returns.

If B takes arguments, you'll need to build the PL/SQL block dynamically. So if B takes two NUMBER parameters

CREATE OR REPLACE PROCEDURE a( p_cur_out OUT SYS_REFCURSOR )
AS
  l_jobno  pls_integer;
  l_param1 pls_integer;
  l_param2 pls_integer;
BEGIN
  OPEN p_cur_out 
   FOR SELECT cast( null as varchar2(64) ) empname, 
              cast( null as integer ) empid
         FROM dual;

  dbms_job.submit( l_jobno,
                   'BEGIN B(' || to_char( l_param1 ) || ', ' ||
                                 to_char( l_param2 ) || '); END;' );
  commit;
END a;