Format options while Redirecting output of sql to a file in shell


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Format options while Redirecting output of sql to a file in shell
# 1  
Old 12-14-2012
Format options while Redirecting output of sql to a file in shell

In my korn shell - I have a sql with say 6 columns whose output i am redirecting to a file and attaching this file while sending a mail. While all this happens correctly, i face issues in the format of this file.

my intended output is
Code:
Column_1     Column_2     Column_3     Column_4     Column_5     Column_6
abcde             John             Hammer        Club              Cool             Forever
cdfes              Cathy            Hello             Wow             Hot              Obselete

But my outout is like
Code:
Column_1     Column_2     Colu            Column_4     Column_5         Colu
abcde             John             Hammer        Club              Cool             Forever
cdfes              Cathy            Hello             Wow             Hot              Obselete

Note here the "Column_3" was cut down to "colu" and the again the "Column_6" was cut down to Colu.

Can someone please tell me how am i supposed to print the entire heading from my sql alias in my output file too.

Appreciate your help. TIA!


Moderator's Comments:
Mod Comment Please use code tags next time for your code and data.

Last edited by Franklin52; 12-16-2012 at 09:58 AM..
# 2  
Old 12-14-2012
Please always use code tag if you are posting code fragments or data samples.

You can do following in SQL:-
Code:
set echo off head off feed off pagesize 0 trimspool on linesize 1000
select 'Column_1 Column_2 Column_3 Column_4 Column_5 Column_6' from dual;
select  Column_1,Column_2,Column_3,Column_4,Column_5,Column_6  from your_table;

# 3  
Old 12-14-2012
Unless you show us what you did to produce the truncated output, we can't do anything but guess about what might cause what you're seeing. Please post your code (and use code tags).
# 4  
Old 12-14-2012
Format options while Redirecting output of sql to a file in shell

Ok ill keep that in mind next time to use these code tags.

Please find my code here -

Code:
sqlplus -s / >$my_file <<EOF
  set serveroutput on;
  set pagesize 400;
  set heading on;
  set lines 10000;
  set feedback off;
  set linesize 10000;
  set verify off;
  spool $spooled_file
             SELECT a.column_1 Column_1,
                b.column_2 Column_2 ,
                c.column_3 Column_3,
                a.column_4 Column_4,
                a.column_5 Column_5,
                b.column_6  Column_6
         FROM   table_a a,
                table_b b,
                table_c c
         WHERE    a.p_id=b.p_id
                 AND b.c_id=c.f_id
  spool off 
  exit sql.sqlcode 
EOF

# 5  
Old 12-14-2012
Did you see my post?

Set the heading OFF then form column headers using DUAL table and run your query.
# 6  
Old 12-14-2012
Format options while Redirecting output of sql to a file in shell

Bipinajith,

If i do that all the column headers are printed fully but the formatting between the column headers and the data is lost. The data doesnt fall correctly under the corresponding header.

Please suggest any other way.
# 7  
Old 12-14-2012
Then use col to format:-
Code:
col Column_1 format a8 
col Column_2 format a8 
col Column_3 format a8 
col Column_4 format a8 
col Column_5 format a8 
col Column_6 format a8

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

Python or Shell script to Grep strings from input file and output in csv format

Hi Experts, I am writing a python script to grep string from file and display output in csv file as in attached screenshot https://drive.google.com/file/d/1gfUUdfmQma33tz65NskThYDhkZUGQO0H/view Input file(result_EPFT_config_device) Below is the python script i have prepared as of... (1 Reply)
Discussion started by: as7951
1 Replies

2. UNIX for Beginners Questions & Answers

Not able to write SQL query output in to .csv file with shell script.

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line. Main Code shell script: this is my code: #!/bin/bash file="db_detail.txt" . $file rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies

3. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

4. Shell Programming and Scripting

Redirecting command output to a file in a shell script

Hello All, I have some unique requirement. I have written a very lengthy script which calls number of resource script to execute a particular task. What I want is output of each command(called from main script and resource scripts) should go to a... (3 Replies)
Discussion started by: anand.shah
3 Replies

5. Shell Programming and Scripting

Help me! Format output file using shell program

Hi, I have following input file. I want to generate output file in specific format using shell program. The input file has atleast few thousands of lines, the below are some sample lines. Input file: "ORDER NO"|"ORDER AMT"|"LINE ITEM"|"LINE AMT"|"SALES COMMISION %" ORD3456|5000|LIN345|30|25%... (8 Replies)
Discussion started by: dsubha
8 Replies

6. Shell Programming and Scripting

formatting into CSV format of SQL session output

I am getting a no of fields from a SQL session (e.g. select a,b,c from table). How do I convert the output values into CSV format . The output should be like this 'a','b','c', (4 Replies)
Discussion started by: mady135
4 Replies

7. Shell Programming and Scripting

redirecting sql query output to a file

Hi, I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning. success records to success.log file failure record to failure.log file. As of now i am doing like... (1 Reply)
Discussion started by: sailaja_80
1 Replies

8. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi Yogesh, Lucky that i caught you online. Yeah i read about DBI and the WriteExcel module. But the server is not supporting these modules. It said..."Cannot locate DBI"..."Cannot locate Spreadsheet::WriteExcel" I tried creating a simple text file to get the query output, but the... (1 Reply)
Discussion started by: dolphin123
1 Replies

9. Shell Programming and Scripting

Redirecting sql select query result to txt file

Hi , I just found you while surfing for the string 'Redirecting sql select query output from within a shell script to txt file/excel file' Could you find time sending me the code for the above question? It'll be great help for me. I have a perl file that calls the sql file... (1 Reply)
Discussion started by: dolphin123
1 Replies

10. Shell Programming and Scripting

redirecting SQL output from within a shell script

Hi all I would like to open a SQL session from within a shell script and then redirect the output of the SQL command to a file. e.g. #!/bin/bash sqlplus "/ as sysdba" <<EOF @$HOME/recovery_space.sql EOF I want to redirect the output of the SQL command to a temp file, because... (2 Replies)
Discussion started by: soliberus
2 Replies
Login or Register to Ask a Question