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

this will work if you are selecting all columns from a table or a view - boils down to writing the columns first, then the data.

Below assume TAB delimited. If you want comma or some other delimiter, you need to change it in two places: colsep and the listagg lines below; Example for comma delimiter: the new colsep line would be: set colsep ',' and the new listagg line adjustment would be replace chr(9) with chr(44)

/*google "sqlplus OPTION" to get the meaning */
    set colsep '    ' --literal TAB; can alter to taste
    set HEADING OFF
    set UNDERLINE OFF
    set PAGESIZE 50000
    set LINESIZE 32767
    set TERMOUT OFF
    set TRIMSPOOL ON
    set FEEDBACK OFF
    set WRAP OFF
    set NEWPAGE none

/*first, write the column names to the file*/
    spool "C:\yourPath\output.txt"
        select listagg(column_name, chr(9) ) within group (order by column_id)
        FROM dba_tab_columns
        WHERE owner = 'SomeOwner'
        AND table_name = upper('ViewOrTable_Name')
        ;

/*now append the data*/
    spool "C:\yourPath\output.txt" append

        select * from  SomeOwner.ViewOrTable_Name
        where 1 = 1
        ;

/*stop spooling*/
spool OFF

Answer 2

None of the proposed solutions work to show the original column names, so I'm not sure why people are voting them up... I do have a "hack" that works for the original request, but I really don't like it... That is you actually append or prefix a string onto the query for each column so they are always long enough for the column heading. If you are in an HTML mode, as the poster is, there is little harm by a bit of extra white spacing... It will of course slow down your query abit...

e.g.

SET ECHO OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET NUMW 20
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
spool '/tmp/Example.html'
select 
   (s.ID||'                  ') AS ID,
   (s.ORDER_ID||'                  ') AS ORDER_ID,
   (s.ORDER_NUMBER||'                  ') AS ORDER_NUMBER,
   (s.CONTRACT_ID||'                  ') AS CONTRACT_ID,
   (s.CONTRACT_NUMBER||'                  ') AS CONTRACT_NUMBER,
   (s.CONTRACT_START_DATE||'                  ') AS CONTRACT_START_DATE,
   (s.CONTRACT_END_DATE||'                  ') AS CONTRACT_END_DATE,
   (s.CURRENCY_ISO_CODE||'                  ') AS CURRENCY_ISO_CODE,
from Example s
order  by s.order_number, s.contract_number;
spool off;

Of course you could write a stored procedure to do something better, but really it seems like overkill for this simple scenario.

This still does not meet the original posters request either. In that it requires manually listing on the columns and not using select *. But at least it is solution that works when you are willing to detail out the fields.

However, since there really is no problem having too long of fields in HTML, there is an rather simple way to fix Chris's solution to work it this example. That is just pick a use the maximum value oracle will allow. Sadly this still won't really work for EVERY field of every table, unless you explicitly add formatting for every data type. This solution also won't work for joins, since different tables can use the same column name but a different datatype.

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 32766
SET LINESIZE 32766
SET MARKUP HTML OFF
SET HEADING OFF

spool /tmp/columns_EXAMPLE.sql
select 'column ' || column_name || ' format A32766' 
from all_tab_cols
where data_type = 'VARCHAR2' and table_name = 'EXAMPLE'
/
spool off

SET HEADING ON
SET NUMW 40
SET VERIFY OFF
SET TERM OFF
SET UNDERLINE OFF
SET MARKUP HTML ON
SET PREFORMAT ON
SET WORD_WRAP ON
SET WRAP ON
SET ENTMAP ON
@/tmp/columns_EXAMPLE.sql
spool '/tmp/Example.html'
select *
from Example s
order  by s.order_number, s.contract_number;
spool off;

Answer 3

I had the same problem trying to implement this feature in VoraX. In the next version I have in mind the following solution:

set feedback off 
set serveroutput on
declare
  l_c number;
  l_col_cnt number;
  l_rec_tab DBMS_SQL.DESC_TAB2;
  l_col_metadata DBMS_SQL.DESC_REC2;
  l_col_num number;
begin
  l_c := dbms_sql.open_cursor;
  dbms_sql.parse(l_c, '<YOUR QUERY HERE>', DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS2(l_c, l_col_cnt, l_rec_tab);
  for colidx in l_rec_tab.first .. l_rec_tab.last loop
    l_col_metadata := l_rec_tab(colidx);
    dbms_output.put_line('column ' || l_col_metadata.col_name || ' heading ' || l_col_metadata.col_name);
  end loop;
  DBMS_SQL.CLOSE_CURSOR(l_c);
end;

Instead of tweaking column sizes, formatting and stuff just enforce the column heading with the column name you want. I think the same approach would work also with DBA_TAB_COLUMNS solution but I prefer the DBMS_SQL one as it also considers the aliases and it gets only the columns you query.

EDIT: Using just "column heading" doesn't work. It's still needed to use "column format" statements. So, please ignore my previous answer.

Answer 4

This should provide some reasonable formatting. You are, of course, free to substitute your own preferences for the maximum width of char columns, and what to do with LONG, RAW and LOB columns.

SELECT 'COLUMN ' || column_name || ' FORMAT ' ||
       CASE
          WHEN data_type = 'DATE' THEN
           'A9'
          WHEN data_type LIKE '%CHAR%' THEN
           'A' ||
           TRIM(TO_CHAR(LEAST(GREATEST(LENGTH(column_name),
                        data_length), 40))) ||
           CASE
              WHEN data_length > 40 THEN
               ' TRUNC'
              ELSE
               NULL
           END
          WHEN data_type = 'NUMBER' THEN
           LPAD('0', GREATEST(LENGTH(column_name),
           NVL(data_precision, data_length)), '9') ||
           DECODE(data_scale, 0, NULL, NULL, NULL, '.' ||
           LPAD('0', data_scale, '0'))
          WHEN data_type IN ('RAW', 'LONG') THEN
           'A1 NOPRINT'
          WHEN data_type LIKE '%LOB' THEN
           'A1 NOPRINT'
          ELSE
           'A' || TRIM(TO_CHAR(GREATEST(LENGTH(column_name), data_length)))
       END AS format_cols
  FROM dba_tab_columns
 WHERE owner = 'SYS'
   AND table_name = 'DBA_TAB_COLUMNS';

Answer 5

It's a bit of a hack if you don't need or want XML formatting, but you should be able to use the DBMS_XMLGEN package. This script should give you an XML file for an arbitrary query with the full column name as the tag name.

VARIABLE resultXML clob;
SET LONG 100000; -- Set to the maximum size of the XML you want to display (in bytes) 
SET PAGESIZE 0;

DECLARE
   qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN
  qryCtx := dbms_xmlgen.newContext('SELECT * from scott.emp');

  -- now get the result
  :resultXML := DBMS_XMLGEN.getXML(qryCtx);

  --close context
  DBMS_XMLGEN.closeContext(qryCtx);
END;
/

print resultXML

Answer 6

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"

Answer 7

I don't think sqlplus offers the functionality you are requesting. You might be able to automate the formatting, using some sort of scripting language such as Perl or Python. In other words, query the ALL_TAB_COLS view for the schema and table, and then create the script dynamically with a format column attribute. This will only work, of course, if you have permission to query the ALL_TAB_COLS view (or some other equivalent).

This is a quick proof-of-concept I threw together:

#!/usr/bin/python

import sys
import cx_Oracle

response=raw_input("Enter schema.table_name:  ")
(schema, table) = response.split('.')
schema = schema.upper()
table = table.upper()
sqlstr = """select column_name,
                   data_type,
                   data_length
              from all_tab_cols
             where owner      = '%s'
               and table_name = '%s'""" % ( schema, table )

## open a connection to databases...
try:
    oracle = cx_Oracle.Connection( oracleLogin )
    oracle_cursor = oracle.cursor()

except cx_Oracle.DatabaseError, exc:
    print "Cannot connect to Oracle database as", oracleLogin
    print "Oracle Error %d:  %s" % ( exc.args[0].code, exc.args[0].message )
    sys.exit(1)

try:
    oracle_cursor.execute( sqlstr )

    # fetch resultset from cursor
    for column_name, data_type, data_length in oracle_cursor.fetchmany(256):
        data_length = data_length + 0
        if data_length < len(column_name):
            if data_type == "CHAR" or data_type == "VARCHAR2":
                print "column %s format a%d" % ( column_name.upper(), len(column_name) )
            else:
                print "-- Handle %s, %s, %d" % (column_name, data_type, data_length)

except cx_Oracle.DatabaseError, e:
    print "[Oracle Error %d: %s]:  %s" % (e.args[0].code, e.args[0].message, sqlstr)
    sys.exit(1)

try:
    oracle_cursor.close()
    oracle.close()
except cx_Oracle.DatabaseError, exc:
    print "Warning: Oracle Error %d:  %s" % ( exc.args[0].code, exc.args[0].message )

print "select *"
print "from %s.%s" % ( schema, table )