Procedure Calls [message #442218] |
Fri, 05 February 2010 07:05  |
eight8ball@gmail.com
Messages: 20 Registered: February 2010
|
Junior Member |
|
|
I would like to achieve the following, is it possible and if so...how?
From my main processing block I want to call a procedure. This procedure will perform some tasks and then call a further procedure. What I want to happen is for the control to return to the main process regardless of the outcome of the third procedure.
I.E.
ProcedureA;
-- do some stuff
-- procedure_b;
-- do some more stuff;
ProcedureB;
--do some stuff
-- ProcedureC; -->> so i want procedureB to return to procedure A now.....rather than wait for procedure c to finish
ProcedureC;
-- do some stuff
Is this possible? How?
Thanks
Joe
|
|
|
|
|
Re: Procedure Calls [message #442222 is a reply to message #442218] |
Fri, 05 February 2010 07:19   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The only way to do it in synchroneous is to raise a specific error that is not trapped by the second procedure:
SQL> create or replace procedure p3
2 is
3 begin
4 if dbms_random.value(0,1) < 0.7 then
5 raise_application_error(-20000,'Exit!');
6 end if;
7 end;
8 /
Procedure created.
SQL> create or replace procedure p2
2 is
3 begin
4 dbms_output.put_line('calling p3');
5 p3;
6 dbms_output.put_line('p3 normally returned');
7 end;
8 /
Procedure created.
SQL> create or replace procedure p1
2 is
3 x exception;
4 pragma exception_init(x, -20000);
5 begin
6 begin
7 p2;
8 dbms_output.put_line('p2 returned');
9 exception
10 when x then dbms_output.put_line('exception in p3');
11 end;
12 end;
13 /
Procedure created.
SQL> exec p1;
calling p3
exception in p3
PL/SQL procedure successfully completed.
SQL> exec p1;
calling p3
exception in p3
PL/SQL procedure successfully completed.
SQL> exec p1;
calling p3
p3 normally returned
p2 returned
PL/SQL procedure successfully completed.
Regards
Michel
[Updated on: Fri, 05 February 2010 07:19] Report message to a moderator
|
|
|
Re: Procedure Calls [message #442224 is a reply to message #442218] |
Fri, 05 February 2010 07:29   |
eight8ball@gmail.com
Messages: 20 Registered: February 2010
|
Junior Member |
|
|
Dont think that will achieve what I want.
The basic concept is to have a call to my procedure from a legacy system, that will create lines in a table. Once the lines are created there is an enrichment process that needs to happen on the data just created. But I dont want the legacy system to have to wait for that process to start/end.
I just want it to return as soon as the lines are created.
|
|
|
|
|
Re: Procedure Calls [message #442278 is a reply to message #442218] |
Sat, 06 February 2010 07:55  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
There is no "parallel plsql" or "concurrent plsql" or "multi-threaded plsql" concept in PLSQL. This was never built into plsql and to my knowledge, oracle has no plans to add it anytime soon.
Thus it is not possible to create a true multi-threaded plsql transaction in which all threads will work their way to a common synch point and more importantly, all threads being governed by the same transaction commit or rollback. It is not possible to do this:
begin
proc1;
proc2;
proc3;
...
commit;
end;
/
with the expectation that each procedure call above will run asynchornously (proc2 can start without proc1 having been completed) as a seperate thread yet all three calls under the control of the same transaction.
It is possible to use any of several Oracle features to create multiple transactions that run independently of each other but this is exactly as it sounds, multiple transactions each of which can suceed or fail independently of each other. For example, it is possible to do this:
begin
dbms_job.isubmit(...,'proc1;',...);
dbms_job.isubmit(...,'proc2;',...);
dbms_job.isubmit(...,'proc3;',...);
...
commit;
end;
/
The above requires four seperate transactions, one for the main code stream and one for each job submit. HOWEVER there are other rules to be observed.
Quote:1) if the main stream fails to commit, or otherwise does a rollback, the dbms_job calls will never execute.
2) if the main stream reaches its commit, then the three jobs will be queued for execution but you cannot control precisely when they will actually execute. It is quite possible for you to wait hours or days for one or more of these jobs to start. Indeed it is even very possible that one or more of your "submitted" jobs could be removed from the job queue by some other process and never execute at all. The important point being that your main code stream looses all control over the "submitted" job parts. Think about that before you jump into trying to parallelize your plsql code. How would you detect that one of your submitted jobs failed to execute because it was removed from the job queue? Your main code stream cannot recover from this because it will have ended by the time you figure this out, even if you could figure it out.
3) each of the jobs is a seperate transaction and as such can succeed or fail independently of the others. Thus you must be good with that, and have a way to resolve issues where one or more of the jobs fails even though your main code stream succeeded.
If you are bent on trying to do some kind of parallel plsql, here are related topics (not an all exclusive list) that you will want to familiarize yourself with:
DBMS_JOB/DBMS_SCHEDULER
Oracle Rules Manager
Oracle Advanced Queuing
DBMS_ALERT
DBMS_PIPE
Parallel Query and Parallel DML
Parallel Query Hack
Each of the above can be used to create or help create a multi-transaction job stream. Consider however that those of us who have tried to do what you want generally stop trying. Except for extreme situations, there is no real value in it. At least that is my opinion.
To emphasis this let me ask you a question... WHY? Why do you want to do this? If you are trying to get things to go faster I would suggest you look at other tuning practices first.
Good luck, Kevin
|
|
|