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

I have a process that queries an oracle database, many times the query contains some mathematical functions. At times we get the dreaded ORA-01476: divisor is equal to zero error which haults processing.

I am trying capture the rowid of the record that is causing this error for review. What I have come up with seems to only give me the rowid of the last successful record. (By the way, I am writing the output of the query to a flat file, so there is a little bit of code in here to print all of the columns except the rowid.)

I have a command line switch that allows the user to request the rowid, otherwise it just runs the supplied sql statement. 
    while ((@cols)=$sth->fetchrow_array){
        if ($row_id) {
            $cur_rowid=$cols[0];
            
            @cols=(@cols[1..$col_cnt]);
            print OFILE join "$delimiter" => (@cols),"\n";    
        }
        else {
            print OFILE join "$delimiter" => (@cols),"\n";
        }

    }  #end while

[download]
Does anyone know how I can capture the specifics of the row that is actually failing? Based on the behavior, it appears as though the behavior is that the array @cols isn't populated when the error occurs. Regards

Replies are listed 'Best First'.
Re: get rowid for failed records using dbi
by Anonymous Monk on Jul 15, 2005 at 22:25 UTC
    I can't answer the question directly. Since I have not encountered that type of error with the DBI module. I suspect that there is NO rowid to provide your perl script since the operation is failing in the oracle database server itself.

    Since you're likely doing the divide operation in sql, you should do a simple decode on Zero for the divisor and set the result an "impossible" value. That will help you identify from your script.

    If this is bad data just look in the column where the row is null and identify the rowid that way.

[reply]
Re: get rowid for failed records using dbi
by ctaustin (Sexton) on Jul 16, 2005 at 05:31 UTC
    Yeah, I know that we could work around this error in the sql statement itself, but in these cases the data is actually bad and we need to investigate it. Thus we are trying to capture the rowid for the investigation. This may not be possible, we were just hoping to get that level of error handling.
[reply]
Re: get rowid for failed records using dbi
by Anonymous Monk on Jul 16, 2005 at 07:00 UTC

    You may try watching what is returned in $sth->err and $sth->errstr, may contain the id or some indication of the position on the dataset where the error ocurred.

    The use of parens around @cols may be redundant and can (IMHO), give troubles as it may be creating an anonymous list (at least it's what it does when you use ($v1,$v2)=sub_returning_array). Also, the use of shift can clarify your code a little. Try:

    while(@cols=$sth->fetchrow_array) {
    $cur_rowid = shift @cols if $row_id;
    print OFILE join("$delimiter", @cols), "\n";
]

if($sth->err) {
    print "$sth->errstr\n";
}

    [download]

    Please note that this is a blind shot as I don't have Oracle and, as you may note, I'm just an average Perl programmer, but this may be of help (I think ;)

    If that does not give the results you want, you may try using the HandleError hook of DBI. Looking at the documentation it says that the sub defined this way receives, as a third argument, the "first value being returned by the method that failed". Who knows, that may be the rowid you want. ;)

    HTH

[reply]
[d/l]
      Thanks, I will give these changes a try to see if this helps. I'll post the results.
[reply]
Re: get rowid for failed records using dbi
by ctaustin (Sexton) on Jul 18, 2005 at 15:57 UTC
    I haven't gotten this to work yet. At this point I am imagining that this isn't possible. I do like the suggestions that tightened up the code though. Always nice to reduce the number of lines of code in half. Thanks for that.
[reply]

Back to Seekers of Perl Wisdom