Hi I want to write a script, which will create a flat file containing data form a particular table. And this flat file will be sent to particular email id as an attachment on daily basis.
Any help in this regard will be appreciated.
Chetan
Printable View
Hi I want to write a script, which will create a flat file containing data form a particular table. And this flat file will be sent to particular email id as an attachment on daily basis.
Any help in this regard will be appreciated.
Chetan
#!/bin/sh
# $Header: $
#
TNS_ADMIN=/u002/oracle/env01ora/8.0.6/network/admin/
export TNS_ADMIN
ORACLE_HOME=/u002/oracle/env01ora/8.0.6
export ORACLE_HOME
PATH=/usr/users/appenv01/bin:/usr/local:/usr/local/bin:/usr/bin/X11:/u002/oracle/env01ora/8.0.6/bin:/u002/oracle/env01ora/8.0.6/network/admin/:${PATH:-/usr/bin:.}
export PATH
#menage des fichier du traitement
rm -f /tmp/concrun.sql
rm -f /tmp/concrun.log
#création du fichier sql
printf "
REM dbdrv: none
REM +=======================================================================+
REM | FILENAME
REM | concrun.sql
REM |
REM | DESCRIPTION
REM | script de vérification pour les programmes concurent
REM | qui sont en cours depuis plus d'une heure
REM |
REM *=======================================================================*
--mettre les parametre de langue Ã* CANADIAN FRENCH
EXECUTE FND_GLOBAL.set_nls_context (p_nls_language => 'CANADIAN FRENCH', p_nls_date_format => null, p_nls_date_language => null, p_nls_numeric_characters => '.,', p_nls_sort => null, p_nls_territory=> null);
SET LINE 132
Set feedback off
Set trimspool ON
set head off
spool /tmp/concrun.log
SELECT
'=======================================================' || chr(10) ||
'Programme Concurent (En cours) depuis plus d''une heure' || chr(10) ||
'=======================================================' || chr(10) ||
'Request Id : ' || request_id || ' Parent : ' || REPLACE(parent_request_id,'-1',NULL) || chr(10) ||
'Concurrent Program : ' || user_concurrent_program_name || chr(10) ||
'Demandeur : ' || requestor || chr(10) ||
'Début : ' || to_char(actual_start_date,'YYYYMMDD HH24:MI:SS') || ' Sysdate : ' || to_char(sysdate,'YYYYMMDD HH24:MI:SS') || chr(10) ||
'Phase : ' || phase_code || ' Status : ' || status_code
FROM fnd_conc_req_summary_v
WHERE ( PHASE_CODE = 'R'
OR STATUS_CODE = 'R')
and ACTUAL_START_DATE <= (sysdate - (1/24))
ORDER BY request_id DESC;
SPOOL OFF
exit
" > /tmp/concrun.sql
#récupère le mots de passe apps
PASSWD="manager"
priv_connect="apps/$PASSWD@prd"
PASSWD=" "
#connection a sqlplus
/u002/oracle/env01ora/8.0.6/bin/sqlplus "$priv_connect" @/tmp/concrun.sql /nolog
priv_connect=" "
FILE="/tmp/concrun.log"
if test -s $FILE
then
#envoie du mail
MailTo=email@email.com
Hostname=`hostname`
Silo=`whoami | tr A-Z a-z | cut -c 4-8`
TimeStamp=`date +%C%y'/'%m'/'%d' '%a' '%H':'%M`
LogFile=/tmp/concrun.log
#echo $MailTo; echo $Silo; echo $Hostname; echo $TimeStamp; echo $LogFile;
mailx -s "Le $TimeStamp - $Silo sur $Hostname URGENT: Courriel du log $LogFile" $MailTo < $LogFile
fi;
#
printf "Exiting with status $exit_code\n"