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