[Dbix-class] update oracle clob field fails

[Peter Rabbitson]

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.