How do I execute oracle query using shell scripting and send the results in the email

Question

I am new to Unix scripting. I have an oracle DB which has a username, password, hostname, port and servicename. I want to connect the database and run the query and in the end the result should be email to me or other people. Result should be displayed in the body of an email.

Answer 1

This is a sample shell script to connect through SQL*Plus, SPOOL the output and send it as email in the message body.

#!/bin/sh -- or bash or ksh

sqlplus -s /nolog <<EOF
CONNECT username/password@sid 
SPOOL /u01/spool.csv
--do something
SPOOL OFF
EXIT;
EOF

mail -s "Subject" email@address.com < /u01/spool.csv

Answer 2

PFB functionality breakup to connect oracle, email query result(as body) using shell Script with example :

• Connect to Oracle Database
• Set Headers for formatted output
• Run sql query and store result to temp file(query_output.dat)

http://www.folkstalk.com/2012/06/connect-to-oracle-database-in-unix.html

• Send output as body. Ex

  mailx -s "subject" mail_address <query_output.dat
  rm query_output.dat