Unix: Send mail in Text format using Oracle SQL*Plus

Contact Me

For this, you need to know and use sqlplus and Unix shell scripting (See write-ups on Unix and SQL*Plus for more info.).

We will call a sql script named my_query.sql, which will dump data into a text file using sqlplus's spool command.

Then we will just email this files's content to the desired email addresses.

Here is the code that goes into sql file. We used "set feedback off" to prevent diplaying message like "n rows processed". We use spool command to dump sql result into employee_data.txt. At last, we use exit to exit from sqlplus. Have a look at Unix shell script to see how sqlplus is used to invoke our sql script file named my_query.sql.

set feedback off;
spool employee_data.txt
select * from employees;
spool off;

Below is the sample shell script code to call above sql file and mailing the result in simple text format.

#!/bin/ksh						# this tells that we are using korn shell.						
sqlplus $DB @my_query.sql 1>/dev/null			# 1 here is file-descriptor for standard output. 
							# It re-directs any output from sql file, to null device, to avoid anything to be displayed on screen.
if [ -s employee_data.txt ]				# Simple if statement to check if the spooled file employee_data.txt is having any size (via -s option)
out_tot='wc -l employee_data.txt.txt`			# counting number of lines in the file
echo "\n" >> employee_data.txt				# Appending a blank line in file using append (>>) operator. Echo is similar to printf in C language.
echo "Total customers:" $out_tot >> employee_data.txt	# Appending customer count file. 
if [ $cus_out_tot -ge 2 ]				# If test to check if number is > 2
  cat outage_stats.txt | mail -s "Put subject here !" -c xyx@mail.com abc@mail.com	
  echo "No Mail sent"	>> employee_data.txt	
cat employee_data.txt
rm -f employee_data.txt					# Removing the file at last

Here we used mail command for mailing file's contents. Pipe (|) operator feeds cat command's output to mail command's body.

To: is abc@mail.com, cc: is xyz@mail.com, subject is specified via -s option.