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
Post a Comment