RE: OWA_PATTERN Error

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@xxxxxxxx] 
Sent: Wednesday, August 03, 2005 12:22 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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, '&', '&');
      v_GoodHTML := REPLACE(v_GoodHTML, ' ', ' ');
      v_GoodHTML := REPLACE(v_GoodHTML, ' "', '''');

      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

Other related posts: