Re: ANNOUNCE: DBD::Oracle 1.24 Release Candidate 4 (ORA-22922 error)

John Scoles wrote:
> 
> Well here comes #4
> 
> 
> It can be found at the usual place
> 
> http://svn.perl.org/modules/dbd-oracle/trunk/DBD-Oracle-1.24-RC4.tar
> 
> 
> This time round I added a little patch from Charles Jardine fro objects
> and some fixes for warnings
> 
> I have changed the ora_ncs_buff_mtpl default value back to 4 so it
> doesn't muck anyone up and have made it so it reports better and have
> updated the Pod to reflect this
> 
> Hopefully this will get most of them this time round
> 
> Cheers
> 
> and thanks for all the testing
> 
> John S
> 
> 

John,

I have a vague recollection someone mentioned lobs and freeing them
during this RC round but I cannot locate that email now - if someone did
it may be relevant to this issue. Anyway, I am getting a new error

"DBD::Oracle::st DESTROY failed: ORA-22922: nonexistent LOB value (DBD
ERROR: OCILobFreeTemporary) [for Statement "BEGIN p_mje(?); END;"]."

but as yet I cannot be certain this was not there before as our code
base for the application has changed. Code demonstrating the problem is
below and I will a) try and check this with a stock 1.23 and b) see if I
can locate the problem.

# $Id: fork.pl 3727 2010-01-22 10:47:27Z martin $
# Perl script which demonstrates ORA-22922 error in DBD::Oracle 1.24 RC4
#
use strict;
use warnings;
use DBI;
use Proc::Fork;
use Data::Dumper;
use DBD::Oracle qw(:ora_types);

my $ph = DBI->connect(
    "dbi:Oracle:host=betoracle.easysoft.local;sid=devel",
    "bet", "b3t");
print "ph InactiveDestroy = $ph->{InactiveDestroy}\n";

eval {
    local $ph->{PrintError} = 0;
    $ph->do(q/drop table mje/);
    $ph->do(q/drop procedure p_mje/);
};
$ph->do(q/create table mje (a clob)/);
$ph->do(<<SQL);
create procedure p_mje(a clob) AS
BEGIN
INSERT INTO mje values(a);
END;
SQL

my $clob = 'x' x 50000;
my $st = $ph->prepare(q/BEGIN p_mje(?); END;/);
$st->bind_param(1, $clob, {ora_type => ORA_CLOB});
$st->execute;

run_fork {
    child {
#        my $ch = $ph->clone;
        $ph->{InactiveDestroy} = 1;
#        $ph = undef;
        exit 0;
    }
    parent {
        waitpid $_[0], 0;
    }
};

NOTE, it is not sufficient to simply set InactiveDestroy, you seem to
need to do the fork.

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