Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Run Proc Async
On Thu, 05 Jun 1997 12:48:59 GMT, mwagoner_at_iac.net (Mark Wagoner) wrote:
>Is it possible to run a stored procedure asynchronously? I need to have
>one procedure (or external app) start a stored procedure but regain control
>immediately. I need the second procedure to continue running in the
>background.
>
>If I use EXECUTE from SQL*Plus, I have to wait until the procedure ends
>before I can continue. Same thing if I call the procedure from a PL/SQL
>block. Does anyone know a way around this?
>
>Thanks
>
>--
>Mark Wagoner
>mwagoner_at_medplus.com (work)
>mwagoner_at_iac.net (life)
>
You can use dbms_job to do this. Please review dbmsjob.sql found in $ORACLE_HOME/rdbms/admin for more info and what init.ora settings you must make. Basically, you will do something like:
is
l_jobno number;
begin
....
dbms_job.submit( l_jobno, 'some_procedure_call( 5, ''Hello'' );' );
commit;
....
end;
This would schedule some_procedure_call to execute with inputs 5 and 'Hello'. You need to commit to let the job scheduler see the job. You need to set 3 init.ora parms to get this working automatically...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities