Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks , I really need your help on this : I have the following function that would grep for oracle errors and exit if catch an error , however , I need to ignore only one error which is the following : 
ERROR at line 1:
ORA-00001: unique constraint (blablablabla) violated

[download]
How ever with my current script , I am catching all ERROR, here is my script : 
package Verify;

sub chkLogs ( $ )
{
    my $LogFileNm = shift;

    open ( logHndl, "$LogFileNm" ) or die ("Failed to open $LogFileNm"
+);

    while(<logHndl>) {
        #
        # grep for keyword ERROR or ORA-ERR
        #
        push(@errors, grep (/ERROR|ORA-ERR|Fail|Unable/i, $_));
    }
    close(logHndl);

    if ( $#errors > -1 ) {
        print "FATAL: ended with the following errors\n";
        for( my $idx = 0; $idx < $#errors + 1; $idx ++ ) {
            print "$errors[$idx]\n";
        }
        exit(10);
    }
}
1;

[download]
Can someone help? thanks

Replies are listed 'Best First'.
Don't use SQL*Plus
by Steve_p (Priest) on Apr 16, 2004 at 18:03 UTC

    Some suggestions...

    First, if this is a program reading in a log created by another real program (not the output from SQL*Plus), change the program to not report the error.

    Second, if this is SQL*PLUS, and this script will be run more than once or you want to run it in a batch mode on a production system, stop what your doing with the log reading script and start writing a program that will do the work instead. SQL*Plus does not have the error handling capabilities to handle problems in a production system. You are setting your self up for a life of misery if you are attempting to use an interactive tool in a batch mode. Its fine for testing or development, but if you don't want to waste your company's or customers' money on an expensive outage, don't use SQL*PLUS.

[reply]
      You shouldn't say don't use SQL*Plus.
      I'm using that thing for years and once you know to handle it, it's ok for a lot of tasks.
      And maybe the logfile was created by sqlloader which is very efficient for large amounts of data.

      pelagic
[reply]

        Unfortunately, there are errors that SQL*Plus will return a zero no matter what you do. Then, your script that is running SQL*Plus needs to handle them. So, yes, it can be done; however, in most cases, it is a kluge to avoid writting a program that can do the same work, but only better.

[reply]
Re: greping erros from ORACLE
by matija (Priest) on Apr 16, 2004 at 16:25 UTC
    push(@errors, grep (/ERROR|ORA-ERR|Fail|Unable/i, $_));
    You're either confused, or very clever.

    Let's try: 

    perl -e 'push(@a,grep(/bra/,"abracadabra")); print @a;'
abracadabra
perl -e 'push(@a,grep(/bar/,"abracadabra")); print @a;'
(no output)

    [download]
    Wow! It works.

    My proposed solution for your problem is to put a line like 

    next if /ORA-\d: unique constraint \(.*?\) violated/;

    [download]
    just before the push line.
[reply]
[d/l]
[select]

      grep on a hardcoded, one element list is clever? Some might say inefficent. My vote would be for confused and grasping at random functions which appear to be cognates to existing Unix knowledge.

[reply]
[d/l]

Back to Seekers of Perl Wisdom