CODEWITHSUNDEEP
sqloracle-databaseshellemailspool
sachin bhandari
sachin bhandari

Reputation: 31

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

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.

Upvotes: 3

Views: 28627

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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 protected] < /u01/spool.csv

Upvotes: 2

Rajesh
Rajesh

Reputation: 2155

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

Upvotes: 0

Related Questions