Preventing sqlplus truncation of column names, without individual column formatting

Question

By default sqlplus truncates column names to the length of the underlying data type. Many of the column names in our database are prefixed by the table name, and therefore look identical when truncated.

I need to specify select * queries to remote DBAs in a locked down production environment, and drag back spooled results for diagnosis. There are too many columns to specify individual column formatting. Does sqlplus offer any option to uniformly defeat column name truncation?

(I am using SET MARKUP HTML ON, though I could use some other modality, csv, etc. as long as it yields unabbreviated output.)

Answer 1

One thing you can try is to dynamically generate "column x format a20" commands. Something like the following:

set termout off
set feedback off

spool t1.sql
select 'column ' || column_name || ' format a' || data_length
from all_tab_cols
where table_name='YOUR_TABLE'
/
spool off

@t1.sql
set pagesize 24
set heading on
spool result.txt
select * 
from  YOUR_TABLE;
and   rownum < 30;
spool off

Note that this sample will only work with VARCHAR2. You would need to add decode for example to change the generated "column" command for DATEs or NUMBERs.

UPDATE: It turns out the original SQL doesn't really change the behaviour of the SQL*Plus. The only thing I could think of is to rename the field names to one character values A, B, C, etc.. in the following way:

select 'column ' || column_name ||
       ' heading "' ||
       chr(ascii('A') - 1 + column_id) ||
       '"'
from all_tab_cols
where table_name='YOUR_TAB_NAME'

It will generate the output similar to:

column DEPT_NO heading "A"
column NAME heading "B"
column SUPERVIS_ID heading "C"
column ADD_DATE heading "D"
column REPORT_TYPE heading "E"