send graph per mail from sqlplus

How to send a graph with a single command from your database to your mail in Unix?

I tried this (gnuplot is available for Solaris, AIX and most Unix derivates) :

echo '
set hea off pages 0 feed off
prom set title "salaries of EMP"
prom unset key
prom unset xtics
prom unset xlabel
prom set term png
prom
prom plot "-" with circle
select row_number() over (order by sal),sal from emp;
prom e
prom quit
' |
sqlplus -s scott/tiger |
gnuplot |
uuencode emp.png |
mailx [email protected]

The picture is sent as attachment.

If you want to embed your png in an HTML mail, use sendmail

/usr/sbin/sendmail -t <Below a graph...


--MSGBOUNDARY--
--FILEBOUNDARY
Content-Type: image/png
Content-Disposition: inline; filename="png.png"
Content-Transfer-Encoding: base64
Content-Id:

iVBORw0KGgoAAAANSUhEUgAAAoAAAAHgCAMAAAACDyzWAAABIFBMVEX///8AAACgoKD/AAAAwAAA
gP/AAP8A7u7AQADIyABBaeH/wCAAgEDAgP8wYICLAABAgAD/gP9//9SlKir//wBA4NAAAAAaGhoz
MzNNTU1mZmZ/f3+ZmZmzs7PAwMDMzMzl5eX////wMjKQ7pCt2ObwVfDg///u3YL/tsGv7u7/1wAA
/wAAZAAA/38iiyIui1cAAP8AAIsZGXAAAIAAAM2HzusA////AP8AztH/FJP/f1DwgID/RQD6gHLp
lnrw5oy9t2u4hgv19dyggCD/pQDugu6UANPdoN2QUEBVay+AFACAFBSAQBSAQICAYMCAYP+AgAD/
gED/oED/oGD/oHD/wMD//4D//8DNt57w//Cgts3B/8HNwLB8/0Cg/yC+vr7MAY9LAAAKJElEQVR4
nO3d0XaiSBSGUV15/3ee7unEoKLWwYIfrL0vJhNDmLn4FgQoPacTAAAAAAAAAAAAbOl8Ps++uvAX
oWhhgH+2+N3o/L/T/9+f/33X8f+Qj7awlatf+/7m75fvMBXII9/Hp5/D1M8//2Vz+dnMlje/frXL
y48EyHOTPK5LmqZz+cddT5cXb6M8TwJ0Cuax6eHqND0Cnq9ivPR0f7CbffF0VSw8dhXR7cHussXN
YW/ud29+LkBeuz2xPg/w0SlYgCw1ubQ4T06zk1Pw9MXfX7j99Ztd/qYqQAAAAAAAAACGMP983cpJ
NjL7rgVrx9nKZHGvANneb3Lnuy+XTaCsFuF5LsKfH3ctnuP5Kr38TznAx6dgAY5uzQBnyxMgV2ZT
e9pfezXfp+vZL8Vd8anWDPA1AQ5PgGTNxPa8PwHSkwDJusvtRX8CpKuvr+ff3xEgfU2Te5mfAOnv
6193Xw35CZBVfLXVdxIgYQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIESJQAiRIg
UQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIESFR9VNf5fmprfVfwT6GayWQ4k5Lo
pL2a8/nnCChAuimM6roeEjcZmHnZojZ8k9FVipkc7IxrpZ/2AC+1mpZJP+WrYAHSUzFA41rpy41o
ogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJ
kCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEmVQDVEL5gUb1UU/
5XnBAqSn+rzg892XyxbmBVOxYF7wfX2OgLyjOC/YKZi+zAsmym0YotyIJkqARAmQKAESJUCiBEiU
AIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAmQKAES
JUCiBEiUAIkSIFECJEqARAmQKAESJUCiBEiUAIkqzIr7GRNyPy6kuCu4KM+Ku/3OpCTeUR7Vdf6p
ToB0UD4F/w5uNa6V91SL+T3/GtdKJ0sDdAqmiwUXIQKkn+LfgMa10pcb0UQJkCgBEiVAogRIlACJ
EiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVAogRIlACJEiBRAiRKgEQJkCgBEiVA
ogRIlACJEiBRAuR0+vqj53YFAhzed1Qv22rdrkaAg5vm9Cyt1u2qBPjBXp8xb3/+aPvW7eoE+Kma
zph3P3sUYON2dQL8TG1nzJkfzG7but0CAvxEzWfWppcESFHjGfOt2DoVaE7IB3qroZ0GaFLSgXxi
gP9vK8BDePPPuMVXxovUx7VeT8o0rnV/DhPggnGt9/U5Au7OYQL8yyn44zT3cqgAXYQcxkb3/NyG
GdfzZ7wfGuCmu+Kxl894Bch6Wp7xbnLPz6O4EbU94xUgK2m7QC2cMS3HoqD/AcuCVArWOGNakk+r
N863T1dFe1MSTQoBFs+Y3pZJg8rtuLXOmN0J8DCKj2TXOWN2J8DDqK8JWOGM2Z0AD2PVRSkxAjwM
AW63K+asuSYgRoDHIcDNdsUcAW62K2atuCglRoAHIsCtdsW89VZFxQjwSNZbFRUjwGM5zDPeVgI8
moM8420lwAM6wjPeVgIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIEuB+ftMilmQB3
4sOW+TUT4C583ELnZgLcgQ98q0ez8rDC36+XF6u74sYHvtmt2YJRXdffGdX1tk98u2+zcoA/U1sF
2I0AW7edTik0L7iXj/zIlya1Yi7bmhfc17gB/lW4CJn8m1NwRwJs2m7u6kOAHXzmB582aw9w7v6L
2zDvE+AOdzUSAe5wVyMR4A53NRQXIfvb1VAEuL9dDUWA+9vVWDyK292uxiLA3e1qMJZj7W1Xg7Eg
dW+7Go4l+fva1YC8KWlPuxqTt2XuZlcMQ4BECZAoARIlQKIESJQAiRIgUQIkSoBECZAoARIlQKIE
SJQAiRIgUQIkSoBECZAoARIlQKIESJQAiRIgUQIkSoBECZCo4rhWc0Loa8G4VpOS6EeARNXHtZ7v
vlx+bFwrFQvGtd7X5wjIO4rjWp2C6cu4VqKMayXKjWiiBEiUAIkSIFECJEqARAmQKAESJUCiBEiU
AIkSIFECJEqARAmQKAESJUCiBEiUAIkSIFECJEqARAlwfV9/pP8fdkuAK/uOT4MPCHBV0+wkOEeA
K7pNToL3BLiiu94EeEeA65nJTYG3BLgeATYQ4GpmY1PgDQGuRoAtBLjcixvMAmwhwIVe3mB+8LoC
rwlwkYYbzAJsIsAFmm4wC7CJABdousEswCYCrGu8v+cipIUA6wTYkQDLWsMSYIvKqK7To3Eh1V0d
W3NYHsU1KIzq+lfc9zcDT0oSYE/Nk5ImR8CxAyxc3VqO9Vr5FDwztfWywRDjWisBWpD6VHVc6++/
jDyutXR/z5L8V5YG6BTc9rI3Jb2w+BQ8bID12yvelvmEca1l7u/15EZ0mQB7EmCd+3sdCbBOgB0J
8M7rawY3mPsR4LWmuyZuMPcjwKnm+8ZuMPciwF+lA5sbzH0I8Ff1Tzs3mDs4foDNGbza0MVtwsED
bD4RNmwowIRDB9j1msEDjogDB9h8zdC2oQAjjhzgyxdKGwow4rgBNv/J1rah95FnCPDp7wpwbYcN
8J33ps29KMAMAT7bTICrE2B5h/R01ACbD1jvbSjAtQnw6YYCXJsAiy/RlwCLL9HXfgPs9Rn0bzz2
0N/6dhrg68UrawRopf32dhlg18UrlYsLK+03t8MAGw9EnR/FXf5jVtpvao8Bvnzh0asdAjxZab+t
/QW4PKz3lmMRceQA+y5IJWJ3Aa5yzeDiYrcOHWDhmsHFxU4dPMBT4ZrBxcUe7S1Ay/IGI0CiBEiU
AInaPMAFn9Civw+2bYDLPqFFgB9sywCbbgcLcCzbBdj6QMzC5KFUR3UtH1TTuiRAgENZMC942aiu
9q4sXhlJeV7wBgFavDKQ+rDC61GtrfOCS9cWFq+MYcG84Pv6Go+AxYtbi1dGsdW41vrdFYtXhrBR
gJ6wMW+jecECZN5GN6IFyDwBErXVoziPeJnVM8AunyXEWLoeAXt8lhBj6X0Kriyz0h8r/A347mcJ
MZQVLkIscqHdGlfBb36UCyPZLkCLXJixyn3A9z/KhVFsG+DJIheubR4gTK3zKE6BNBIgUQIkSoBE
uQghSoBECZCoLR/FwR0BErXdciyYsdmCVJiz2ZJ8mLPVm5Jg1lZvy4RZe5sTwmAESJQAiRIgUQIk
SoBECZAoARIlQKIESJQAiRIgUcVq5seFLNoVnBYEePlSnJYJc5YcAQVINwuOgE/HtULNgmYfjGuF
TTyalgmrezauFdb3fda+/gIAAAAAAAC0+Q9+0RhW4FaMYwAAAABJRU5ErkJggg==
--FILEBOUNDARY--
EOF

Sendmail syntax and more tips on : Sending Emails With Sendmail – Part 3

6 thoughts on “send graph per mail from sqlplus

  1. Gokhan Atil

    Hi Laurent,

    Thanks for sharing this useful code! I have tested the code and see that gnuplot 4.0 which comes with Oracle Linux 4 and 5 (probably also other linux distors) does not support “circle” option and “plot – with” line gives error. So Linux users should “points pointtype 6 pointsize 3” instead of “circle”.

    I also noticed that uuencode is not installed on Oracle Linux 4 and 5 by default. uuencode is packaged in sharutils. So Oracle Linux users might need to install the sharutils package.

    The following code should work on Oracle Linux:

    echo '
    set hea off pages 0 feed off
    prom set title "salaries of EMP"
    prom unset key
    prom unset xtics
    prom unset xlabel
    prom set term png
    prom
    prom plot "-" with points pointtype 6 pointsize 3
    select row_number() over (order by sal),sal from emp;
    prom e
    prom quit
    ' | sqlplus -s scott/tiger | gnuplot | uuencode emp.png
    | mailx root@localhost

    Regards

    Gokhan Atil

  2. Pingback: Laurent Schneider » Generate network graph from command line

  3. Pingback: Generate network graph from command line « Ukrainian Oracle User Group

Comments are closed.