Hi,
Iam facing following error.please see the below script and error.Iam unable to attach error message to mail and its not sending mail also.
vi Expedites_test.ksh
"Expedites_test.ksh" 49 lines, 2276 characters
#!/bin/ksh
#:---------------------------------------------------------------------------
#:Name : Expedites_test.ksh
#:Path : /bin
#:Author : Venu Narapureddy
#ate : 08-mar-2007
#escription : Script to extract and refresh Expeditesdata.
#:Frequency of job :Monthly
#:History:
#ate: User: Modifications:
#:---------------------------------------------------------------------------
#:02-Jun-2006 Venu Narapureddy Initial version.
#:**************************************************************************
ORACLE_SID=ODSDEV export ORACLE_SID
ORACLE_HOME=/oracle/product/current export ORACLE_HOME
TNS_ADMIN=/var/opt/oracle export TNS_ADMIN
TOP='/users/qtcbi/metrics'; export TOP
MODULE_NAME=Expedites_test.ksh;export MODULE_NAME
ORAENV_ASK='NO';export ORAENV_ASK
LOG_FILE="$TOP/log/$MODULE_NAME.$(date+%m%d.%H:%M)";export LOG_FILE

NORMAL_MAIL='vnarapur'; export NORMAL_MAIL
ERROR_MAIL='vnarapur'; export ERROR_MAIL
UNAME='ccaisadm'; export UNAME # ORacle User ID
PWORD='changeit'; export PWORD # Oracle Password

echo "METRIX :Expedites : Extract of Expedites " |/bin/tee -a $LOG_FILE
echo "Current System Date Is : $(date) " |/bin/tee -a $LOG_FILE
echo "Script Is : $MODULE_NAME " |/bin/tee -a $LOG_FILE
echo "Working Directory Is : $(pwd) " |/bin/tee -a $LOG_FILE
echo "Host Machine Is : $(hostname) " |/bin/tee -a $LOG_FILE
echo "Oracle Instance Is : $ORACLE_SID " |/bin/tee -a $LOG_FILE
echo "$(date) Daily Cron Started " |/bin/tee -a $LOG_FILE
echo "$(date) Expedites extraction process Started " |/bin/tee -a $LOG_FILE

$ORACLE_HOME/bin/sqlplus $UNAME/$PWORD@$ORACLE_SID @$TOP/sql/Expedites.sql 25-oct-2006 29-oct-2006 |/bin/tee - $LOG_FILE

errorcount=$(grep ORA - $LOG_FILE | wc -l)
if [[ $errorcount -gt 0 ]]; then
echo "$(date) Process Failed " |/bin/tee - $LOG_FILE
/usr/bin/mailx -s "METRIX : EXPEDITES : $ORACLE_SID $ (date)Expedites process Failed " $ERROR_MAIL < /tmp/expedites_test.lst >/ dev/null
exit 1
else
echo "$(date) Process Done " |/bin/tee -a $LOG_FILE
/usr/bin/mailx -s "METRIX:EXPEDITES :$ORACLE_SID : LOG File for$(date) " $NORMAL__MAIL < $LOG_FILE >/
dev/null
exit 0
fi

"Expedites_test.ksh" 49 lines, 2288 characters
$ Expedites_test.ksh
./Expedites_test.ksh[20]: date+%m%d.%H:%M: not found
METRIX :Expedites : Extract of Expedites
Current System Date Is : Mon Mar 12 04:39:04 PDT 2007
Script Is : Expedites_test.ksh
Working Directory Is : /users/qtcbi/metrics/bin
Host Machine Is : wonder
Oracle Instance Is : ODSDEV
Mon Mar 12 04:39:04 PDT 2007 Daily Cron Started
Mon Mar 12 04:39:04 PDT 2007 Expedites extraction process Started

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 12 04:39:04 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

DOC> Report Name : Expedite VS EE Report
DOC> File Name : Expedite_VS_EE_Automated.sql
DOC> Created by : Pushkar Kumar Singh
DOC> Business User : Christos Matheou (cmatheou)
DOC> Input Parameters : Date range
DOC> 1.Start Date
DOC> 2.End Date
DOC> 3.Output File name
DOC> Running Frequency : Monthly/Quarterly/Adhoc Request
DOC> SQL File Location in LL :
DOC> OutPut File Location in LL :
DOC> Output file name : User Defined
DOC> Query Execution Step/Time : 1. Insert all records (ODS) in temp Table for the specified
DOC> date range
DOC> ( Estimate for 1 month) 2. PL/SQL script execution
DOC> PL/SQL script performs following
DOC> 3. Final summary Output generation
DOC> (Avg Time:2-2.5 mins)
DOC> Database Used : Run same sql script against ODS database
DOC> Schema Used : ccaisadm
DOC>
DOC>*/
DOC> As index is not available, the value for the Total Product SS Booked is not ptrovided in the CSV file and a hard code
DOC> is used in the procedure.
DOC> To make verification easier,i am providing values for few months:
DOC> 27-Aug-2006 to 23-Sep-2006: 133897
DOC> 24-Sep-2006 to 28-Oct-2006: 189540
DOC> 29-Oct-2006 to 25-Nov-2006: 142997
DOC>Because of the aforesaid reason,code for the Sections Total Expedtes (Expedite Rate) by Theatre by Type and Cycle time / handle Time By Type and Theater has been not written fully.
DOC>
DOC> All hard coded--including Section names--used in the code are as per currently being used the Expedite vs EE excel sheet.
DOC>
DOC> PLS_INTEGER,instead of BINARY_INTEGER,is used as the datatype for those variables which are used in the arithmetic operations.
DOC>************************************************************************************************ **********************/
old 293: WHERE FISCAL_MONTH_START_DATE='&1'
new 293: WHERE FISCAL_MONTH_START_DATE='25-oct-2006'
old 295: FISCAL_MONTH_END_DATE='&2';
new 295: FISCAL_MONTH_END_DATE='29-oct-2006';
old 310: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 310: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 312: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS');
new 312: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS');
old 319: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 319: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 321: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS');
new 321: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS');
old 329: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 329: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 331: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 331: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 375: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 375: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 377: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 377: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 385: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 385: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 387: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 387: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 396: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 396: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 398: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 398: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 492: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 492: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 494: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 494: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 506: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 506: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 508: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 508: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 516: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 516: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 518: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 518: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 535: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 535: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 537: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 537: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 545: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 545: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 547: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 547: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 644: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 644: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 646: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 646: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 657: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 657: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 659: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 659: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 671: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new 671: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old 673: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 673: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 902: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 902: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old 920: AND h.creation_date BETWEEN TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new 920: AND h.creation_date BETWEEN TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1134: AND h.creation_date BETWEEN TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1134: AND h.creation_date BETWEEN TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1151: AND h.creation_date BETWEEN TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1151: AND h.creation_date BETWEEN TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS') AND TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1280: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new1280: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old1282: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1282: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1350: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new1350: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old1352: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1352: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1363: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new1363: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old1365: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1365: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1380: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new1380: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old1382: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1382: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
old1393: TO_DATE('&1 00:00:00','DD-MON-YYYY HH24:MI:SS')
new1393: TO_DATE('25-oct-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
old1395: TO_DATE('&2 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
new1395: TO_DATE('29-oct-2006 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
INSERT INTO XXCCA_RPT_GENERIC_TP(N_COLUMN1,C_COLUMN1)
*
ERROR at line 140:
ORA-06550: line 140, column 16:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 140, column 4:
PL/SQL: SQL Statement ignored


'Saving Enviorment setting in file'
Wrote file set_save.sql
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Mon Mar 12 04:39:11 PDT 2007 Process Failed
./Expedites_test.ksh[41]: /tmp/expedites_test.lst: cannot open

THANKS IN ADVANCE
VENU

Check your umask setting. Set
or and see.

I faced a similar issue. basically, its related to tmp files and it could point to file permission issues.

Hi,
Thanks for your reply
But Iam facinf same problem...could you please verify Code.
Regrds
Venu

Where is the file in the below statement created?
Thats what is failing with the file permission error. Could you check the file's existence and its permissions??And one more thing, In this code part
you should have a space after the 'date' command. it should be

Hi
Thanks for your reply.Its working fine now.but while appending log information to log file its not working.

log file contains ONLY Following line:

ODSDEV Mon Mar 12 23:23:02 PDT 2007Expedites process Failed

Please see the code below

#:Name : Expedites_test.ksh
#:Path : /bin
#:Author : Venu Narapureddy
#ate : 08-mar-2007
#escription : Script to extract and refresh Expeditesdata.
#:Frequency of job :Monthly
#:History:
#ate: User: Modifications:
#:---------------------------------------------------------------------------
#:02-Jun-2006 Venu Narapureddy Initial version.
#:**************************************************************************
ORACLE_SID=ODSDEV export ORACLE_SID
ORACLE_HOME=/oracle/product/current export ORACLE_HOME
TNS_ADMIN=/var/opt/oracle export TNS_ADMIN
TOP='/users/qtcbi/metrics'; export TOP
MODULE_NAME='Expedites_test';export MODULE_NAME
ORAENV_ASK='NO';export ORAENV_ASK
LOG_FILE="$TOP/log/$MODULE_NAME.$(date +%m%d.%H:%M)"; export LOG_FILE
TMPFILE="/tmp/Expedites_test.lst"; export TMPFILE
NORMAL_MAIL='vnarapur'; export NORMAL_MAIL
ERROR_MAIL='vnarapur'; export ERROR_MAIL
UNAME='ccaisadm'; export UNAME # ORacle User ID
PWORD='changeit'; export PWORD # Oracle Password

echo "METRIX :Expedites : Extract of Expedites " |/bin/tee -a $LOG_FILE
echo "Current System Date Is : $(date) " |/bin/tee -a $LOG_FILE
echo "Script Is : $MODULE_NAME " |/bin/tee -a $LOG_FILE
echo "Working Directory Is : $(pwd) " |/bin/tee -a $LOG_FILE
echo "Host Machine Is : $(hostname) " |/bin/tee -a $LOG_FILE
echo "Oracle Instance Is : $ORACLE_SID " |/bin/tee -a $LOG_FILE
echo "$(date) Monthly job started " |/bin/tee -a $LOG_FILE
echo "$(date) Expedites extraction process Started " |/bin/tee -a $LOG_FILE
$ORACLE_HOME/bin/sqlplus $UNAME/$PWORD@$ORACLE_SID @$TOP/sql/Expedites.sql 25-oc
t-2006 29-oct-2006 |/bin/tee - a $LOG_FILE
errorcount=$(grep ORA - $LOG_FILE | wc -l)
if [[ $errorcount -gt 0 ]]; then
echo "$(date) Process Failed " |/bin/tee - a $LOG_FILE
/usr/bin/mailx -s "METRIX : EXPEDITES : $ORACLE_SID $(date)Expedites process Fai
led " $ERROR_MAIL </tmp/Expedites_test.lst > /dev/null
exit 1
else
echo "$(date) Process Done " |/bin/tee -a $LOG_FILE
/usr/bin/mailx -s "METRIX:EXPEDITES :$ORACLE_SID : LOG File for$(date) " $NORMAL
__MAIL <$LOG_FILE >/
dev/null
exit 0
fi
PWORD='changeit'; export PWORD # Oracle Password

echo "METRIX :Expedites : Extract of Expedites " |/bin/tee -a $LOG_FILE
echo "Current System Date Is : $(date) " |/bin/tee -a $LOG_FILE
echo "Script Is : $MODULE_NAME " |/bin/tee -a $LOG_FILE
echo "Working Directory Is : $(pwd) " |/bin/tee -a $LOG_FILE
echo "Host Machine Is : $(hostname) " |/bin/tee -a $LOG_FILE
echo "Oracle Instance Is : $ORACLE_SID " |/bin/tee -a $LOG_FILE
echo "$(date) Monthly job started " |/bin/tee -a $LOG_FILE
echo "$(date) Expedites extraction process Started " |/bin/tee -a $LOG_FILE
$ORACLE_HOME/bin/sqlplus $UNAME/$PWORD@$ORACLE_SID @$TOP/sql/Expedites.sql 25-oct-2006 29-oct-2006 |/bin/tee - a $LOG_FILE
errorcount=$(grep ORA - $LOG_FILE | wc -l)
if [[ $errorcount -gt 0 ]]; then
echo "$(date) Process Failed " |/bin/tee - a $LOG_FILE10
/usr/bin/mailx -s "METRIX : EXPEDITES : $ORACLE_SID $(date)Expedites process Failed " $ERROR_MAIL </tmp/Expedites_test.lst > /dev/null
exit 1
else
echo "$(date) Process Done " |/bin/tee -a $LOG_FILE
/usr/bin/mailx -s "METRIX:EXPEDITES :$ORACLE_SID : LOG File for$(date) " $NORMAL__MAIL <$LOG_FILE >/
dev/null
exit 0
fi

HiGHLIGHTED part only appending to LOG FILE...but i want all information in log file and as well as in body of the mail.

PLease guide me on this ....


Regards
Venu