Perl solution for reading multi lines error search pattern

[Srikanth Arunachalam]

Hi,

I need to consolidate a reconcillation file based on errors received from standard oracle SQL*Loader log file. In case of SQL*Loader logfile, the error message for single rejected record is spawned as 5 error messages. For example, rejected record 10 appears as
Record 10: Rejected - Error on table "CFSOWNER"."SUA_MT", column FEES_AMT_IND.
ORA-01400: cannot insert NULL into (FEES_AMT_IND)
SQL*Loader-522: lfiopn failed for file (/transfer/linktest/estath/scripts/ods/loader/bad/LOADED_ASFGH.DAT.bad.1)
SQL*Loader-552: insufficient privilege to open file
SQL*Loader-509: System error: Permission denied
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

Based on SQL*Loader error logfile, I want to generate a short version reconcillation information that would give me info like
Record10|ORA-01400: cannot insert NULL into (FEES_AMT_IND)|
Record15|
Please note that I am interested only on ORA- error and not on SQL*Loader error.

Another level of information is I should be able to scan record 10 in LOADED_ASFGH.DAT and get the first 15 characters in the feedfile in a loop.
So output should be like.
Record10|ORA-01400: cannot insert NULL into (FEES_AMT_IND)|Record15|ORA-01400:..
records are like
111465550005060 30/04/20100000000001003 UKP 29632.99

Thanks,
Srikanth A

[James R. Ferguson · Accepted solution]

Hi:

A post of a portion of the *actual* files (not pasted here where the forum may mangle things) would lead to a better response.

Regards!

...JRF...

[Srikanth Arunachalam]

Hi James,

As requested I am attaching the feed file and logfile to be scanned and generate a output file. Please advice.

Thanks,
Srikanth Arunachalam

[Srikanth Arunachalam]

Hi,

Sorry to have missed the feedfile.

Thanks,
Srikanth

[James R. Ferguson]

Hi (again) Srikanth:

Based on your data, you could do:

# cat ./myfilter
#!/usr/bin/perl
use strict;
use warnings;
my ( @pieces, $line );
{
local $/ = undef;
@pieces = split( /(?=Record\s+\d+:)/, <> );
}
for $line (@pieces) {
if ( $line =~ m{(Record\s+\d+).+?(ORA.+?)SQL}s ) {
print "$1|$2";
}
}
1;

# ./myfilter mylog
Record 10|ORA-01400: cannot insert NULL into (FEES_AMT_IND)
Record 15|ORA-01400: cannot insert NULL into (FEES_AMT_IND)
Record 21|ORA-01400: cannot insert NULL into (FEES_AMT_IND)

As for the second part of your question, does "...scan record 10..." simply mean "read record #10" and append the first 15-characters to the above output?

Regards!

...JRF...

[James R. Ferguson]

Hi (again):

I am going to assume that for the second part of your question, does "...scan record 10..." simply mean "read record #10" and append the first 15-characters to the above output. If so, overall, you might do:

# cat ./myfilter
#!/usr/bin/perl
use strict;
use warnings;
my ( @pieces, $line, @lookup );
{
my $feedfile = '/tmp/myfeed';
open( my $fh, '<', $feedfile ) or die "Can't open '$feedfile': $!\n";
while (<$fh>) {
chomp;
push( @lookup, substr( $_, 0, 14 ) );
}
}
{
local $/ = undef;
@pieces = split( /(?=Record\s+\d+:)/, <> );
}
for $line (@pieces) {
if ( $line =~ m{(Record\s+(\d+)).+?(ORA.+?)\sSQL}s ) {
print $1, "|", $3, "|", $lookup[ $2 - 1 ], "\n";
}
}
1;

# ./myfilter mylog
Record 10|ORA-01400: cannot insert NULL into (FEES_AMT_IND)|11465550005060
Record 15|ORA-01400: cannot insert NULL into (FEES_AMT_IND)|11465550005060
Record 21|ORA-01400: cannot insert NULL into (FEES_AMT_IND)|11465550005060

Please note that I hard-coded the name for the "feeder" data. You can embellish this as needed.

Regards!

...JRF...

[Srikanth Arunachalam]

Hi James,

Thank you very much. I got exactly what is expected. Many many thanks for your help as usual. You have been a good mentor for honing my perl skills.

[Srikanth Arunachalam]

Completely satisfied with the response.