SQL & PL/SQL

Hi,

I made a function to replace latin american accented letters to a html entitie. Kind of html_entities function from PHP.

So, insted of directly output the html with htp.p, i use this function. Example:

htp.p(html(Mário));

will output:

Mário

But This functions is slow. Does anynoe know a way to optimize regexp_replace? In C, I used to pre-compile the regular expression patterns to speed up.

Anyone has a tip to improve the performance off my function?

This is my function:

function html(wstr in varchar2, nl2br in boolean default false) return varchar2
is
    wretorno   varchar2(32767);
begin
    wretorno := null;
    if wstr is not null then
        wretorno := wstr;
        -- To avoid cascade replacement, I mask the html tags already formatted
        wretorno := regexp_replace(wstr, '&([a-zA-Z]{3,6});', '_:\1:_');
        -- Now I replace the caracters I need to.
        wretorno := replace(wretorno, '&', '&');
        wretorno := regexp_replace(wretorno, '([áéíóúýÁÉÍÓÚÝ])', '&\1acute;');
        wretorno := regexp_replace(wretorno, '([ãõñÃÕÑ])'      , '&\1tilde;');
        wretorno := regexp_replace(wretorno, '([âêîôûÂÊÎÔÛ])'  , '&\1circ;' );
        wretorno := regexp_replace(wretorno, '([àèìòùÀÈÌÒÙ])'  , '&\1grave;');
        wretorno := regexp_replace(wretorno, '([äëïöüÿÄËÏÖÜ])' , '&\1uml;'  );
        wretorno := regexp_replace(wretorno, '([çÇ])'          , '&\1cedil;');
        wretorno := translate(wretorno, 'áàâãäéèêëïíìîóòôöõúùûüçýÿÁÀÂÃÄÉÈÊËÏÍÌÎÔÖÓÒÕÙÛÜÚÇÝ', 'aaaaaeeeeiiiiooooouuuucyyAAAAAEEEEIIIIOOOOOUUUUCY');
        wretorno := replace(wretorno, 'æ', 'æ');
        wretorno := replace(wretorno, 'Æ', 'Æ');
        wretorno := replace(wretorno, '©', '©');
        wretorno := replace(wretorno, '€', '€');
        wretorno := replace(wretorno, '®', '®');
        wretorno := replace(wretorno, '§', '§');
        wretorno := replace(wretorno, '´', '´');
        wretorno := replace(wretorno, '`', '`');
        wretorno := replace(wretorno, '~', '˜');
        wretorno := replace(wretorno, '¨', '¨');
        -- Unmask html tags from before
        wretorno := regexp_replace(wretorno, '_:([a-zA-Z]{3,6}):_', '&\1;');
        if nl2br then
            wretorno := replace(wretorno, chr(13)||chr(10), chr(10));
            wretorno := replace(wretorno, chr(10), '<br />');
        end if;
    end if;
    return(wretorno);
end;