Parallel processing in oracle

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.

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;