HOME

Unix: Send mail in Excel 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 HTML type file using sqlplus's spool command.

Then we will convert this html file into excel format using uuencode command of Unix. Then will email it 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".

"set pagesize 50" is used to display the column names after every 50 rows. If you don't want column name to be displayed, set this to a big number like 50000.
"set linesize 100" is used to limit width of data displayed in a line to 100 characters.
"set echo off" prevents display of commands as they get executed. It will just help de-clutter the output.
"set markup html on spool on" tells that the SQL*Plus output will be HTML encoded and we will be spooling HMTL output.
"set heading on" controls printing of column headings. OFF will suppress header printing.
spool command is used to dump sql result into employee_data.html.

column command helps in controlling display of data displayed in different columns:
"column Employee_id heading 'Emp id'" will change column name to "Emp id".
"column Hire_date heading 'Date Hired' FORMAT A10" will change column name to "Date Hired" and data as 20 characters wide, including spaces.
"column Addr heading 'Address' FORMAT A100 WRA OFF WOR OFF TRU OFF". WRA (wrapping) wraps the string within the column bounds, beginning new lines when required. WOR (word_wrapped) left justifies each new line, skipping all leading whitespace, TRU truncates the string at the end of the first line of display.

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
set pagesize  50
set linesize 100
set echo off
set markup html on spool on
set heading on

column Employee_id  heading 'Emp id'
column Hire_date  heading 'Date Hired' FORMAT A10
column Addr heading 'Address'  FORMAT A100 WRA OFF WOR OFF TRU OFF

spool employee_data.html
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.			
dt=`date +'%m.%d.%Y'`					# taking current date in a variable, to append to the file name.
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.html ]				# Simple if statement to check if the spooled file employee_data.html is having any size (via -s option)
then
	uuencode employee_data.html employee_data$dt.xls | mail -s "Employee info " -c xyz@mail.com abc@mail.com
fi
rm -f employee_data.html					# Removing the file at last
fi
		

Here we converted generated html file into excel format using uuencode command and then 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.


Thanks...

TOP