Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Column Header in the Spool file

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Column Header in the Spool file
# 1  
Old 04-02-2014
[Solved] Column Header in the Spool file
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
Code:
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


Moderator's Comments:
Mod Comment Please use code tags next time for your code and data, NOT ICODE. Thanks
Last edited by vbe; 04-02-2014 at 12:53 PM..
# 2  
Old 04-02-2014
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  
Old 04-02-2014
Yes, vbe

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

Thanks...
# 4  
Old 04-02-2014
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
Code:
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  
Old 04-02-2014
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  
Old 04-02-2014
Code:
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  
Old 04-03-2014
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
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Split file by column value, each with header

Hello all, I have a csv with with different testcase values in column 5. year,min,max,Instrument,Testcase 2016,201,1003,GEOTROPH-02116,TATA7980 2016,53,1011,GEOTROPH-01963,TATA7980 2016,3,1024,GEOTROPH-02067,TATA7980 2016,203,1027,GEOTROPH-02011,TATA7980... (16 Replies)
Discussion started by: senhia83
16 Replies

2. Shell Programming and Scripting

How to get sqlplus column header once in csv file?

Hi All, Could anyoone please let me know how do I get sqlplus column header once in csv file Scripts are below: cat concreq.sh #!/bin/bash . $HOME/.profile while ; do sqlplus apps/pwd <<-EOF set lines 100 pages 100 col "USER_CONCURRENT_QUEUE_NAME" format a40; --set termout off... (5 Replies)
Discussion started by: a1_win
5 Replies

3. Shell Programming and Scripting

Add column header and row header

Hi, I have an input like this 1 2 3 4 2 3 4 5 4 5 6 7 I would like to count the no. of columns and print a header with a prefix "Col". I would also like to count the no. of rows and print as first column with each line number with a prefix "Row" So, my output would be ... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

4. UNIX for Dummies Questions & Answers

append column and row header to a file in awk script.

Hi! Is there a way to append column and row header to a file in awk script. For example if I have Jane F 39 manager Carlos M 40 system administrator Sam F 20 programmer and I want it to be # name gend age occup 1 Jane F 39 manager 2 Carlos M ... (4 Replies)
Discussion started by: FUTURE_EINSTEIN
4 Replies

5. Shell Programming and Scripting

Put Header on Text file of all column

Hi I have in put file A.txt ABCDE1 JFHFJFJF3 1 1 SC1 12/10 ABCDE2 JFHFJFJF5 1 1 SC1 12/10 ABCDE3 JFHFJFJF5 1 1 SC1 12/10 ABCDE4 JFHFJFJF6 1 1 SC1 12/10 I want output in .csv with header: Name SUb_N x y No Board ABCDE1 JFHFJFJF3 1 1 SC1 12/10 ABCDE2 JFHFJFJF5 1 1 SC1... (7 Replies)
Discussion started by: pareshkp
7 Replies

6. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

7. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

8. Ubuntu

Inserting a header with column number to a 1.6 GB file with special spacing

Hi; I've been searching posts to find a solution to what I'm trying to do, but I've have NOT found anything yet. I have a file (file1) with 300K columns and 1411 rows, the columns don't have a column no. header (No header at all) and I'm trying to fetch the information from specific columns.... (3 Replies)
Discussion started by: sogi
3 Replies

9. Shell Programming and Scripting

awk/sed column replace using column header - help

$ cat log.txt Name Age Sex Lcation nfld alias xsd CC 25 M XYZ asx KK Y BB 21 F XAS awe SS N SD 21 M AQW rty SD A How can I replace the column with header "Lcation" with the column with header "alias" and delete the "alias" column? so that the final output will become: Name Age Sex... (10 Replies)
Discussion started by: jkl_jkl
10 Replies

10. Shell Programming and Scripting

Convert Header into Column in Text file

Hi Gurus, I have a requirement like this and have to create a UX shell scripts. Thanks in advance. File-in: ------ Header2007-12-012007-11-21 100|xyz|was 101|wsa|qws ...... ....... Output should be: ------------------- 2007-12-01|100|xyz|was 2007-12-01|101|wsa|qws ...... .......... (7 Replies)
Discussion started by: vsubbu1000
7 Replies
Login or Register to Ask a Question