Help needed for shell scripting for oracle.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Help needed for shell scripting for oracle.
# 1  
Old 05-14-2010
Question Help needed for shell scripting for oracle.

Hi,

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

1)a_p.ksh
Code:
#!/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

Code:
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  
Old 05-14-2010
#)in this log file i only want to have Count ie 155 and not any other information.

Code:
 
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

Code:
 
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  
Old 05-25-2010
Thanks..!!!

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

Eg:
Code:
           150
 
           200
 
            55


But I want it to be like
Code:
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...

Code:
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
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX shell scripting - help needed

Can anyone please help me to create the output file based on the attached input files using ksh script. I have 2 input files 1) inputfile1.txt 2) inputfile2.txt The script should accept 2 parametes: 1. Inputfile2.txt with 1-10,000 values ( sample file contains only 4 records) 2. Script... (5 Replies)
Discussion started by: vinus
5 Replies

2. Shell Programming and Scripting

Help needed in Korn Shell scripting

#! /bin/ksh while read line do if ] ; then echo "no data" continue; fi echo "performing operation on $line" done < prg.txt (3 Replies)
Discussion started by: Juhi Kashyap
3 Replies

3. Shell Programming and Scripting

Help needed with Shell scripting

Hi All, I need to split a flatfile based on it's first character, I am using the following script awk '{print > "TEST_substr($0,1,1).txt"}' PROVIDER.txt It is returning files TEST_1 and TEST_2 But I am not getting the .txt file extension. I need the files like TEST_1.txt and ... (1 Reply)
Discussion started by: sam35
1 Replies

4. Shell Programming and Scripting

Help needed in shell scripting

" how to write a script to send an error in a log file to a particular E-Mail ID " Can anyone help me in this ? Regards (3 Replies)
Discussion started by: himvat
3 Replies

5. Shell Programming and Scripting

Help needed - ksh shell scripting

Hi all, i m new to Unix shell scripting(ksh) i have a requirement, can anyone help me out in this.. spec: i need to move all the files landing in "X" directory to "Y" directory automatically everyday at a particular time.. (5 Replies)
Discussion started by: subbu
5 Replies

6. Shell Programming and Scripting

SHell Scripting Help Needed

Dear All, I have an input file like this interface Serial10/0/7:11.1 point-to-point description CLIENT:SA_INSTITUTO ANGLO MEXICANO Sitio Metepec 104452:0,165 bandwidth 64 ip vrf forwarding INSTITUTO-ANGLO ip address 192.168.148.217 255.255.255.252 no ip directed-broadcast frame-relay... (2 Replies)
Discussion started by: cskumar
2 Replies

7. Shell Programming and Scripting

Help needed - shell scripting

I have a file that has text in the format below ABC / Some text / ABC / Some text / ABC / Some text / ABC / Some text / ABC / Some text / How can I seperate the text between a pair of 'ABC' into seperate files ??? any information would be of great help. Thanks (8 Replies)
Discussion started by: garric
8 Replies

8. Shell Programming and Scripting

Shell Scripting with Oracle...

Hi guys, I have searched all the forums and searched the net as much as possible, but I carn't find any tutorials about shell scripting with Oracle. Has anyone come across any? Thanks for reading and in advance of posts, B14... (2 Replies)
Discussion started by: B14speedfreak
2 Replies

9. Shell Programming and Scripting

help needed in shell scripting......urgent

Dear friends, please help me to solve following problem. I'm running a frontend application from which i'll be invoking the shell script with arguments as given below -driver -w -p "ABC XYZ" -S -ds con -dn "abc xyz" i am getting $1=-driver $2=-w $3=-p $4="ABC $5=XYZ" $6=-S $7=-ds... (3 Replies)
Discussion started by: swamymns
3 Replies
Login or Register to Ask a Question