Column Header in the Spool file

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

Hi All,

I have a problem with the column heading while spooling the data from the database. Since i want the column header, therefore i am not using SET HEADING OFF, and i am getting the header, but for the longer column name, some of the character from the column name is missing.

for example

sqlplus -s $USER<< SQL 
SET SERVEROUTPUT ON
SET TIMING OFF
SET VERIFY OFF
SET FEED OFF
SET ECHO OFF
SET TERM OFF
SET FEEDBACK OFF
SET UNDERLINE OFF
set PAGESIZE 300
set LINESIZE 300
 
spool /abc/xyz/abc.csv
select * abc where date >= '20140401' and date <='20140402'
spool off
exit;
SQL

for some column header i am not getting all the character as there in the database. Could you please help me guys on this....

Thanks in Advance..
Pramod

2

OK, but what defines the column size?
And So if column size is less than the header label it will be truncated...
With what you submitted we cant really do more...

3

Yes, vbe

You are absolutely correct. The column size is less than the column header. Is there any workaround.

Thanks...

4

Im very well and should be in bed I suppose, but beeing at work I try my best but I could come up with real nonsense so be aware...
Last time I code in SQL was 18 years ago... but I remember I could change the column size... a syntax similar to

column your_column         format  AXX

XX beeing the newsize

Maybe worth trying or look in column command syntax ( sorry I am no more DBA... so no doc here...)

5

Yah i could see that....and for your kind information if i could do that then i would have done that...Since i am spooling the data from production environment, i am not authorized to do that. Whatever i could do, it can be in the shell script through files.

So, its not the DBA related things that i have asked, i know we can do that in database level but i want it to be done through shell script.

If you could provide any workaround on this then it would be great otherwise thanks for the help and suggestion.

Regards,
Pramod

6 
sqlplus -s $USER<< SQL 
SET SERVEROUTPUT ON
SET TIMING OFF
SET VERIFY OFF
SET FEED OFF
SET ECHO OFF
SET TERM OFF
SET FEEDBACK OFF
SET UNDERLINE OFF
set PAGESIZE 300
set LINESIZE 300
column  your_column1         format  AXX
column  your_column2         format  AYY
column  your_column3         format  AZZ
 
spool /abc/xyz/abc.csv
select * abc where date >= '20140401' and date <='20140402'
spool off
exit;
SQL

You have no other choice since its in the spool that you issue resides...

7

Hi vbe,

Thanks for the suggestion and help. The code is really helpful for me and its working as per the requirement too.

Thanks
Pramod

8

You welcome... :wink: