OraFAQ Forum: Unix » if record count is zero in file,need to report

Home » Infrastructure » Unix » if record count is zero in file,need to report (unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

if record count is zero in file,need to report [message #590784]

Mon, 22 July 2013 17:40

swapnabpnn
Messages: 96
Registered: December 2010

Member

Hi,
In the following script,I am trying to write a case where if record count in datafile is zero then need to email about that to user.I am trying to write this in function i.e email_report in below code.But not able to do it.Can someone please help.


################################################################################
#####
SALE_LOC=$1
################################################################################
#####
# Variable Declarations
################################################################################
#####

. ~/env_vars
SCRIPT_NAME=$(basename $0)
JOB_NAME=" Report by Date"
JOB_DESCRIPTION="Please find the attached  Report"
PRIORITY="P1"
SEND_CONFIRMATION="NO"
PAGE_ONCALL="NO"
TIME_STAMP=`date +%y%m%d%H%M%S`
STATUS=0
START_TIME=`date`
REPORT_DIR=$INOUT
OUTPUT_FILE="report.txt"
REPORT_FILE="report.csv"
((!$#)) && echo Please enter sale_loc_id as input parameter: && exit 1
#############################################################################
# Execute the Oracle procedure.
#############################################################################
function execute_sql {
echo "Running SQL Script"
sqlplus -s ${ORACLE_CONNECT}<<- EOF
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
exec prddctn.Reports.report_allcntries($SALE_LOC);
EOF
return $?
}

#############################################################################
# Email reports
#############################################################################
function email_report {
print "emailing reports..."
mailx -s "${JOB_NAME}" ${REPORT_EMAIL} <<- EOF
Job Name: ${JOB_NAME}
Job Description: ${JOB_DESCRIPTION}
Script: $BIN/$SCRIPT_NAME"
ux2dos $REPORT_DIR/$OUTPUT_FILE > $REPORT_DIR/$REPORT_FILE
DATAFILE=`ls -t1 $REPORT_DIR/$REPORT_FILE`
rows=`wc -l ${DATAFILE} | awk '{print $1}'` # line count from the data file
rows=$((rows-1));         # remove the header row from the count
if [ ${rows} -eq 0 ]
then
 echo " No items were rekeyed."
else
~< ! uuencode $REPORT_DIR/$REPORT_FILE Rekey_report.csv
fi
EOF
return $?
}

#############################################################################
# FUNCTION email_status
#############################################################################
function email_status
{
print "E-mailing status.."
mailx -s "${1:-$JOB_NAME - Status}" ${2:-$SUPPORT_EMAIL} <<- EOF
Job Name: $JOB_NAME
Job Description: $JOB_DESCRIPTION
Problem: ${3:-n/a}
priority: $PRIORITY
Page Oncall: $PAGE_ONCALL
Recovery: ${4:-n/a}
Script: $BIN/$SCRIPT_NAME
Host: `hostname`
User ID: `whoami`
Oracle SID: $ORACLE_SID
Send Confirmation: $SEND_CONFIRMATION
Confirmation Email: $CONFIRMATION_EMAIL
Support Email: $SUPPORT_EMAIL
Oncall Email: $ONCALL_EMAIL
Start Time: $START_TIME
End Time: ${END_TIME:-`date`}
EOF
return $?
}

#############################################################################
# MAIN
#############################################################################

execute_sql
STATUS=$?
if [ $STATUS -eq 0 ]
then
 email_report
 END_TIME=`date`
 print "End Time: $END_TIME"

else
  END_TIME=`date`
  print "End Time: $END_TIME"
  print "$JOB_NAME FAILED with error code $STATUS"
  SUBJECT="$JOB_NAME *FAILED* - $PRIORITY"
  PROBLEM="The job failed with an error code $STATUS."
  RECOVERY="Rerun the script."
  email_status "$SUBJECT" "$SUPPORT_EMAIL" "$PROBLEM" "$RECOVERY"
  if [ $PAGE_ONCALL = "YES" ]
  then
    email_oncall "$SUBJECT"
  fi
fi

# Exit
print "EXIT STATUS $STATUS"
exit $STATUS

Thanks

Report message to a moderator

Re: if record count is zero in file,need to report [message #590785 is a reply to message #590784]

Mon, 22 July 2013 18:10

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> am trying to write this in function i.e email_report in below code.But not able to do it
What exactly is failing?
Please provide a session output/log.

Report message to a moderator

Re: if record count is zero in file,need to report [message #590786 is a reply to message #590785]

Mon, 22 July 2013 18:28

swapnabpnn
Messages: 96
Registered: December 2010

Member

Sorry about that,Please find below the test results of the code.

################################################################################
#####
SALE_LOC=$1
################################################################################
#####
# Variable Declarations
################################################################################
#####

. ~/env_vars
SCRIPT_NAME=$(basename $0)
JOB_NAME="xxx"
JOB_DESCRIPTION="abc"
PRIORITY="P1"
SEND_CONFIRMATION="NO"
REPORT_EMAIL="abc@1.com"
CONFIRMATION_EMAIL=abc@1.com
SUPPORT_EMAIL=abc@1.com
PAGE_ONCALL="NO"
ONCALL_EMAIL=abc@1.com
TIME_STAMP=`date +%y%m%d%H%M%S`
STATUS=0
START_TIME=`date`
REPORT_DIR=$INOUT
OUTPUT_FILE="report.txt"
REPORT_FILE="report.csv"
((!$#)) && echo Please enter sale_loc_id as input parameter: && exit 1
#############################################################################
# FUNCTION print_job_info
#############################################################################
function print_job_info {
print "Job Name: $JOB_NAME"
print "Job Description: $JOB_DESCRIPTION"
print "SALE_LOC: $SALE_LOC"
print "Priority: $PRIORITY"
print "Page Oncall: $PAGE_ONCALL"
print "Send Confirmation: $SEND_CONFIRMATION"
print "Script: $BIN/$SCRIPT_NAME"
print "Host: `hostname`"
print "User ID: `whoami`"
print "Oracle SID: $ORACLE_SID"
print "Confirmation Email: $CONFIRMATION_EMAIL"
print "Support Email: $SUPPORT_EMAIL"
print "Oncall Email: $ONCALL_EMAIL"
return $?
}

# Execute the Oracle procedure.
#############################################################################
function execute_sql {
echo "Running SQL Script"
sqlplus -s ${ORACLE_CONNECT}<<- EOF
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
exec prddctn.Reports.report_allcntries($SALE_LOC);
EOF
return $?
}

#############################################################################
# Email reports
#############################################################################
function email_report {
print "emailing reports..."
mailx -s "${JOB_NAME}" ${REPORT_EMAIL} <<- EOF
Job Name: ${JOB_NAME}
Job Description: ${JOB_DESCRIPTION}
Script: $BIN/$SCRIPT_NAME"
ux2dos $REPORT_DIR/$OUTPUT_FILE > $REPORT_DIR/$REPORT_FILE
DATAFILE=`ls -t1 $REPORT_DIR/$REPORT_FILE`
rows=`wc -l ${DATAFILE} | awk '{print $1}'` # line count from the data file
rows=$((rows-1));         # remove the header row from the count
if [ ${rows} -eq 0 ]
then
 echo " No items were rekeyed."
else
~< ! uuencode $REPORT_DIR/$REPORT_FILE report.csv
#fi
EOF
return $?
}

#############################################################################
# FUNCTION email_status
#############################################################################
function email_status
{
print "E-mailing status.."
mailx -s "${1:-$JOB_NAME - Status}" ${2:-$SUPPORT_EMAIL} <<- EOF
Job Name: $JOB_NAME
Job Description: $JOB_DESCRIPTION
Problem: ${3:-n/a}
priority: $PRIORITY
Page Oncall: $PAGE_ONCALL
Recovery: ${4:-n/a}
Script: $BIN/$SCRIPT_NAME
Host: `hostname`
User ID: `whoami`
Oracle SID: $ORACLE_SID
Send Confirmation: $SEND_CONFIRMATION
Confirmation Email: $CONFIRMATION_EMAIL
Support Email: $SUPPORT_EMAIL
Oncall Email: $ONCALL_EMAIL
Start Time: $START_TIME
End Time: ${END_TIME:-`date`}
EOF
return $?
}

#############################################################################
# MAIN
#############################################################################
print_job_info

execute_sql
STATUS=$?
if [ $STATUS -eq 0 ]
then
 email_report
 END_TIME=`date`
 print "End Time: $END_TIME"

else
  END_TIME=`date`
  print "End Time: $END_TIME"
  print "$JOB_NAME FAILED with error code $STATUS"
  SUBJECT="$JOB_NAME *FAILED* - $PRIORITY"
  PROBLEM="The job failed with an error code $STATUS."
  RECOVERY="Rerun the script."
  email_status "$SUBJECT" "$SUPPORT_EMAIL" "$PROBLEM" "$RECOVERY"
  if [ $PAGE_ONCALL = "YES" ]
  then
    email_oncall "$SUBJECT"
  fi
fi

# Exit
print "EXIT STATUS $STATUS"
exit $STATUS

Test Results:
--------------
The output of above code is below.After executing pl/sql procedure ,it just says emailing reports... ,
but not getting email.Can you please help.


Job Name :xxx
Job Description:abc
SALE_LOC: 1
Priority: P1
Page Oncall: NO
Send Confirmation: NO
Script: /i/bin/xyz.ksh
Host: os
User ID: dc
Oracle SID: dct
Confirmation Email: abc@1.com
Support Email: abc@1.com
Oncall Email:abc@1.com
Running SQL Script

PL/SQL procedure successfully completed.

emailing reports...

Thanks

Report message to a moderator

Re: if record count is zero in file,need to report [message #590810 is a reply to message #590786]

Tue, 23 July 2013 03:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>it just says emailing reports... ,
>>but not getting email
The script (mailx parts) is fine. Just provided valid email.
I tested and got this email with expected failure status.
It is your environment.
First just test a simple mailx from command line.
Else Talk to your sysadmins.

To: magvivek@gmail.com
Subject: xxx *FAILED* - P1
Message-Id: <20130723074901.91EC6134E961@kalyani.local.home>
Date: Tue, 23 Jul 2013 03:49:01 -0400 (EDT)
From: magvivek@kalyani.local.home (Mahesh Rajendran)

Job Name: xxx
Job Description: abc
Problem: The job failed with an error code 1.
priority: P1
Page Oncall: NO
Recovery: Rerun the script.
Script: /somescript
Host: kalyani.local.home
User ID: magvivek
Oracle SID: 
Send Confirmation: NO
Confirmation Email: magvivek@gmail.com
Support Email: magvivek@gmail.com
Oncall Email: abc@1.com
Start Time: Tue Jul 23 03:49:00 EDT 2013
End Time: Tue Jul 23 03:49:01 EDT 2013

Report message to a moderator

Re: if record count is zero in file,need to report [message #591103 is a reply to message #590810]

Wed, 24 July 2013 15:26

swapnabpnn
Messages: 96
Registered: December 2010

Member

Thanks for the reply.Now it is fixed.Issue is in function email_report block where i tried to send email before performing the check on line count.It should be something like this

function email_report {
ux2dos $REPORT_DIR/$OUTPUT_FILE > $REPORT_DIR/$REPORT_FILE
DATAFILE=`ls -t1 $REPORT_DIR/$REPORT_FILE`
rows=`wc -l ${DATAFILE} | awk '{print $1}'` # line count from the data file
rows=$((rows-1));         # remove the header row from the count
if [ ${rows} -eq 0 ]
then
result="No items were there."
else
result="Please find the attached Report"
fi
BODY=`cat<<EOF
Job Name: ${JOB_NAME}
Script: $BIN/$SCRIPT_NAME
${result}
EOF`

print "emailing reports..."
mailx -s "${JOB_NAME}" ${REPORT_EMAIL} <<- EOF
$BODY
~< ! uuencode $REPORT_DIR/$REPORT_FILE report.csv
EOF
return $?
}

Thanks..

Report message to a moderator

Previous Topic:	exception case
Next Topic:	need to exit if report file is not found

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 21 16:15:21 CST 2026