PL/SQL: Need to call another proc and not wait around.

Is it possible to execute a stored proc from another and not wait around for it to finish?

Give the example below, I want to execute procB, which executes procA.  I do not want to wait the 5 seconds it takes for A before B returns.

create or replace procedure procA as
begin
      dbms_lock.sleep(5);
end;
/

show errors

create or replace procedure procB as
begin
      --need to exec procA and not wait for a response.
      procA;
end;
/

show errors

exec procA;
exec procB;
LVL 79
slightwv (䄆 Netminder) Asked:
Who is Participating?
 
earth man2Commented:
dbms_job ?
0
 
slightwv (䄆 Netminder) Author Commented:
Hadn't thought about that approach.  Might work if that's all that's available.  I can see the logistics of that becoming a nightmare real quick......
0
 
Helena Markováprogrammer-analystCommented:
Here is also an intersting part - pipelined functions:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#19677
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
chedgeyCommented:
Depends on your requirements but you can set up the two procedures as independent transactions and call between them using the DBMS_PIPE package:

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_pipe.htm#996728

The basic idea is to have procA in a perpetual wait loop waiting to start on a named pipe and then have procB run procA by sending a message (fire and forget) on the named pipe. See the examples in the DBMS_PIPE documentation from the link above.

Regards

Chedgey
0
 
slightwv (䄆 Netminder) Author Commented:
Thanks for the posts thus far.  

I'd like to clarify a bit:  I have a stored procedure that is called from a web form.  It accepts data from the web form and performs some processing on it and eventually tucks it away safely into a table and releases the web form to continue it's processing.  I would now like to continue some additional processing and not hold up the web form while this processes.

I'm thinking the proper approach would be to implement workFlow but this seems like overkill for this.  Just hoping that I had overlooked something simple.
0
 
chedgeyCommented:
Just the sort of situation the simple daemon example in the web link I provided earlier is for. Workflow will do the job for you but is overkill for this when it is so easy to write a pipe based version using DBMS_PIPE.

I doubt you need anything so overblown but here we have re-written all of out asynchronus programming such as this from PL/SQL using DBMS_PIPE to using Microsoft BizTalk Server 2004 which does the job admirably.

Take a look at http://www.microsoft.com/biztalk

Regards

Chedgey
0
 
earth man2Commented:
dbms_pipe is OK  for simple transactions.  Data in pipe is lost when system crashes.

May need to use DBMS_AQ.
0
 
slightwv (䄆 Netminder) Author Commented:
Thanks to all who played along.  Not sure how I'm going to implement this (probably outside of Oracle)........
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.