SQL*Plus : column headers truncated

[pbopbo]

Hello,

I want to create a .csv file from a table.

My script :

SET echo OFF 
SET HEADINGS ON 
SET colsep ','    
SET feedback OFF    
SET linesize 1000      
SET sqlprompt ''    
SET serveroutput ON SIZE 40000 FORMAT 
SET TERMOUT OFF
SET UNDERLINE OFF
SET trimspool OFF
SET trimout OFF              

spool C:\temp\EXECUTION.csv
SELECT *
FROM EXECUTION
WHERE ROWNUM < 10;
spool OFF
QUIT

I obtain this result :

EXECUTION  ,CODE ,ST,DATEDEBU,DATEFIN
#xxxxxxx   ,12349,ok,01.01.03,01.01.03
...

The "ST" corresponds to "STATUS" and "DATEDEBU" to "DATEDEBUT". It seems that SQL*Plus truncates the column name to the lenght of the datatype.

How can I change this and force SQL*Plus to show complete names (without formatting each column with COLUMN command ...) ?

Thanx,

Philippe

 

[lewisp]

You could include the column names in your select, and use LPAD or RPAD like this:

[tt]SELECT EXECUTION,
CODE,
Lpad(STATUS,6) STATUS,
Lpad(To_Char(DATEDEBUT,DD.MM.YY),9) DATEDEBUT,
DATEFIN
FROM EXECUTION
WHERE ROWNUM < 10;[/tt]

 

[pbopbo]

Thank you for your response.
But is there no other way to tell sql*plus it should display the full name ? I would not like to have to know the structure of the table (for a generic batch processing).

No idea ?

 

[sem]

Look at COLUMN command syntax, e.g

COLUMN status HEADING &quot;STATUS&quot; FORMAT A6

Regards, Dima

 

[Turkbear]

HI, as to wanting to do it for any table without knowing the structure ( with some global SqlPlus setting for instance), it is not possible..
The column heading ( unless specified or modified by the Column format option, as mentioned by Dima) will have a length <= the width of the data column it names - i.e. a varchar2(12)column will display up to 12 letters of the column name ,but no more.

 

[pbopbo]

Thank you for confirmation.

I think it's a strange limitation. A parameter like &quot;SET HEADERWIDTH [COLNAME|DATA]&quot; should not have been complicated to implement !...

 

[lewisp]

The only way around this I can think of is that your main script calls another script which generates a third script (phew!) containing the COLUMN commands you need. The generate script will read from the USER_TAB_COLUMNS or ALL_TAB_COLUMNS views for the table you are selecting from. Your main script then runs the third generated script ahead of the main SQL.

Your generate script, called COLFORM.sql, might look something like this

[tt]SET PAGES 0
SET FEEDBACK OFF
SET VERIFY OFF

SELECT 'COLUMN ' || column_name ||
' FORMAT a' || Length(column_name)
FROM user_tab_columns
WHERE data_length < Length(column_name)
AND table_name = '&1'

SPOOL cols.sql
/
SPOOL OFF[/tt]

Then your script would use it like this

[tt]colform EXECUTION
@cols

SET echo OFF
SET HEADINGS ON
SET colsep ','
SET feedback OFF
SET linesize 1000..... etc[/tt]

 

[lewisp]

Oops, missed the @ in front of 'colform EXECUTION'.

 

[pbopbo]

I tried to do this, but I had bad results with numeric columns. If you apply an 'AXX' format on a numeric column, you obtain something like ####### in the data...

So one should refine your query with a DECODE like

DECODE(DATA_TYPE, 'NUMBER', '999', 'VARCHAR', 'A')...

Too much work for my &quot;little&quot; problem !

Thank you for your advices.

Philippe

 

[lewisp]

Ok, how about

[tt]SELECT 'COLUMN ' || column_name ||
' FORMAT a' || Length(column_name)
FROM user_tab_columns
WHERE NVL(data_precision,
NULL,data_length,
(data_precision + data_scale + 1)) < Length(column_name)
AND table_name = '&1'[/tt]