DBD::Oracle 2nd insert of row into table with 2 CLOBs hangs

Hi Tim & Folks,

We've found a interesting problem when inserting multiple rows into a
table containing two CLOB columns. The second execute() hangs and
Oracle never responds. The execute() hangs only when the character
sizes of the two strings are larger than 4000 characters each, and even
then not always. Code is below which demonstrates problem. Any clues as
to whether this is in the DBD layer or in Oracle?

Thanks!!!

DBI Version: 1.48
DBD::Oracle Version: 1.16
Oracle Version: Oracle9i Release 9.2.0.7.0
Operating System: Linux
Perl Version: 5.8.7

Code:



#!/usr/bin/perl -w
use strict;

use DBI;

$| = 1;

my $quals = join ' ', map { $_ } ( 0..1022 );
my $discrep = join ' ', map { $_ } ( 0..1022 );

my $dbh = DBI->connect( 'DBI:Oracle:host=ora9sun;sid=ora9sun;port=1521',
'wollaston', '******', { AutoCommit => 0 } );

$dbh->do( "DROP TABLE badins" );
$dbh->do( "CREATE TABLE badins ( q CLOB, d CLOB )" );
$dbh->commit();

my $ins = $dbh->prepare( 'INSERT INTO badins ( q, d ) VALUES ( ?, ? )' );
for ( 1..2 )
{
print "length: ", (length ($quals) + length ($discrep)), "\n";
print( "inserting..." );
$ins->execute( $quals, $discrep );
print( "done\n" );
}
$dbh->rollback();

$dbh->do( "DROP TABLE badins" );
$dbh->commit();



--
Joe Slagel
Chief Software Architect
Geospiza Inc
www.geospiza.com

Re: DBD::Oracle 2nd insert of row into table with 2 CLOBs hangs

On Tue, 2005-12-06 at 15:43 -0800, Joe Slagel wrote:
> Hi Tim & Folks,
>
> We've found a interesting problem when inserting multiple rows into a
> table containing two CLOB columns. The second execute() hangs and
> Oracle never responds. The execute() hangs only when the character
> sizes of the two strings are larger than 4000 characters each, and even
> then not always. Code is below which demonstrates problem. Any clues as
> to whether this is in the DBD layer or in Oracle?
>

A few questions:

* Can you reproduce this in SQL*Plus or PL/SQL? If so, then
you have a problem with the database, possibly a bug.

* Have you traced the session via 10046 to see exactly what
the session is doing?

* Have you checked v$sesstat to see what the session is waiting on?

select
s.username username,
e.event event,
s.sid,
e.p1text,
e.p1,
e.p2text,
e.p2,
e.wait_time,
e.seconds_in_wait,
e.state
from v$session s, v$session_wait e
where s.username is not null
and s.sid = e.sid
and s.username like upper('&uusername')
order by s.username, upper(e.event)

This should help determine if the problem is in the db or somewhere
else.

HTH

Jared