ignition00
asked on
SqlPlus Report Generation Column Headings Truncated
Running SQL Plus on Oracle 10g.
When creating reports I find that column headings are truncated to the maximum length of the column data. This is causing problems becuase the data is to be imported into Access and full heading names are required. A read of the documentation seems to say that there is no way round this but wondered if anyone had some magic spells, rain dances or other proven methods for getting this to work properly.
My script is:
<code>
set echo off
set arraysize 5000
set colsep ';'
set sqlblanklines on
set linesize 10000
set trimspool on
set heading on
set pagesize 50000
set timing off
set termout off
set feedback off
set verify off
set concat on
COLUMN MANDT TITLE 'MANDT' FORMAT A5
<<snip>>
COLUMN DEAR6 TITLE 'DEAR6' FORMAT A5
SPOOL <<snip>>.TXT
select * from SAPR3.KNA1 where rownum < 11;
SPOOL OFF
</code>
Sample output:
MAN;KUNNR ;LAN;NAME1 ;
010;1000000000;GB ;name removed ;
Note the first column "MAN" should read "MANDT"
Thanks
When creating reports I find that column headings are truncated to the maximum length of the column data. This is causing problems becuase the data is to be imported into Access and full heading names are required. A read of the documentation seems to say that there is no way round this but wondered if anyone had some magic spells, rain dances or other proven methods for getting this to work properly.
My script is:
<code>
set echo off
set arraysize 5000
set colsep ';'
set sqlblanklines on
set linesize 10000
set trimspool on
set heading on
set pagesize 50000
set timing off
set termout off
set feedback off
set verify off
set concat on
COLUMN MANDT TITLE 'MANDT' FORMAT A5
<<snip>>
COLUMN DEAR6 TITLE 'DEAR6' FORMAT A5
SPOOL <<snip>>.TXT
select * from SAPR3.KNA1 where rownum < 11;
SPOOL OFF
</code>
Sample output:
MAN;KUNNR ;LAN;NAME1 ;
010;1000000000;GB ;name removed ;
Note the first column "MAN" should read "MANDT"
Thanks
i've read quickly http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12040.htm#sthref2520 and havent found any possibility how to display whole column name
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you concatenate a blank or two to the ones where you are havig trouble? i.e.:
SELECT (TO_CHAR(mandt) || ' ') mandt, etc.
I have done that before when the look was more important than the length of the SQL.
SELECT (TO_CHAR(mandt) || ' ') mandt, etc.
I have done that before when the look was more important than the length of the SQL.