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.
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 firstname.lastname@example.org email@example.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 firstname.lastname@example.org, cc: is email@example.com, subject is specified via -s option.