Home » SQL & PL/SQL » SQL & PL/SQL » Procedure Calls (10g)
Procedure Calls [message #442218] Fri, 05 February 2010 07:05 Go to next message
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 #442219 is a reply to message #442218] Fri, 05 February 2010 07:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Spawn a job using dbms_job or dbms_schedular to run procedureC
Re: Procedure Calls [message #442220 is a reply to message #442219] Fri, 05 February 2010 07:13 Go to previous messageGo to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
I have looked into that package.

ANy chance of an example of the dbms_scheduler package which serves this purpose?
Re: Procedure Calls [message #442222 is a reply to message #442218] Fri, 05 February 2010 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #442225 is a reply to message #442224] Fri, 05 February 2010 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

Then the job is the way to do.

Regards
Michel
Re: Procedure Calls [message #442226 is a reply to message #442218] Fri, 05 February 2010 07:37 Go to previous messageGo to next message
eight8ball@gmail.com
Messages: 20
Registered: February 2010
Junior Member
Okie Dokie - Thanks!
Re: Procedure Calls [message #442278 is a reply to message #442218] Sat, 06 February 2010 07:55 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: can i remove pragma autonomous_transaction in this scenario
Next Topic: how to remove special character and albhabet in the given string
Goto Forum:
  


Current Time: Mon Dec 05 02:53:56 CST 2016

Total time taken to generate the page: 0.12982 seconds