Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Casting causes a problem -how to fix?

Catfive LanderDec 21 2009 — edited Dec 23 2009
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?
This post has been answered by Karthick2003 on Dec 22 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 20 2010
Added on Dec 21 2009
18 comments
2,022 views