RE: OWA_PATTERN Error

From: Subbiah, Nagarajan <Nagarajan.Subbiah_at_aetn.com>
Date: Fri, 5 Aug 2005 11:18:33 -0400
Message-ID: <30462D80AA52E74698512ADCC4F7EAA3280A7DBB@exchange.aetvn.com>

In the other posting "10046/10079 Tracing understanding - SOLVED", there are some 'CHAR' issues. Does oracle do some types of conversion when it uses the 'OWA_PATTERN'. SQL> desc program_synopsis

 Name                                      Null?    Type
 ----------------------------------------- --------


----------------------------

 PROGRAM_ID                                NOT NULL NUMBER(10)
 SYNOPSIS                                  NOT NULL CLOB
 CREATED_BY                                NOT NULL VARCHAR2(10)
 CREATED_DATE                              NOT NULL DATE
 UPDATED_BY                                         VARCHAR2(10)
 UPDATED_DATE                                       DATE
 SYNOPSIS_TEXT                                      CLOB

SQL> update program_synopsis set synopsis_text= ppl.stripallhtmltags(synopsis)
2 where length(synopsis) between 11000 and 12000; update program_synopsis set synopsis_text= ppl.stripallhtmltags(synopsis)

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.OWA_PATTERN", line 885
ORA-06512: at "SYS.OWA_PATTERN", line 1128
ORA-06512: at "SYS.OWA_PATTERN", line 1165
ORA-06512: at "PPL.PPL", line 874

SQL> update program_synopsis set synopsis_text= ppl.stripallhtmltags(synopsis)
2 where length(synopsis) between 9000 and 10000;

31 rows updated.

SQL> Raja.
-----Original Message-----

From: Subbiah, Nagarajan [mailto:Nagarajan.Subbiah_at_aetn.com] Sent: Wednesday, August 03, 2005 12:22 PM To: 'oracle-l_at_freelists.org'
Subject: OWA_PATTERN Error

Hi,

We are running an update statement (stripping off all HTML tags from the not nullable clob and saving into another clob column, which can be null) and getting this error:

(Error): ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "SYS.OWA_PATTERN", line 885 ORA-06512: at "SYS.OWA_PATTERN", line 1128 ORA-06512: at "SYS.OWA_PATTERN", line 1165 ORA-06512: at "PPL.PPL", line 875

Any idea why? The length of the column is only 10,000. The works for the column length upto 10,000 and when the column length is close to 11,000 this throws an error.

Function which is called in the update statement:

Function : StripAllHTMLTags --
Purpose : Remove All HTML tags --

FUNCTION StripAllHTMLTags( p_HTMLString IN CLOB ) RETURN CLOB IS v_GoodHTML CLOB; BEGIN

v_GoodHTML := p_HTMLString;

      v_GoodHTML := REPLACE(v_GoodHTML, '&amp;', '&');
      v_GoodHTML := REPLACE(v_GoodHTML, '&nbsp;', ' ');
      v_GoodHTML := REPLACE(v_GoodHTML, ' &quot;', '''');

      OWA_PATTERN.CHANGE(v_GoodHTML, '<[^>]+>','','gi');
      OWA_PATTERN.CHANGE(v_GoodHTML, '\&[^;]+;', ' ', 'gi');


      RETURN v_GoodHTML;

END StripAllHTMLTags;

Thanks,
Raja

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Aug 05 2005 - 10:20:40 CDT