Thanks for the question, Shubhi.
Asked: October 10, 2019 - 9:02 am UTC
Last updated: October 10, 2019 - 1:00 pm UTC
Version: 18.1
Viewed 1000+ times
You Asked
i am tracking around 17000 orders through a web service through pl/sql to a destination server. i am running multiple jobs in batches(for 500 orders each job) for invoking webservice to get the order status. so around 34 jobs are running (17000/500) at the same time to get status of 500 orders each. Now, few of my jobs are getting finished properly but few are long running and are not finishing and getting stuck (not even giving any error or exception) as connection is not establishing with the destination server through utl_http.begin_request.
i have added UTL_HTTP.set_transfer_timeout but i believe that works for getting the response but here connection itself is not establishing.
Please give me a solution so that in such case if i am not able to establish connect then job should end giving some error.
with LiveSQL Test Case:
and Chris said...
MOS note 760664.1 cover this issue.
The short answer is the timeout doesn't apply to making the initial connection. Only to calls you make once you've established a connection.
Luckily there is a workaround:
In Oracle there is a workaround to this limitation, you can configure the TCP connection timeout by setting any of the below SQL*Net parameter:
* SQLNET.OUTBOUND_CONNECT_TIMEOUT
* TCP.CONNECT_TIMEOUT
The outbound connect timeout is a super set of the TCP connect timeout which specifies a limit on the time taken to establish a TCP connection. For more information about these parameters, you can refer to chapter "Parameters for the sqlnet.ora File" in "Oracle Database Net Services Reference" guide.
Example:
Inside the SQLNET.ORA file on the database server, add any of the following parameters which will time out all the TCP connection attempts that take longer than the specified number of seconds to connect.
TCP.CONNECT_TIMEOUT = 30
or
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 30
Limitation:
Setting these parameters will not only impose a 30 second timeout for UTL_HTTP package, but it will affect all outbound TCP connections from the database. So, only consider using this workaround if you would like to limit the connection timeout for ALL TCP connections from your database.
Is this answer out of date? If it is, please let us know via a Comment