[Dbix-class] update oracle clob field fails

[Marc Logghe]

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 =3D :p1
WHERE ( (
UTL_RAW.CAST_TO_VARCHAR2(RAWTOHEX(<*>DBMS_LOB.SUBSTR(anncomment_id, 2000,
1))) =3D :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 =3D :p1 WHERE anncomment_id =3D :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 =3D> "NUMBER", default_value =3D> undef, is_nullable =3D> 0, =
size
=3D> 38 },
  "bioentry_id",
  { data_type =3D> "NUMBER", default_value =3D> undef, is_nullable =3D> 0, =
size
=3D> 38 },
  "comment_text",
  {
    data_type =3D> "CLOB",
    default_value =3D> undef,
    is_nullable =3D> 0,
    size =3D> 2147483647,
  },
  "rank",
  { data_type =3D> "NUMBER", default_value =3D> "0 ", is_nullable =3D> 0, s=
ize =3D>
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 =3D> $comment->comment_text . $message=
});
 }


Guess, I'm missing something here, right ?

Regards,
Marc
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120223/28c=
ac623/attachment.htm