Can We use threading in PL/SQL?

Question

Is there any feature of asynchronous calling in PL/SQL? Suppose I am in a block of code would like to call a procedure multiple times and wouldn't bother when and what the procedure returns?

BEGIN
  myProc(1,100);
  myProc(101,200);
  myProc(201,300);
  ...
  ...

END;

In the above case, I don't want my code to wait for myProc(1,100) to finish processing before executing(101,200)
Thanks.

Answer 1

You may want to look into DBMS_SCHEDULER.

Edited for completeness:

DMBS_SCHEDULER is available on Oracle 10g. For versions before this, DBMS_JOB does approximately the same job.

For more information, see: http://download.oracle.com/docs/cd/B12037_01/server.101/b10739/jobtosched.htm

Answer 2

Submit it in a DBMS_JOB like so:

declare
  ln_dummy number;
begin
  DBMS_JOB.SUBMIT(ln_dummy, 'begin myProc(1,100); end;');
  DBMS_JOB.SUBMIT(ln_dummy, 'begin myProc(101,200); end;');
  DBMS_JOB.SUBMIT(ln_dummy, 'begin myProc(201,300); end;');
  COMMIT;
end;

You'll need the job_queue_processes parameter set to >0 to spawn threads to process the jobs. You can query the jobs by examining the view user_jobs.

Note that this applies to Oracle 9i, not sure what support 10g has. See more info here.

EDIT: Added missed COMMIT

Answer 3

+1 for DBMS_SCHEDULER and DBMS_JOB approaches, but also consider whether you ought to be using a different approach.

If you have a procedure which executes in a row-by-row manner and you find that it is slow, the answer is probably not to run the procedure multiple times simltaneously but to ensure that a set-based aproach is used instead. At an extreme you can even then use parallel query and parallel DML to reduce the wall clock time of the process.

I mention this only because it is a very common fault.

Answer 4

Here an explanation of different ways of unloading data to a flat file. One of the ways shows how you can do parallel execution with PL/SQL to speed things up.

http://www.oracle-developer.net/display.php?id=425

Answer 5

Another way of doing parallel (multi-threaded) PL/SQL is shown here:

http://www.williamrobertson.net/feed/2007/10/parallel-plsql-launcher.html

The disadvantage of using dbms_job or dbms_schedular is that you don't really know when your tasks are finished. I read that you don't bother but maybe you will change your mind in the future.

EDIT:

This article http://www.devx.com/dbzone/10MinuteSolution/20902/0/page/1 describes another way. It uses dbms_job and dbms_alert. The alerts are used to signal that the jobs are done (callback signal).

Answer 6

Have you considered using Oracle Advaned Queuing?