Solved

oracle pl/sql mutlithreading

Posted on 2013-05-28
7
1,915 Views
Last Modified: 2013-07-04
Hi ,

I do not have knowledge on implementing multi threading concept in pl/sql. I have to develop a pl/sql procedure which in turns call 4 more procedures to load some data. This procedure would be called from an Java program . This java program is scheduled using an scheduler and when the user starts the java program , it wil invoke the  stored procedure.

The user has the option to suspend and resume the java pr ogram. When the user suspends the java program , he updates a process table to a status suspended, then the procedure running in the database must stop execution and when the user resumes the java program , then the stored procedure must start executing from the place it stopped.

I have decided to use two procedures proc 1 which calls the proc 2 . While proc2 is execting , proc1 will heck the status in the process table . If it is suspended , then it has to stop the execution of the stored procedure.

Can anyone help me how I can achive this code using oracle 11g pl/sql

Thanks in advance.
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39202006
I don't believe there is a way to halt the execution of a stored procedure.  Once executed it will run to completion.

I suppose you could write your own code into the procedure to check the process table throughout the code.  Then go into a sleep loop until the table is updated again.

As you can imagine, this could cause some major issues.  For example, the user kills the java program and the procedure will loop forever.
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 39202033
I don't think that real multithreading can be done with PL/SQL due to its procedural execution patterns, but you can take a look at this (using pipes):

http://www.oracle-base.com/articles/misc/dbms_pipe.php

But if these procedures are being called from a java program, why don't you just put the multithreading into java, it should be quite easy...
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 250 total points
ID: 39202041
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39202057
>>tuning-plsql-with-multithreading-dbms_scheduler/

The issue here is each part of the procedure will still execute to completion.  Now, if you break the procedure up into manageable sections then you can likely halt between sections but it will still likely require some custom code.
0
 

Author Comment

by:sam_2012
ID: 39203251
Thanks for your suggestions ..

Is it possible to stop the execution of the procedure , if I schedule it from dbms_job.

Iam planning to execute two procedure from two threads using java program ,

One thread, i will call the proc1 which will loop for ever and constantly monitor the status table , if any update is done , then  it will send the message to the proc2 which will run using the other thread . Proc 2 will scheduled using dbms_job .

Can I stop the execution of an procedure for some time using either dbms_job, dbms_scheduler package?

Sending the status information between the two procedures , can it be achievied using the dbms_pipe or dbms_alert packages?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39203274
>>Can I stop the execution of an procedure for some time using either dbms_job, dbms_scheduler package?

No.  You can halt dbms_scheduler but I'm pretty sure any procedure it is running will continue until it stops.

>>Sending the status information between the two procedures , can it be achievied using the dbms_pipe or dbms_alert packages?

Should be possible. You can en-queue and de-queue messages.  How the code handles the messages is up to you.  This is where I mentioned a lot of custom code.
0
 

Author Closing Comment

by:sam_2012
ID: 39300356
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

630 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