Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can you help with a CLOB problem?

Re: Can you help with a CLOB problem?

From: <frank.van.bortel_at_gmail.com>
Date: 29 Mar 2007 07:31:57 -0700
Message-ID: <1175178716.990174.24100@p15g2000hsd.googlegroups.com>


On 29 mrt, 10:06, Jeremy <jeremy0..._at_gmail.com> wrote:
> Started getting an error reported:
>
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "SYS.DBMS_LOB", line 789
> ORA-06512: at "WD.WD_FAB", line 11065
> ORA-06512: at "WD.WD_FAB", line 10288
>
> Line 10288 is:
> 10286 l_fab_form.view_form_score_intro :=
> 10287 replace (l_fab_form.view_form_score_intro,'[score]',p_score);
> 10288 append(l_fab_form.view_form_score_intro,
> 10289 l_clob);
>
> Line 11065 is in the procedure (in same package):
>
> ..... procedure append
> ..... (p_in in varchar2,
> ..... p_out in out clob)
> ..... is
> .....
> ..... begin
> .....
> 11655 dbms_lob.writeappend( p_out, LENGTH( p_in ), p_in );
> .....
> ..... end append;
>
> l_fab_form is defined as wd_fab_forms%rowtype;
>
> And the view_form_score_intro column is a varchar2(4000).
>
> What is puzzling is that the "append" procedure is used repeatedly in
> the same execution of the procedure using similar columns e.g.
>
> we start off with:
>
> -- Initialize CLOB
> dbms_lob.CreateTemporary( l_clob, TRUE );
>
> Then e.g.
>
> -- open an outer table
> append(htf.tableopen(cattributes=>'border="0"')
> ||chr(10)
> ||htf.tablerowopen
> ||chr(10)
> ||'<TD>',
> l_clob);
>
> Then e.g.
>
> append(l_fab_form.view_form_intro,
> l_clob);
>
> which works fine (and view_form_intro is also a varchar2(4000)).
>
> That's when we come to the call to "append" which is failing. There are
> also several calls to "append" after the one which fails, all of which
> work absolutely fine (when I comment out the 'problem' line).
>
> I am a bit stumped, could anyone shed any light on why this particular
> call to "append" might cause an error?
>
> Thanks.
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

Just a thought: what if the replaced string exceeds 4000 characters? Received on Thu Mar 29 2007 - 09:31:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US