Hello Tom,

we are using oracle 10g, perl dbi to connect to oracle,with 2 tables with clob's columns, and the trigger that after insert concatenates a varchar2 and a clob, and inserts the new concatenated value to a clob field in the second table.

CREATE OR REPLACE TRIGGER QUERY_SEQUENCE_TR5 AFTER
INSERT ON QUERY_SEQUENCE FOR EACH ROW
BEGIN
insert into blistxt.query_seq_txt values(:new.seq_id,:new.name||' '||:new.description);
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;

this works perect if we insert a row thru sqlplus, but when we insert a row trhu the application written in perl, the clob doesn't get propagated correctly, in fact it doesn't get propagated at all, but when we inspect the original row after the insert, all the values are there.

for values:
seq_id=17170598
name='622_at'
description='622_at; gb|M28212; SEBASTIAN AND CHRIS Homo sapiens GTP-binding protein (RAB6)mRNA, complete cds'

here is the part of the perl code for the insert

my $query = qq{
insert into query_sequence (seq_id, user_id, name, description, moltype, seq_data, seq_len, time, link, org_id, realm_id, datasource_id, datasource_id_original, query_seq_load_id)
values (null, ?, ?, ?, ?, ?, ?, sysdate, ?, ?, ?, ?, ?, ?)
};

my $sth = $self->prepare($query);

$sth->bind_param(1, $self->user_id);
$sth->bind_param(2, $name);
$sth->bind_param(3, $desc, { ora_type => ORA_CLOB, ora_field=>'description'});
$sth->bind_param(4, $moltype);
$sth->bind_param(5, $seq_data, { ora_type => ORA_CLOB, ora_field=>'seq_data'});
$sth->bind_param(6, $length);
$sth->bind_param(7, undef);
$sth->bind_param(8, $org_id);
$sth->bind_param(9, $realm_id);
$sth->bind_param(10, $source_id);
$sth->bind_param(11, $source_id);
$sth->bind_param(12, $load_id);

my $result = $sth->execute();

this is the result

SQL> l
1* select seq_id,name,description from query_sequence where seq_id=17170598
SQL> /

SEQ_ID
----------
NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
17170598
622_at
622_at; gb|M28212; SEBASTIAN AND CHRIS Homo sapiens GTP-binding protein (RAB6) m



1* select * from query_seq_txt where seq_id=17170598
SQL> /

SEQ_ID QUERY_TEXT
---------- ----------------------------------------------------------
17170598 622_at



do you know any limitation or exception with triggers and clobs being inserted thru perl/dbi?

Any help will be great.

Tahnks a lot

Sebastian