Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

oracle pl/sql mutlithreading

Posted on 2013-05-28
7
Medium Priority
?
2,122 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 750 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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 750 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 750 total points
ID: 39202041
0
Independent Software Vendors: 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!

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 750 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 78

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying 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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

877 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