Solved

oracle pl/sql mutlithreading

Posted on 2013-05-28
7
1,595 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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now