| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Multi-Threaded PL/SQL
On Wed, 09 Jun 1999 17:26:31 -0600, Dogan Cibiceli <dcib_at_gwl.com> wrote:
>Hi,
> I have a curious question. To utilize the database to max ( in off
>time), I would like a procedure to run several diff. instances. Each
>instance of the procedure will work on one part of the huge table and
>the parts will be non-overlapping. Is there a feature in Oracle that
>lets me call a procedure (PL/SQL) and go on without waiting for its
>results. The procedure will have its own thread of execution. I would
>like to spawn (fork) 10 instances of the same procedure. Does that make
>sense also ??
You could look at scheduling the procedure to run as a database job. This way your process could connect, initialize the job and disconnect. The database will then run the job sometime later.
You need to set up the database to handle jobs. In the init.ora file, the following parameters need to be set.
job_queue_interval = 60 job_queue_keep_connections = TRUE job_queue_processes = 1
This tells the database to use one process to handle the jobs scheduled and to check every 60 seconds to see if there is a job to run.
An example of how to schedule a job would look like...
create or replace
procedure my_proc_background(
p_param1 varchar2,
p_param2 varchar2 ) is
--
l_job number;
begin
dbms_job.submit(
job => l_job,
what => 'my_proc( ' || p_param1 || ', ' || p_param2 || ' );' );
end proc_background;
/
This will schedule a job and tell the database to run the procedure my_proc with the parameters p_param1 and p_param2. Since it did not specify when to run it, it will run it the next time the job_queue is checked ( within the next 60 seconds if your init.ora is set up the above parameters. )
Check chapter 7 of the Oracle8 Server Administrators Guide for more information on the dbms_job package.
hope this helps.
chris.
>
>D
>
>PS. Or back to basics and write a shell script or something like that ?
>
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |