Hi All,

I am spooling the data some sql queries into a single file but wanted to know how to format the data of the file generated by spool.

#!/bin/sh
unset -f USAGE
USAGE () {
clear
echo "############################USAGE#######################\n"
echo "Incorrect number of parameters\n"
echo "Usage: ${1} <DBUser>"
echo "Usage: ${2} <DBPassword>"
echo "########################################################\n"
exit -1;
}
 
if [ $# -ne 2 ]; then
USAGE
fi
 
username=$1
password=$2
 
sqlplus -s $username/$password@DB_TGT01 << END
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET ECHO OFF
set LINESIZE 5000
set TRIMSPOOL on
spool reco-counts-SRC_TGT.txt
 
select TABLE_NAME "Table_Name", ROWCOUNTS "Source_CU01" from DBS_Table_counts@DB_SRC order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU01" from DBS_Table_counts order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU04" from DBS_Table_counts@DB_TGT04 order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU06" from DBS_Table_counts@_TGT06 order by TABLE_NAME;
 
select ROWCOUNTS "Target_CU07" from DBS_Table_counts@_TGT07 order by TABLE_NAME;
spool off;
exit;
END

Col1   Col2   Col3   Col4   Col5   Col6   Col7   Col8
A       4       1       1       1       1       4       0
B       26      5       8       4      9       26      0
C       70      1      10      55     0       67      -3
 
where Col1 is Table_Name from 1st query
Col2 is Source_CU01 from 1st query
Col3 is Target_CU01 from 2nd query
Col4 is Target_CU04 from 3rd query
Col5 is Target_CU06 from 4th query
Col6 is Target_CU07 from 5th query
Col7 is values of (Col3+Col4+Col5+Col6)
Col8 is values of Col7 - Col2