Formatting error while creating report using Shell Script

Question

HI all I am fairly new to Shell Scripting .. I was testing a sample report in which I was trying to spool the output of sql query embedded in shell script. here is my code

#!/bin/sh

ORACLE_ACCESS=hr/hr@xe
# Report file location
FILE_LOCATION=/home/$USER/Documents/loaddata/
#File Name
FILE_NAME=Report_`date +%y%m%d`.csv

sqlplus -s $ORACLE_ACCESS <<EOF 2>log.txt
set serveroutout on
set echo off
set first_name format A120

spool ${FILE_LOCATION}${FILE_NAME}
select first_name,last_name 
from employees;
exit;
EOF

sql_error=$?
if [$sql_error !=0]; then
   echo "Error"
   exit 1
fi

However I am getting a pretty badly formatted output.

below is the sample

FIRST_NAME LAST_NAME

Ellen Abel
Sundar Ande
Mozhe Atkinson
David Austin
Hermann Baer
Shelli Baida
Amit Banda
Elizabeth Bates
Sarah Bell
David Bernstein
Laura Bissot

FIRST_NAME LAST_NAME

Harrison Bloom
Alexis Bull
Anthony Cabrio
Gerald Cambrault
Nanette Cambrault
John Chen
Kelly Chung
Karen Colmenares

My intended output should look something like below

First_name Last_name Atlas Levine John Doe

can somebody help?

Community · Accepted Answer · 2017-05-23 10:32:25Z

0

Is your intended output like

First_name Last_name
Atlas Levine
John Doe

If so you can use something like set pagesize 10000 or something large so that the header doesnt get repeated. If you want to skip the header you can just set it to 0 You can refer the answers and links in Formatting output of queries in SQLPlus along with many other similar questions

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Jun 5, 2014 at 10:27

Pradhyoth

1481 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Formatting error while creating report using Shell Script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related