[Dbix-class] update oracle clob field fails

[Marc Logghe]

On Thu, Feb 23, 2012 at 3:55 PM, Peter Rabbitson <rabbit+dbic at rabbit.us>wro=
te:

> 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 possi=
bly
>> near <*> indicator at char 85 in 'UPDATE anncomment SET comment_text =3D=
 :p1
>> WHERE ( ( UTL_RAW.CAST_TO_VARCHAR2(**RAWTOHEX(<*>DBMS_LOB.SUBSTR(**annco=
mment_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,=
 size
>> =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});
>>  }
>>
>
> $comment here is not a comment result object - it's the comments resultset
> object. You are invoking a ResultSet->update, not a Row->update.
>
>
Hi,
Actually, it is not:
$LOG->debug('Comment is a ' . ref $comment);
gives:
2012/02/23 16:07:49 DEBUG: Comment is a BioSQL::DB::Anncomment

calling search_related in list context, implicitely calls all()
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120223/d52=
2d0bb/attachment.htm