By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,676 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Perl script that cannot receive respond from Oracle stored procedure

P: 6
I had a Perl script that called a sql file calling the stored procedure in Oracle database. The stored procedure ran for a long time (more than 1.5 hrs) and did not return the result code to the perl script. (I could see from the job log of the stored procedure that the stored procedure was successfully completed)
Expand|Select|Wrap|Line Numbers
  1. my $sql = "sqlplus -s userid/password $jobId";
  2. $rtnCode = system($sql);
  3.  
I had other similar jobs that ran shorter time (e.g. 10 mins). That job could successfully got the result code.

Is the connection lost between the database server and the application server ?Is there any parameter that can control the timeout ?

Please help.
Feb 11 '10 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 785
I am pretty sure the connection timeout was hit.
And it's not a good idea to stay connected that long. A connection should be returned as fast as possible to the connection pool for usage of other processes.
So instead of fiddling with the timeout-parameters of your computer, your firewall and your oracle server, you could approach the problem another way:
make a temporary table "temp" with columns for your expected result and execute "insert into temp select my_stored_procedure(...) from dual" with autocommit. Don't wait for results, just close the connection.
Then you can poll the table after an hour to see if the result is already arrived. if yes, then just make a small sql on this temp table to grab it.
Feb 12 '10 #2

P: 6
Dear chaarmann,

I searched the internet and found that a setting of "KeepAliveTime" under "\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Ser vices\Tcpip\Parameters" in the windows registry may help the issue.

But it may require the application program to use this setting. May I have more information how to use this setting in batch file (.bat) or the Perl program (.pl) ?
Feb 24 '10 #3

P: 6
If I set the value of "KeepAliveTime" to 300000, do I need to specify in my Perl code to use this feature ? Or I can leave it to the OS to handle ?
Feb 25 '10 #4

P: 6
Does anyone have any idea if I set the value of "KeepAliveTime" to 300000 in the windows registry "\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Ser vices\Tcpip\Parameters", do I need to specify in my Perl code/bat file to use this feature ? Or I can leave it to the OS to handle ?
Feb 26 '10 #5

P: 6
Would anyone please give me some idea if I set the value of "KeepAliveTime" to 300000 in the windows registry "\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet \Ser vices\Tcpip\Parameters", do I need to specify in my Perl code/bat file to use this feature ? Or I can leave it to the OS to handle ?

I tried to search for the information in the internet, but could not find any information.

Thanks in advanced.
Mar 1 '10 #6

P: 6
I tried to set the value of KeepAliveTime in the windows registry and not changed anything in the Perl code/batch file, but it still did not get the return code.

The system was just transferred to our site. The program logic and system settings were new to us. We tried to keep the changes minimal to the system. And the database server and the application server was in trusted zone and DMZ respectively.

Is there any other ways to tackle the problem ?
Mar 2 '10 #7

Expert Mod 100+
P: 589
Have you tried using Perl's DBI module instead of shelling out to sqlplus?

DBI - Database independent interface for Perl
http://search.cpan.org/~timb/DBI-1.609/DBI.pm

DBD::Oracle - Oracle database driver for the DBI module
http://search.cpan.org/~pythian/DBD-....24a/Oracle.pm
Mar 2 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.