Solved

oracle pl/sql mutlithreading

Posted on 2013-05-28
7
1,807 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 60
Row_number in SQL 6 56
UTL_FILE invalid file operation 5 59
why truncate is faster than delete in oracle ? 4 71
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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 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