[ORACLE] Error - oracle input pattern or replacement parameters exceed 32K size limit

 

When the following error occurs like this

"oracle input pattern or replacement parameters exceed 32K size limit ... CLOB ..."

 

CREATE OR REPLACE FUNCTION REPLACE_CLOB( p_origin                          IN CLOB
                                	, p_search_text                     IN VARCHAR2
                                	, p_replace                         IN CLOB
) 
RETURN CLOB 
IS
    l_pos pls_integer;
    out_replace_clob CLOB := p_origin;
BEGIN
    l_pos := instr(p_origin, p_search_text);

    IF l_pos > 0 THEN
        WHILE l_pos > 0 LOOP
            out_replace_clob := substr(out_replace_clob, 1, l_pos-1)
            || p_replace
            || substr(out_replace_clob, l_pos + LENGTH(p_search_text));

            l_pos := instr(out_replace_clob, p_search_text);
        END LOOP;
        RETURN out_replace_clob;
    END IF;

    RETURN p_origin;
END REPLACE_CLOB;

-- v_huge_sql := REPLACE_CLOB(v_huge_sql, '[[REPLACE_LOCATION_IN_QUERY]]'    , v_huge_replace_text);

 

be the happy Gosu.

woojja ))*

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\