Script to create a flat file and email it daily.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Script to create a flat file and email it daily.

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Post 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

  2. #2
    Join Date
    Jan 2006
    Posts
    4
    #!/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
  •  


Click Here to Expand Forum to Full Width