Shell Programming and Scripting

Hi All,

I have a log file which consists of log messages as follows ->

GLOBALCALLID_CLUSTERID_B NEXT * , O(") CHARACTER
JOINONBEHALFOF NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table IFA_MMV_CDR, column CDRRECORDTYPE.
ORA-01722: invalid number

Record 1811: Rejected - Error on table IFA_MMV_CDR.
ORA-01400: cannot insert NULL into ("VOIP3"."IFA_MMV_CDR"."ORIGIPPORT")

Record 1833: Rejected - Error on table IFA_MMV_CDR.
ORA-01400: cannot insert NULL into ("VOIP3"."IFA_MMV_CDR"."ORIGIPPORT")


Table IFA_MMV_CDR:
1813 Rows successfully loaded.
3 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


I want to know how can I use this data to get the total number of records failed and get the reasons for each record failed as stated with the record number in some shell variables in a shell script.


Thanks,
Rahul.

Hi Rahul,
Hmm, lets c if I get you correct.

1. Collect all the error messages and reason as a single line in a new file

grep -e "^Record" -e "ORA-" samp.txt | sed 'N;s/\n/ /g' > samp1.txt

2. Number of erroneous records
wc -l samp1.txt

3. get the record number and reason of failure for this record in shell variables
cat samp1.txt|while read i
do
recno=`echo "$i"|awk '{print $2}'
reason=`echo "$i"|nawk -F "ORA-" '{print $2}'
#Process $recno and $reason as you want.
done


Hope this helps

Regards