Marc Logghe wrote:
Hi,
I bumped into an exception when trying to update a clob field in Oracle:
DBI Exception: DBD::Oracle::st execute failed: ORA-06553: PLS-306:
wrong number or types of arguments in call to 'SUBSTR' (DBD ERROR: error
possibly near <*> indicator at char 85 in 'UPDATE anncomment SET
comment_text = :p1 WHERE ( (
UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(<*>DBMS_LOB.SUBSTR(anncomment_id,
2000, 1))) = :p2 ) )
According to the documenation of DBIC::Row::update(), the row is
uniquely identified using a proper WHERE clause.
I don't understand why this 'proper WHERE clause' should be of such
complexity; guess
UPDATE anncomment SET comment_text = :p1 WHERE anncomment_id = :p2 )
should do the trick, since anncomment_id is the primary key (which is a
number)
The schema class BioSQL::DB::Anncomment looks basically like:
__PACKAGE__->load_components("Core");
__PACKAGE__->table("anncomment");
__PACKAGE__->add_columns(
"anncomment_id",
{ data_type => "NUMBER", default_value => undef, is_nullable => 0,
size => 38 },
"bioentry_id",
{ data_type => "NUMBER", default_value => undef, is_nullable => 0,
size => 38 },
"comment_text",
{
data_type => "CLOB",
default_value => undef,
is_nullable => 0,
size => 2147483647,
},
"rank",
{ data_type => "NUMBER", default_value => "0 ", is_nullable => 0, size
=> 2 },
);
__PACKAGE__->set_primary_key("anncomment_id");
__PACKAGE__->add_unique_constraint("xak1comment", ["bioentry_id", "rank"]);
In the script:
foreach my $comment ($nb->bioentries->search_related('comments'))
{
$comment->update({comment_text => $comment->comment_text . $message});
}
$comment here is not a comment result object - it's the comments resultset
object. You are invoking a ResultSet->update, not a Row->update.
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
