Script SH che esegue query e invia email con allegati
Il seguente script sh lancia una serie di file sql.
I file sql contengono delle query ed estraggono il risultato in un file excel.
Lo script sh rinomina i file excel aggiungendo la data di creazione e li invia via email usando il comando "mutt".
ls -lrt /u01/app/oracle/work
-rwxr-xr-- 1 oracle oinstall 340 Dec 11 11:17 lancio_query.sh
-rwxrw-r-- 1 oracle oinstall 1062 Dec 11 10:33 query1.sql
-rwxrw-r-- 1 oracle oinstall 258 Dec 11 10:33 query2.sql
-rwxrw-r-- 1 oracle oinstall 266 Dec 11 10:34 query3.sql
-rwxrw-r-- 1 oracle oinstall 276 Dec 11 10:34 query4.sql
-rwxrw-r-- 1 oracle oinstall 274 Dec 11 10:34 query5.sql
-rwxrw-r-- 1 oracle oinstall 1073 Dec 11 12:46 query1_gd.sql
-rwxrw-r-- 1 oracle oinstall 231 Dec 11 12:47 query2_gd.sql
-rwxrw-r-- 1 oracle oinstall 237 Dec 11 12:47 query3_gd.sql
-rwxrw-r-- 1 oracle oinstall 241 Dec 11 12:47 query4_gd.sql
-rwxrw-r-- 1 oracle oinstall 241 Dec 11 12:47 query5_gd.sql
I file sql contengono delle query ed estraggono il risultato in un file excel.
Lo script sh rinomina i file excel aggiungendo la data di creazione e li invia via email usando il comando "mutt".
ls -lrt /u01/app/oracle/work
-rwxr-xr-- 1 oracle oinstall 340 Dec 11 11:17 lancio_query.sh
-rwxrw-r-- 1 oracle oinstall 1062 Dec 11 10:33 query1.sql
-rwxrw-r-- 1 oracle oinstall 258 Dec 11 10:33 query2.sql
-rwxrw-r-- 1 oracle oinstall 266 Dec 11 10:34 query3.sql
-rwxrw-r-- 1 oracle oinstall 276 Dec 11 10:34 query4.sql
-rwxrw-r-- 1 oracle oinstall 274 Dec 11 10:34 query5.sql
-rwxrw-r-- 1 oracle oinstall 1073 Dec 11 12:46 query1_gd.sql
-rwxrw-r-- 1 oracle oinstall 231 Dec 11 12:47 query2_gd.sql
-rwxrw-r-- 1 oracle oinstall 237 Dec 11 12:47 query3_gd.sql
-rwxrw-r-- 1 oracle oinstall 241 Dec 11 12:47 query4_gd.sql
-rwxrw-r-- 1 oracle oinstall 241 Dec 11 12:47 query5_gd.sql
vi lancio_query.sh
#!/bin/bash -x
export QBILLDIR=/u01/app/oracle/work
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export ORACLE_SID=DBTEST
export OID=oracle
orario=$(date +"_%Y%m%d_%H%M")
for i in {1..5}
do
sudo -u $OID $ORACLE_HOME/bin/sqlplus "/as sysdba" @$QBILLDIR/query${i}.sql
mv $QBILLDIR/QBILL_query${i}.xls $QBILLDIR/QBILL_query${i}${orario}.xls
sudo -u $OID $ORACLE_HOME/bin/sqlplus "/as sysdba" @$QBILLDIR/query${i}_gd.sql
mv $QBILLDIR/QBILL_query${i}_gd.xls $QBILLDIR/QBILL_query${i}${orario}_gd.xls
done
echo "In allegato il risultato delle query di QBILL di oggi $(date +"%Y%m%d") e del $(date -d "1 days" +"%Y%m%d")" | \
mutt -a $QBILLDIR/QBILL_query1${orario}.xls \
-a $QBILLDIR/QBILL_query1${orario}_gd.xls \
-a $QBILLDIR/QBILL_query2${orario}.xls \
-a $QBILLDIR/QBILL_query2${orario}_gd.xls \
-a $QBILLDIR/QBILL_query3${orario}.xls \
-a $QBILLDIR/QBILL_query3${orario}_gd.xls \
-a $QBILLDIR/QBILL_query4${orario}.xls \
-a $QBILLDIR/QBILL_query4${orario}_gd.xls \
-a $QBILLDIR/QBILL_query5${orario}.xls \
-a $QBILLDIR/QBILL_query5${orario}_gd.xls \
-s "Query QBILL" bat@man.com \
-c super@man.com
-c super@man.com
exit
vi query1.sql
set markup html on
spool /u01/app/oracle/work/QBILL_query1.xls
-- query 1
SELECT.......from ....where .... = to_number(to_char(sysdate, 'yyyymmdd'));
set markup html off
exit
vi query1_gd.sql
set markup html on
spool /u01/app/oracle/work/QBILL_query1_gd.xls
-- query 1
SELECT.......from ....where .... = to_number(to_char(sysdate, 'yyyymmdd'));
set markup html off
exit