Project and Portfolio Management Practitioners Forum
cancel

command execution times out in sqlplus

Highlighted
Daniel U
Frequent Contributor.

command execution times out in sqlplus

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 8.0.6.0.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 11.1.0.7.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.

Thanks
6 REPLIES
Nisha Kurien
Honored Contributor.

Re: command execution times out in sqlplus

Can you try to execute/create this package using TOAD or SQL Developer?
Daniel U
Frequent Contributor.

Re: command execution times out in sqlplus

Yes, I can compile successfully in TOAD and also through sqlplus. Problem only occurs when ITG tries to run through sqlplus.
Nisha Kurien
Honored Contributor.

Re: command execution times out in sqlplus

Not sure if this would help, you could include a statement in your script:
SET SERVEROUTPUT ON SIZE UNLIMITED

How is this script called? Is it via an execution command?
Marlene Mazzeo
Super Contributor.

Re: command execution times out in sqlplus

Not sure if this would make a difference, but it looks like the SQL Plus version is older than the database.

SQL*Plus: Release 8.0.6.0.0
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

Not sure if this is another application's database or ITG's or the version of Oracle that ITG is running on.

I have had similar issues in the past, and HP Support usually has had me look at the SQL Plus version defined in the server.conf first, and to update to match the ITG database if it did not match.
Daniel U
Frequent Contributor.

Re: command execution times out in sqlplus

Thanks for your replies. I just tried the "set serveroutput on size unlimited" suggestion but there was no change. The driver script is called with the following command:

ksc_simple_respond "sqlplus [DEST_ENV.DB_USERNAME]@[DEST_ENV.ENV.DB_CONNECT_STRING] @$driver" -hide "Enter password:" "[DEST_ENV.DB_PASSWORD]"

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.
Daniel U
Frequent Contributor.

Re: command execution times out in sqlplus

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.