?
Solved

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

Posted on 2005-04-11
8
Medium Priority
?
1,796 Views
Last Modified: 2012-06-27
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;
0
Comment
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 22

Accepted Solution

by:
earth man2 earned 600 total points
ID: 13754822
dbms_job ?
0
 
LVL 78

Author Comment

by:slightwv (䄆 Netminder)
ID: 13754908
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
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 200 total points
ID: 13759418
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 6

Assisted Solution

by:chedgey
chedgey earned 200 total points
ID: 13760665
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
 
LVL 78

Author Comment

by:slightwv (䄆 Netminder)
ID: 13761420
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
 
LVL 6

Expert Comment

by:chedgey
ID: 13761653
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
 
LVL 22

Expert Comment

by:earth man2
ID: 13762512
dbms_pipe is OK  for simple transactions.  Data in pipe is lost when system crashes.

May need to use DBMS_AQ.
0
 
LVL 78

Author Comment

by:slightwv (䄆 Netminder)
ID: 13772284
Thanks to all who played along.  Not sure how I'm going to implement this (probably outside of Oracle)........
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month14 days, 12 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question