Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle pl/sql mutlithreading

Posted on 2013-05-28
7
Medium Priority
?
2,021 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 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
Industry Leaders: 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 77

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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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…
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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

722 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