format of output is weird

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting format of output is weird
# 1  
Old 08-13-2009
format of output is weird

hi all,
have a ksh script which connects to a database and runs a sql and dumps it to a '.csv' file. The problem is the result is in multiple rows with long spaces in between when it should be just a single line and this screws up the format in the '.csv' file.

script is :

export SQLPLUS=/appl/oracle/product/;
export mydate=`/usr/bin/date +%d%m%Y_%H_%M`;

$SQLPLUS -s $1/$2@$3<<EOF

set head off
set feedback off
set lines 300
set pages 300

spool out_$mydate.csv

 SELECT 'name ', ',',
        'Number', ',',
        'Name', ',',
        'name', ',',
        'Product', ',',
        'Product ID', ',',
        'Product Name', ',',
 from dual;

SELECT  (users.FIRST_NAME || ' ' || users.LAST_NAME) || ',',
              users.TNUMBER || ',',
              cam.CAMPAIGN_NAME || ',',
              team.TEAM_TITLE || ',',
              pSales.PRODUCT_TYPE_ID || ',',
              pSales.PRODUCT_ID || ',',
              product.PRODUCT_NAME || ',',
FROM tables names
WHERE ...... ;
spool off

this script is meant to write out the heading i.e the first select and then put data under those headers but since the data has long spaces in it, the format is messed up.

This is what gets written out to the '.csv' file :

name , Number , Name , name, Product , Product ID , Product Name , Date

sys admin, t12345,
my plan 08,
Administrators, 3, 1005,

myproduct, 12-AUG-09

when i was expecting to see :

name , Number , Name , name, Product , Product ID , Product Name , Date

sysAdmin, t12345,my plan 08, Administrators, 3, 1005, myproduct, 12-AUG-09

so all data would be a single row in the 'csv' file rather then multiple rows in different columns with large spaces.

any suggestions ???

# 2  
Old 08-13-2009
This is a sqlplus problem, not unix.

Place this above the select statement:
COL FIRST_NAME       format a8 trunc   head 'fname'
COL LAST_NAME        format a8 trunc   head 'lanme'
COL TNUMBER          format a8 trunc   head 'tnum'
COL CAMPAIGN_NAME    format a8 trunc   head 'cname'
COL TEAM_TITLE       format a8 trunc   head 'ttitle'
COL PRODUCT_TYPE_ID  format a8 trunc   head 'prod typ'
COL PRODUCT_ID 		 format a8 trunc   head 'prod id'
COL PRODUCT_NAME     format a8 trunc   head 'prod name'
COL UPDATED_DT       format a8 trunc   head 'prod dt'

I gave each column 8 characters (a8). You get to correct that. If any columns are numeric us format 9999 - with enough 9's to cover the width of the column. OTherwise you get ##### instead.

the head clause is also yours to edit. The number of chars in the head clause needs to be no longer than the format statement allows the column to be.

Consider learning about sqlplus.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Grep two files: -F flag gives weird output

Hi Members, I'm confused with grep -F option. Goal is to get all the lines from file2 that have exact gene name from gene list (file one). File one has list of genes: File two has lot more information pertinent to genes in file one: I use three following commands: 1) grep -wf gene... (9 Replies)
Discussion started by: genome
9 Replies

2. UNIX for Advanced & Expert Users

Weird output from ps command

Hi Guys, I am regular Solaris user. I came across a weird problem that puzzled me. Hope you guys can help. I found that process's state(command & arguments) in two different variants of ps command is different. Can anyone explain how is this possible? bash-3.2$ ps -eLo pid,s,comm,args |... (2 Replies)
Discussion started by: brij123
2 Replies

3. HP-UX

[Solved] Weird 'ls -l' output

Hello folks, I've found an HP-UX server with a rare 'ls -l' output. Please see the attached file. Anybody knows how can I change the output to not have this extra tabulations? Thanks in advance! (10 Replies)
Discussion started by: carpannav
10 Replies

4. Shell Programming and Scripting

Weird tail output over ssh

Hello; Am trying to correct the formatting of tail output over ssh. Using the following code: echo "" > $FILE for BOX in $SERVERS do echo "Processing on $BOX" |tee -a $FILE echo "===============================" >> $FILE sudo ssh $BOX 'TERMINAL="vt100" /usr/bin/sh -s' <... (2 Replies)
Discussion started by: delphys
2 Replies

5. Shell Programming and Scripting

Traceroute script weird output

This script is giving weird output #!/bin/bash NETPATH=(`/bin/traceroute -n | awk '{print $2}'`) for i in "${NETPATH}" do echo $i done The output: to (7 Replies)
Discussion started by: thumbs
7 Replies

6. Shell Programming and Scripting

Find files older than X with a weird file format

I have an issue with a korn shell script that I am writing. The script parses through a configuration file which lists a heap of path/directories for some files which need to be FTP'd. Now the script needs to check whether there are any files which have not been processed and are X minutes old. ... (2 Replies)
Discussion started by: MickAAA
2 Replies

7. Shell Programming and Scripting

In bash getting weird output from function ?

My script- result="" times() { echo "inside the times function" result=8 echo "Inside function $result" return $result } result=$(times) echo "the value is "$? echo "the value of result $result" when I run I get this, why the value still remain 0. $ ./func the value is 0 the value... (5 Replies)
Discussion started by: boy18nj
5 Replies

8. UNIX for Dummies Questions & Answers

weird 'ls' output

Hi, Anyone knows why I can't display the contents of my directory and how to fix this? Thanks in advance for any advise. Deanne Double post. Continued here. (0 Replies)
Discussion started by: Deanne
0 Replies

9. Shell Programming and Scripting

weird echo output?

#!/bin/bash INPUT=$1 if then INPUT=0$1 TRACKNUMBER=$INPUT fi TRACKNUMBER=$INPUT echo "Track Number:" $TRACKNUMBER if then echo "File Does Not Exist!: split-track"${TRACKNUMBER}".wav" exit 0 fi CUEFILE="$2" (6 Replies)
Discussion started by: TinCanFury
6 Replies

10. AIX

Weird bootlist output

just setup a new system today - 9117 570, using HMC for console but not partitioned. I installed this system twice. the first time it started off as 5300-00, then updated to ML03. before the update, I believe I mirrored the rootvg, and then altered the bootlist, at that point, the display was... (4 Replies)
Discussion started by: davew1099
4 Replies
Login or Register to Ask a Question