As a result of a question I asked back in April (see
3403446 I have this function which takes in a string such as 'publicise, publicize, italicise, italicize' and spits out 'publicise,italicise', removing the redundant duplication of the -ise, -ize suffix:
create or replace function deise (instring varchar2)
return varchar2
is
v_outstring varchar2(32000);
begin
WITH T AS
(SELECT 1 rn , instring
TXT
FROM DUAL)
select substr( string, 2 ) into v_outstring
from (
select TXT , rn, pos, row_number()over(partition by regexp_replace(txt,'[ize|iseorder by pos ) n
from ( SELECT TXT , rn, position pos
FROM T
MODEL
RETURN UPDATED ROWS
partition by ( rn)
DIMENSION BY (0 POSITION)
MEASURES (TXT , NVL(LENGTH(REGEXP_REPLACE(TXT,'[^,]+','')),0)+1 NB_MOT)
RULES
(TXT[FOR POSITION FROM 1 TO NB_MOT[0] INCREMENT 1] =
REGEXP_SUBSTR(TXT[0],'[^,]+',1,CV(POSITION)) )) t)
where n=1
model
return updated rows
partition by ( rn )
dimension by ( row_number() over (partition by rn ORDER BY pos ) as position )
measures ( cast( txt as varchar2(4000) ) as string )
rules
upsert
iterate( 1000 )
until ( presentv(string[iteration_number+2],1,0) = 0 )
( string[0] = string[0] || ',' || string[iteration_number+1] )
order by rn;
return v_outstring;
end;
Works great, usually. Sometimes, however, I get 'ORA-25137 Data Value Out of Range' errors, and the line it lists as being at fault is the one which reads
measures ( cast( txt as varchar2(4000) ) as string ).
I have tried bumping that up to varchar2(8000) or, indeed, to varchar2(4001), but it refuses to accept anything larger than 4000. (This is 10g Release 2, 10.2.0.4).
Is there a simple way to re-write this function so that the cast can be done to (say) a CLOB?