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; exit
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) then 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 then cat outage_stats.txt | mail -s "Put subject here !" -c email@example.com firstname.lastname@example.org else echo "No Mail sent" >> employee_data.txt fi cat employee_data.txt rm -f employee_data.txt # Removing the file at last fi
Here we used mail command for mailing file's contents. Pipe (|) operator feeds cat command's output to mail command's body.
To: is email@example.com, cc: is firstname.lastname@example.org, subject is specified via -s option.