Help needed for shell scripting for oracle.

Shell Programming and Scripting
1

Hi,

Please see contains both files created for automating the data from oracle through shell.

1)a_p.ksh

#!/bin/ksh
LOG=/home/A_P.log
MESSAGE=/home/MESSAGE_A_P.txt
mail_list=/home/AP_MAIL_LIST.txt
data=/home/spooled_A_P.log
echo "`date` Starting execution for A_P COUNT" > $LOG
 
RESULT=`$ORACLE_HOME/bin/sqlplus sa/'pwd'@im << EOF
        set echo on;
        set feedback on;
        @/home/A_P.sql
`
echo $RESULT >> $LOG
 
echo " `date` End of step generation of A_P COUNT has completed" >> $LOG
 
mailx -r"id@mail.com" -s " A P  Count `date` "  `cat $mail_list`<$MESSAGE
echo "mail has been sent" >> $LOG

2)spooled_A_P.log

SQL> #set serveroutput on;
SQL>
SQL> select count(*) from a_p;
  COUNT(*)
----------
    155
1 row selected.
SQL>
SQL> spool off;
 
SQL>

Now,
#)in this log file i only want to have Count ie 155 and not any other information.
##)in the script file how to pull data from this log file so that it gets into the body of the mail along with the contains of MESSAGE file and also want to write some text ie, THE count from A_P table is <data from log file> and message file contains signature.

Please help me out with this.

2

#)in this log file i only want to have Count ie 155 and not any other information.

 
Use "sqlplus -s"  to connect to oracle and use the "SET " options available with SQL to turn everything off(except the output).
 
set echo off
set heading off

##)in the script file how to pull data from this log file so that it gets into the body of the mail along with the contains of MESSAGE file and also want to write some text ie, THE count from A_P table is <data from log file> and message file contains signature

 
echo "THE count from A_P table is $RESULT" > temp_file
cat temp_file message_file > temp1
mv temp1 message_file 
rm temp_file temp1
mailx -r ...(use the message_file)
3

Thanks..!!!

One more problem....I am pulling data into a text file but along with data a new line and tab is coming...

Eg:

           150
 
           200
 
            55

But I want it to be like

150
200
55

so that can use it in a variable each separately as i need to send this in a tabular form as mail using script only.
Also please let me know how to assign same and then arrange it in table form, it will be around 20 or so numbers and aranged like this...

table name                   count1                count2             count3
--------------------------------------------------------------------------------
table1                          11111                 11111               11111
table1                          11111                 11111               11111
table1                          11111                 11111               11111
table1                          11111                 11111               11111
table1                          11111                 11111               11111
table1                          11111                 11111               11111

Please help