Re: How to call DBD::Oracle's dbms_output_get from within a DBIx

Martin Evans wrote:

Cheers, I took a variation on your second suggestion similar to what Philip Garret put forward. This works but I have (hopefully) one related last issue. In the following sequence the first statement handle loses errstr and err values (see comments):

DBIx::Log4perl::st::execute {
my ($sth, @args) = @_;
my $h = $sth->{private_DBIx_Log4perl};

my $ret = $sth->SUPER::execute(@args);

# execute failed and an error handler was called
# $sth->errstr and $sth->err are both true and contain values

if (($h->{logmask} & DBIX_L4P_LOG_DBDSPECIFIC) &&
($h->{driver} eq 'Oracle') && (!$h->{dbd_specific})) {
$h->{dbd_specific} = 1;
my $dbh = $sth->FETCH('Database');

# The following call causes a new sth to be created in
# DBD::Oracle from a prepare_cached call and execute
# to be called multiple times to retrieve dbms_output
# The execute calls bring us back to this method but
# because of $h{dbd_specific} we don't get here again.

my @d = $dbh->func('dbms_output_get');
$sth->_dbix_l4p_debug('dbms', @d);
$h->{dbd_specific} = 0;

# Now $sth->errstr and $sth->err are undef - why?
# Why has creating a new statement and executing
# successfully on it changed errstr/err in another
# statement?
}
return $ret;
}

BTW, this is DBI 1.54rc8 (though using 1.53 makes no difference) and DBD::Oracle 1.19 (with one tiny patch to execute_array I posted here last week). This may also be slightly related to my posting "most drivers share error variable for sth/dbh handles?"
http://www.nntp.perl.org/group/perl.dbi.users/2007/01/msg30761.html.

Martin

It would appear the problem I'm seeing can be simplified to the following (not using any DBIx):

use DBI qw(neat);
use strict;
use warnings;

my $h = DBI->connect("dbi:Oracle:XXX","xxx","yyy",
{RaiseError => 0, PrintError => 0});
$h->func('dbms_output_enable');
my $s1 = $h->prepare(q/update fred set fred = 'fred'/);
$s1->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";
my $s2 = $h->prepare(q/begin
dbms_output.put_line('fred');
end;/);
$s2->execute;
print "errstr:\n" . neat($s1->errstr) .
"\nerr:\n" . neat($s1->err) . "\n";

which produces:

errstr:
'ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 7 in 'update <*>fred set fred = 'fred'')'
err:
'942'
errstr:
undef
err:
undef

So, as you can see the $s1 has lost its errstr and err values. Any idea why or where to look for this?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
.

Relevant Pages

  • Re: Documentation / Error checking
    ... >> just a few special cases such as err and errstr, obviously, and FETCH ... if you're going to die() I'd just set RaiseError and forget about it. ...
    (perl.dbi.users)
  • DBI->state()
    ... I see the err() and errstr() are still ... but no trace of state(as far ... Presicient Corp. ...
    (perl.dbi.users)
  • Re: DBI->state()
    ... there seems to be some confusion here. ... > Ultimately the only access to a handle's err, errstr, and state are ... > on the last handle the DBI used. ...
    (perl.dbi.users)
  • Re: DBI->state()
    ... I see the err() and errstr() are still ... but no trace of state(as far ... Tim. ...
    (perl.dbi.users)