Oracle asynchronous call procedure

I had a procedure that inserts and updates in 5 table so natural it takes time, so i came up in my mind to create asynchronous call to procedure. To do that i used the dbms_job to call explicit pl sql anonymous block. Here is how I did it.


procedure p_call_job_(p_id in table1.id%type) is
  v_plSqlBlock varchar2(500);
  v_job_int binary_integer;
begin

 -- create pl sql block
v_plSqlBlock  :=
'BEGIN ' ||
   'pack_demo.p_procedure( ' || to_char(p_id ) || ' ); ' ||
'END;';

  dbms_job.submit(
                  job =>  v_job_int,
                  what => v_plSqlBlock
                  );        
exception
  when others then
   -- p_log is a procedure that log for me if somethings go wrong
    p_log('failed to execute job ' || sqlerrm);
end p_call_job_for_import;

So the client call this procedure and now he can continue his work.  You can also create atribute on table that will show client in witch status is the hole process.  :]

Comments

Popular Posts