Home » SQL & PL/SQL » SQL & PL/SQL » How to use timeout in plsql (Oracle10g)
How to use timeout in plsql [message #589703] Wed, 10 July 2013 05:23 Go to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Hi,

I have one procedure which calls a function. My requirement is if that function didnt return value in 1 minute then stop the exection of that function and call next statement.

Begin
(1).........
(2).......
(3) get_func_val:= function(1,2);
(4)......
(5)....
end;

My requirement is, if (3) doesnt return value in 1 minute, stop the execution of (3) and start executing (4).


Regards,
Parag
Re: How to use timeout in plsql [message #589705 is a reply to message #589703] Wed, 10 July 2013 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just insert "-- " before the function call.

Regards
Michel
Re: How to use timeout in plsql [message #589706 is a reply to message #589705] Wed, 10 July 2013 05:35 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Hi Michel,

Is it going to work??

--Parag
Re: How to use timeout in plsql [message #589708 is a reply to message #589706] Wed, 10 July 2013 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, definitively.

Regards
Michel
Re: How to use timeout in plsql [message #589709 is a reply to message #589703] Wed, 10 July 2013 05:37 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Hello,

What is exactly the purpose/output of this function so that you can simply ignore it based on its execution delay, and doing so without any impact on the program flux? For me it doesn't make sense.

Regards,
Dariyoosh

[Updated on: Wed, 10 July 2013 05:39]

Report message to a moderator

Re: How to use timeout in plsql [message #589712 is a reply to message #589709] Wed, 10 July 2013 05:41 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
And to clarify what Michel is saying - if the rest of the procedure doesn't depend on the result of the function you don't need to call it in the first place.

[Updated on: Wed, 10 July 2013 05:41]

Report message to a moderator

Re: How to use timeout in plsql [message #589715 is a reply to message #589712] Wed, 10 July 2013 05:44 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
I would like to publish my business requirement here.

Business Requirement:-
There is a package which calls plsql function from one oracle form at the click of one button.

If this function is taking 10 minutes to return value, user has to wait for 10 minutes. But what user is saying now, if it is taking more than 1 minutes to user want to skip executing function and go ahead with next statement execution.

--Parag
Thats why I want to put timeout of 1 minute so that next statements will be executed...

Re: How to use timeout in plsql [message #589716 is a reply to message #589715] Wed, 10 July 2013 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 11088
Registered: September 2008
Location: Rainy Manchester
Senior Member
Again - if the process doesn't need it to run why are you running it at all?
Re: How to use timeout in plsql [message #589717 is a reply to message #589715] Wed, 10 July 2013 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again If the user don't care about the function result then just don't call it and the user will be happy with the new better response time.

Regards
Michel
Re: How to use timeout in plsql [message #589720 is a reply to message #589715] Wed, 10 July 2013 06:12 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Dariyoosh,
Basically I am calling webservice from plsql function, if that webservice is taking more than 1 minute, i want to stop processing and mark that record as error and resume the execution back to calling enviornment.

--Parag
Re: How to use timeout in plsql [message #589722 is a reply to message #589720] Wed, 10 July 2013 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And after that what about this "error"? What do you do with it?

Regards
Michel
Re: How to use timeout in plsql [message #589737 is a reply to message #589722] Wed, 10 July 2013 07:06 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
We will process these error records manually by some other method.
Re: How to use timeout in plsql [message #589741 is a reply to message #589737] Wed, 10 July 2013 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway, the only way is that the function aborts itself after the time you may give in a parameter.
The only other way is to do it asynchronously using a job (as anyway the user don't want/need to have the result immediately).

Regards
Michel
Re: How to use timeout in plsql [message #589743 is a reply to message #589737] Wed, 10 July 2013 07:30 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
What is the return type of the function? Scalar? composit? REF CURSOR?

Can there be any possibility of using a pipelined table function instead of waiting for the whole result to be processed?


Regards,
Dariyoosh
Re: How to use timeout in plsql [message #589745 is a reply to message #589743] Wed, 10 July 2013 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Might be a good idea, this is indeed another option.

Regards
Michel
Re: How to use timeout in plsql [message #589747 is a reply to message #589745] Wed, 10 July 2013 07:43 Go to previous message
ThomasG
Messages: 3101
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Since it's calling a webservice: Can't a http timeout be set in some way? (would depend on throuh which mechanism the web service is actually called I gues.)
Previous Topic: Bulk insert in index table
Next Topic: EXECUTE IMMEDIATE - Create View
Goto Forum:
  


Current Time: Thu Oct 30 08:45:04 CDT 2014

Total time taken to generate the page: 0.11068 seconds