-
Script to create a flat file and email it daily.
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"
Make my words a crime, I will cry louder. Silence my voice, I will find another.
Make my voice a crime, I will create a new one. Hunt me down, I will find a new place to hide.
Lock me away, Ten willrise to take my place. You cannot stop me, I and my kind are forever.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|