Perl script that cannot receive respond from Oracle stored procedure

[sammak168]

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)

my $sql = "sqlplus -s userid/password $jobId";
$rtnCode = system($sql);

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.

[chaarmann]

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_proce dure(...) 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.

[sammak168]

Dear chaarmann,

I searched the internet and found that a setting of "KeepAliveT ime" under "\\HKEY_LOCAL_M ACHINE\SYSTEM\C urrentControlSe t\Services\Tcpi p\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) ?

[sammak168]

If I set the value of "KeepAliveT ime" 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 ?

[sammak168]

Does anyone have any idea if I set the value of "KeepAliveT ime" to 300000 in the windows registry "\\HKEY_LOCAL_M ACHINE\SYSTEM\C urrentControlSe t\Services\Tcpi p\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 ?

[sammak168]

Would anyone please give me some idea if I set the value of "KeepAliveT ime" to 300000 in the windows registry "\\HKEY_LOCAL_M ACHINE\SYSTEM\C urrentControlSe t\Ser vices\Tcpip\Par ameters", 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.

[sammak168]

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 ?

[RonB]

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-Oracle-1.24a/Oracle.pm