How to write a shell script to run a sql query and send the query results in table format in email?

Question

I need to run a SQL query using SHELL script in PUTTY and send the results in email body in table format with headers along with a message line on top. I have written this so far but I am not able to get results in table format.

#! /bin/ksh
sqlplus -s user/pass @sid > /dev/null << EOF 
whenever sqlerror exit 4;
set newpage 0
set space 0
set feedback off
set head off
set echo off
set pagesize 0
set serverout on
set trimspool on
set linesize 3000
spool $CSVFILE
select * from table;
spool off;   
exit 0;
EOF
mailx -s "Subject Line" [email protected] < $CSVFILE
exit 0

The SQL is running fine and data is being saved in CSVFILE. The result I want should be in table format sent to the email address in email body and also involve a message line above table, something like so: EMAIL EXAMPLE WITH ROUGH TABLE

There are no errors. But no headers in CSVFILE even after putting SET HEAD ON. I have also tried the 'mail -a (content type)' solution but '-a' gives error.

Answer 1

Shell is not a good tool for it.

The better solution would be to dump the data into the file however you can, then reformat the file with awk, perl, or even python.

Even better - write the whole thing in a perl or python - connect to database, execute SQL statement, download and format data.

Another way, is to find on the net (or write yourself) an export2csv tool (there are many versions for it on the github). Just find the one you can run in your environment. They usually have an easy way to define SQL statement and output file.

If you have ODBC connection to your database (unixODBC works perfectly), and can compile C++ code, you can try my nisql: https://github.com/White-Owl/nisql

Answer 2

Your problem is in the way sqlplus is set. Add sets like:

set lines 256
set trimout on
set tab off

to print table like this:

Name   |    Address    |    Phone    |
-------+---------------+-------------+
name1  |    address1   |    phone1   |
name2  |    address2   |    phone2   |
name3  |    address3   |    phone3   |

and then you last command will send pretty formatted mail.

To add lines before the table you can use prompt:

prompt Hi,
prompt Please check results below:
prompt

As you understand these command should be before select which generate the table.