yumcConsent/blob text replace.sql

CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) RETURN CLOB IS
   l_clob         CLOB;
   l_dest_offset  NUMBER := 1;
   l_src_offset   NUMBER := 1;
   l_lang_context NUMBER := dbms_lob.default_lang_ctx;
   l_warning      NUMBER;
BEGIN
   dbms_lob.createtemporary(l_clob, TRUE);
   dbms_lob.converttoclob(dest_lob     => l_clob,
                           src_blob     => l_blob,
                           amount       => dbms_lob.lobmaxsize,
                           dest_offset  => l_dest_offset,
                           src_offset   => l_src_offset,
                           blob_csid    => nls_charset_id('AL32UTF8'),
                           lang_context => l_lang_context,
                           warning      => l_warning);
    RETURN l_clob;
 END convert_to_clob;
 /

CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS
   l_blob         BLOB;
   l_dest_offset  NUMBER := 1;
   l_src_offset   NUMBER := 1;
   l_lang_context NUMBER := dbms_lob.default_lang_ctx;
   l_warning      NUMBER;
BEGIN
   dbms_lob.createtemporary(l_blob, TRUE);
   dbms_lob.converttoblob(dest_lob     => l_blob,
                          src_clob     => l_clob,
                          amount       => dbms_lob.lobmaxsize,
                          dest_offset  => l_dest_offset,
                          src_offset   => l_src_offset,
                          blob_csid    => nls_charset_id('AL32UTF8'),
                          lang_context => l_lang_context,
                          warning      => l_warning);
   RETURN l_blob;
END convert_to_blob;
/


UPDATE ema.eform_document a
    SET form_xml = convert_to_blob(
        REPLACE(convert_to_clob(form_xml),
        'http://192.168.101.95',
        'https://ema032.knuh.kr')
    )
    WHERE  a.use_yn = 'Y' 
        and a.last_yn = 'Y'
        -- and form_rid = '15241'
        and form_rid = '14891'
;