Direct, simple SQL query results in “ORA-01406: fetched column value was truncated”

If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:

variable p_owner varchar2(30);
variable p_column varchar2(30);

set verify off
set feedback off

begin
    :p_owner := '&1';
    :P_column := '&2';
end;
/

set feedback on

select table_name
from all_tab_columns
where owner = :p_owner
and column_name = :p_column;

exit 0;

And put the parameter values in a text file, say parms.txt:

SYS
OSUSER

And put it together with:

sqlplus -s user/password @query `cat parms.txt`

(This is for Unix, obviously; not sure off-hand what the command-line equivalent is for Windows). Change parms.txt, or use different files, and you'll still get the same hard-parsed plan each time.

Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description. The execution plan is determined by the first query run, which might choose suitable indexes etc. for those parameters; subsequent runs then use the same plan even if different indexes might be more suitable for the new parameters. The stand-alone straight SQL will have a separate parse and thus possibly different (more suitable) plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.

Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.

SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to do its fetching.

hth!