How do i execute oracle Query using shell Scripting and send the results in the email

sql oracle shell email spool
sachin bhandari picture sachin bhandari · Jun 5, 2015 · Viewed 25.2k times · Source

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

Lalit Kumar B picture Lalit Kumar B · Jun 5, 2015

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

Related questions

suppressing messages while running sql queries in a script

I am trying to write a simple query-script that gets me cnt of rows in a table. However I am facing problem to suppress all sorts of oracle messages. All I am interested is the output: Here is my script: #!/…

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More