We are migrating a sql script containing a pl/sql package body to compile on the destination. The file is around 1800 lines. Our commands are timing out even before the full script is getting fed to sqlplus. See the log:
KSC Simple Respond
Source Command: Connect to Destination
sqlplus apps@DEVDB @$driver SQL*Plus: Release 220.127.116.11.0 - Production on Wed Oct 14 16:55:26 2009 (c) Copyright 1999 Oracle Corporation. All rights reserved. Enter password: **** Waiting for command prompt Connected to: Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options SQL> @gl_rates.pkb SQL> CREATE OR REPLACE PACKAGE BODY gl_rates 2 AS ... 1412 EXCEPTION 1413 WHEN OTHERS 1414 THEN 1415 p_out_num_ret_code := 2; 1416 p_out_chr_errbuff := 'Unexpected Termination' || SQLERRM; 1417 glb_err.set_procedure ( 141Preparing for further command execution
So you can see how the sql script is being called, and where it hangs. It is not even fully fed into sqlplus before ITG gets impatient and writes "Preparing for further command execution" to the log. There is nothing unusual about the sql file, and this problem is occurring for other files as well (for this destination environment). It seems to work fine for sql files that are not as long, even 1000 lines or so. I was wondering if perhaps there is some buffer that is being hit- ITG command buffer or timeout, perhaps with SSH2? I can verify that I can connect to the server myself and execute the driver file and it completes fine- sql executes and the package is created. It does not work through ITG however. I must note that we are using a new Linux environment and using SSH2. Most of our other environments, which do not have this issue, are HP-UX and use telnet. Any ideas what this could be? I'm wondering if perhaps ITG has some SSH2 settings that can be adjusted. Any ideas are appreciated.
I can execute the driver script manually from the same server and it runs fine. It is only through ITG that I have an issue. It only seems to be occuring for these new linux environments that use SSH2, 11g DB. Sqlplus is called from these boxes, not from the ITG box.
I have opened a ticket with support, hopefully will get some suggestion there. I'm thinking it must be some ITG setting since I can run the script fine manually.
Support is not going to help me since I am on a desupported version, 6.0. However, I seem to have found a workaround. In the driver script creation there is a set command:
set echo on >> $driver
I just commented that line and now the scripts are being created just fine. Only problem is we cannot see the sqlplus session details in the log, can't see the code that is getting compiled. So, I don't want to leave things this way, this is just a workaround. I'm thinking there must be something with SSH buffer size- when echo is turned on it seems to fail when the output is greater than 1300 lines or so. Turning echo off seems to avoid this. I'll keep looking around and post back if I find anything. Thanks all for your help and please let me know if you have any further suggestions.