Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Appending error messages from log file next to the corresponding error record

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Appending error messages from log file next to the corresponding error record
# 1  
Old 08-11-2010
Java Appending error messages from log file next to the corresponding error record
Hi Everyone,

I have an issue and trying to get a solution but was not succesful yet. Any help is greatly appreciated.
I am using ksh to inoke sql loader to load data from txt file into two oracle tables based on the condition written in the control file. If an error occurs while loading into tables sql loader creates a file called .bad and .log files, .bad file will have all the error records and .log file will have the corresponding error message. I have to write script which takes error message from the log file and appends at the end of each error record from .bad file into a new file with some name.
I hope i am clear, below are sample data on how a error record in .bad file looks like and how an error message in .log file looks like and what i am expecting the output to be.

Errror records in .bad file:
Code:
333abc22ef
444efg22ef

Error Message in .log file:
Code:
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 5 18:04:41 2010
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
-------------------------------------
----------- some info here----------
-------------------------------------
Record 2: Rejected - Error on table EMP, column EMP_ID.
ORA-01861: literal does not match format string

Record 3: Rejected - Error on table EMP, column EMP_NAME.
ORA-01861: literal does not match format string
---------------------------------------
--------some info here----------------
---------------------------------------

Output I am Looking For:
Code:
333abc22efError on column EMP_ID literal does not match format string
444efg22efError on column EMP_NAME literal does not match format string

I hope i am clear here, As i am new to scripting, finding difficult to get this work done. I will really be thankful for any help.
Last edited by Scott; 08-11-2010 at 07:11 PM.. Reason: Please use code tags
# 2  
Old 08-11-2010
Hi
Not exactly as you wanted, you can modify it a bit to satisfy your exact requirement:

Assuming your bad and log files are a.bad and a.log:

Code:
# cat a.bad
333abc22ef
444efg22ef
# cat a.log
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 5 18:04:41 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
-------------------------------------
----------- some info here----------
-------------------------------------
Record 2: Rejected - Error on table EMP, column EMP_ID.
ORA-01861: literal does not match format string

Record 3: Rejected - Error on table EMP, column EMP_NAME.
ORA-01861: literal does not match format string
---------------------------------------
--------some info here----------------
---------------------------------------
# awk 'NR==FNR{a[i++]=$0;next;}/Error on/{getline x;print a[j++],$0,x;}' a.bad a.log
333abc22ef Record 2: Rejected - Error on table EMP, column EMP_ID. ORA-01861: literal does not match format string
444efg22ef Record 3: Rejected - Error on table EMP, column EMP_NAME. ORA-01861: literal does not match format string
#

Guru.
# 3  
Old 08-11-2010
MySQL
Hi guruprasad,

You really rock, the peice of code you have given works and saved me from getting into trobule. I really appreciate your help thank you very much.

I know i am asking for too much but as i said i am new to scripting, can you give me an idea on how to remove the unwanted data which is getting appended to the error records.

Code:
Record 2: Rejected - and ORA-01861:

and also if you could describe on how your code works it would help me a lot with my further coding.

Thanks a lot for your prompt response and your timeSmilie
Last edited by Scott; 08-11-2010 at 07:12 PM.. Reason: Code tags
# 4  
Old 08-11-2010
Hi

Code:
# awk 'NR==FNR{a[i++]=$0;next;}/Error on/{getline x;print a[j++],$0,x;}' a.bad a.log | sed 's/Record[^-]*//;s/ORA-[^ ]*//'
333abc22ef - Error on table EMP, column EMP_ID.  literal does not match format string
444efg22ef - Error on table EMP, column EMP_NAME.  literal does not match format string
#

First we store all the records in the file a.bad in memory. Next, we start with a.log. As we encounter the lines 'Error on' we start reading the records in sequence from memory and display them.

Guru.
# 5  
Old 08-11-2010
Thank you GuruPrasad.

Once i got this one working i went up and embeded this piece of code in my script and when started to run the code i came up with one more issue which is related to this one. Let me explain it

sql loader created a 3rd file called .discarded which will have the records which failed to statisfy the condition when trying to load into the tables.
To make it more clear
suppose if we have a txt file with the following records

Code:
123vpvm
124gprd 
125ampk

i will have a control file which says if we have m at positon 7 then the record should go to table A and if we have d at position 7 then the record should go to table B but we do not have anything which says where sql loader should load if we k at position 7. Since it is not specified in the control file it will discarded while loading and sql loader will create a file with extension c.discarded. If the records satify the condition and if has data mismatch errors then they will be written to a file called a.bad
but the error messages for both the bad and discarded records will go into b.log

Note: Here .discarded file or .bad file will be created only if those kind of errors exist (ie. discarded file will be created only if we have rows which do not satisfy control file condition, similarly .bad file will be created only if the data in the rows do not satisfy table datatypes)

Suppose in txt file we have
Code:
123vpvm
124gprd 
125ampk

and we have not specified sqlloader what to do if a we have k at position 7 and if records 1 and 2 have error then

a.bad has both the 1st and 2nd records in it as
Code:
123vpvm
124gprd

and c.discarded will have the 3rd record
Code:
125ampk

and the b.log file will have
Code:
Record 1: Rejected - Error on table EMP, column EMP_ID.
ORA-01861: literal does not match format string

Record 2: Rejected - Error on table EMP, column EMP_NAME.
ORA-01861: literal does not match format string
 
Record 3: Discarded - failed all WHEN clauses.

so our script should check for .bad file and .discarded files and should create a new file with the error messages from log file appended next to corresponding error recrod from .bad or .discard files.(to make it more clear, our script should look for .bad file first, if exist then should append error messages from log file next to corresponding error record in .bad file and then check for .discarded file and if exist append error messages from log file next to corresponding error record. at the end everything should be in a new file)

so now the output should look like below in a new file
Code:
123vpvm Error on column EMP_ID literal does not match format string
124gprd Error on column EMP_NAME literal does not match format string
125ampk Discarded failed all WHEN clauses

is this possible to achive.

I know it will annoying if we repost a question by adding new stuff to it but this was not anticipated by me. Please excuse me and let me know what the solution would be.
Last edited by Scott; 08-12-2010 at 01:31 PM.. Reason: Code tags, please...
# 6  
Old 08-12-2010
You can do something like that :
Code:
awk '
FNR==1 { FileNum++ }
FileNum==1 { Bad[FNR] = $0 ; next }
FileNum==2 { Dis[FNR] = $0 ; next }
/^Record [0-9]+: Rejected/ {
   rec = Bad[++Rejected];
   col = $NF;
   getline;
   sub(/^ORA-[^:]*:[[:space:]]*/, "");
   print rec, "Error on column", col;
   next;
}
/^Record [0-9]+: Discarded/ {
   rec = Dis[++discarded];
   sub(/.*Discarded - /, "")
   print rec, $0;
   next;
}
' file.bad file.discarded file.log

file.bad:
Code:
123vpvm
124gprd

file.discarded:
Code:
125ampk

file.log:
Code:
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Aug 5 18:04:41 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
-------------------------------------
----------- some info here----------
-------------------------------------
Record 1: Rejected - Error on table EMP, column EMP_ID.
ORA-01861: literal does not match format string

Record 2: Rejected - Error on table EMP, column EMP_NAME.
ORA-01861: literal does not match format string

Record 3: Discarded - failed all WHEN clauses.

---------------------------------------
--------some info here----------------
---------------------------------------

Output:
Code:
123vpvm Error on column EMP_ID.
124gprd Error on column EMP_NAME.
125ampk failed all WHEN clauses.

Jean-Pierre.
# 7  
Old 08-12-2010
Hi Jean,

Thank you for the solution its working great. Its works when we have both file.bad and file.dis are availbale, as i mentioned earlier file.dis or file.bad will be created only if those error exist, in other words we might or might not have file.dis similarly file.bad but if file.dis is created then its corresponding error message will be in file.log and viceversa. So can we include some kind of logic in the written script which checks for those file existence.

If incase one of the files is missing then the error i am getting is "cannot open /home/file.dis for reading (No such file or directory)"

Please help me on this
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Print Error in Console and both Error & Output in Log file - UNIX

I am writing a shell script with 2 run time arguments. During the execution if i got any error, then it needs to redirected to a error file and in console. Also both error and output to be redirected to a log file. But i am facing the below error. #! /bin/sh errExit () { errMsg=`cat... (1 Reply)
Discussion started by: sarathy_a35
1 Replies

2. SuSE

Some error messages in var/log/messages

How are you? SUSE V10 and 11. In /var/log/messages I see these lines in some servers. I'd like to know what causes these errors and how to fix them. Thank you, error: PAM: Authentication failure for root from XXXXXXXX Did not receive identification string from XXXXXXX Invalid user suse-gm... (2 Replies)
Discussion started by: JDBA
2 Replies

3. Shell Programming and Scripting

Appending CRLF to end of record

I need to append |\r\n (a pipe character and CRLF) at end of each record in Unix to all records where they are not already present. So first check for the presence of |\r\n and if absent append it else do nothing (3 Replies)
Discussion started by: abhilashnair
3 Replies

4. Shell Programming and Scripting

Appending ErrorCodes to the corresponding error record

Hi, Here i'm using a awk inside bash script to validate the datafile by referring to the configuration file(schema file). Here the validation check is done for datatype, field length and null values. Once the validation is done on data file the error records are moved to the bad file. So... (22 Replies)
Discussion started by: shree11
22 Replies

5. Shell Programming and Scripting

need help in search the error messages from log file

Hi, My log file has error messages, universal no and universal ID. The problem is i have to search Universal ID from the error messages. But the log file write error messages with universal no and error messages in one line. and universal no with universal ID in other line. So i write two loops... (5 Replies)
Discussion started by: pjlotiya
5 Replies

6. UNIX for Dummies Questions & Answers

Appending error

Hi All, I just want to append the value in variable at the end of the file. var=1234 sed -e "$a $var" file1 > file 2. But I get this error sed: -e expression #1, char 4: unknown command: `1' Kindly let m know how can I do that... (5 Replies)
Discussion started by: waqar1
5 Replies

7. Shell Programming and Scripting

Error while appending records to a file

Hi, I have a sample file which contains records. Input File : 1 user1 username1\password@database-name 2 user2 username2\password@database-name 3 user3 username1\password@database-name I should search for a 'username1\' in those records. If 'username1\' is found in those records, that record... (7 Replies)
Discussion started by: siri_886
7 Replies

8. Shell Programming and Scripting

sqlplus error output to different error log file

HELLO, I am using such a command to write oracle sqlplus query result to text file: sqlplus -S xxx/xxx@xxxxxxx @\tmp\2.sql>\tmp\123.txt Is it possible to script that: If command succesfull write in \tmp\log.txt: timestamp and "succeded" and create 123.txt with results else If error... (2 Replies)
Discussion started by: tomasba
2 Replies

9. HP-UX

How to Redirect the error messages from Syslog file to our own Application Log File

Hello, I am New to Unix. I am Using HP-UX 9000 Series for my Application. I am Currently Facing an Issue that the error messages are being written in the syslog file instead of the Application Log File. The Codes for that Syslog.h is written in Pro*C. I want to know how to Redirect these... (3 Replies)
Discussion started by: balasubramaniam
3 Replies

10. Shell Programming and Scripting

appending spaces to first line based on second record.

Hi, I have a situation to append spaces to end of first record (header)and last record (footer) based on second record length. The first record length is always 20.The second record will be different for different files.I have to append spaces for the first line based on second record... (2 Replies)
Discussion started by: ammu
2 Replies
Login or Register to Ask a Question