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 ))*

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

반응형

+ Recent posts