RE: OWA_PATTERN Error - Fixed
- From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 10 Aug 2005 13:47:00 -0400
This has been fixed with the workaround. It's a limitation of the function
owa_pattern.change as it converts the CLOB into VARCHAR2.
DECLARE
v_text varchar2(2000);
v_count integer;
v_clob clob;
v_result clob;
v_offset number := 1;
BEGIN
select round(dbms_lob.getlength( synopsis )/1000) a, synopsis
into v_count, v_clob
from program_synopsis
where rowid = 'AAAEimAAGAAADO6AAD';
for i in 1..v_count loop
v_text := dbms_lob.substr(v_clob, 1000, v_offset);
v_offset := v_offset + 1000;
OWA_PATTERN.CHANGE(v_text, '<[^>]+>','','gi');
v_result := v_result||v_Text;
end loop;
update program_synopsis
set synopsis_text= v_result
where rowid = 'AAAEimAAGAAADO6AAD';
END;
Thanks,
Raja.
-----Original Message-----
From: Subbiah, Nagarajan [mailto:Nagarajan.Subbiah@xxxxxxxx]
Sent: Friday, August 05, 2005 11:19 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » RE: OWA_PATTERN Error - Fixed
