Oracle Pl/Sql Utl_Url. Escape Clob
I Have a Large Blob Object Which I Need to Convert to Clob in Base64 with Encoded Characters, I Have Tried to Use Utl_Encode. Base64_Encode but It Does Not...
I have a large BLOB object which I need to convert to CLOB in base64 with encoded characters, I have tried to use utl_encode.base64_encode but it does not escape special characters like ={}+@~ etc. does anyone know how to escape those special characters with utl_url.escape? my code that I have:
PROCEDURE base64encode ( i_blob in blob, io_clob in out nocopy clob) IS
l_step pls_integer := 22500;
l_converted VARCHAR2(32767);
l_buffer_size_approx pls_integer := 1048576;
l_buffer CLOB;
BEGIN
dbms_lob.createtemporary(l_buffer, TRUE, dbms_lob.call);
FOR i IN 0 .. trunc((dbms_lob.getlength(i_blob) - 1 )/l_step)
LOOP
l_converted := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(i_blob, l_step, i * l_step + 1)));
dbms_lob.writeappend(l_buffer, length(l_converted), l_converted);
IF dbms_lob.getlength(l_buffer) >= l_buffer_size_approx THEN
dbms_lob.append(io_clob, l_buffer);
dbms_lob.trim(l_buffer, 0);
END IF;
END LOOP;
dbms_lob.append(io_clob, l_buffer);
dbms_lob.freetemporary(l_buffer);
END base64encode;
and if I try to convert BLOB to base64 it gives me output like: 8J5G7Ty8t3Pn7T+9ce/+w/c//nn/Jd1VDTKhadyLoNKDDx/Cl+4/SHN7jFjSVFrj <- not full base64 just single line,
But I need the output like this, with escaped characters:
8J5G7Ty8t3Pn7T%2B9ce%2F%2Bw%2Fc%2F%2Fnn%2FJd1VDTKhadyLoNKDDx%2FCl%2B4%2FSHN7jFjSVFrj
the result is large CLOB text, it does not fit in varchar2 variable.
2 Answers
So I have came up with a working solution, this code decodes base64 clob and escapes reserved chars. It is a bit slow, but it does the work. first, you need to call base64encode function from my main question, then pass the clob to this function. I hope it will help others too.
FUNCTION encode64_clob(in_clob CLOB) RETURN CLOB IS
temp_chunk VARCHAR(4000);
l_enter NUMBER := 1;
l_old_ent NUMBER := 1;
l_ent_cnt NUMBER := 1;
l_out CLOB;
BEGIN
LOOP
l_enter := instr(in_clob, chr(10), 1, l_ent_cnt);
IF l_enter = 0 THEN
temp_chunk := regexp_replace(substr(in_clob, l_old_ent, length(in_clob)), '[[:space:]]+', '');
l_out := l_out || temp_chunk;
EXIT;
END IF;
temp_chunk := regexp_replace(substr(in_clob, l_old_ent, l_enter - l_old_ent), '[[:space:]]+', '');
temp_chunk := utl_url.escape(url => temp_chunk, escape_reserved_chars => TRUE, url_charset => 'UTF-8');
l_out := l_out || temp_chunk;
l_ent_cnt := l_ent_cnt + 1;
l_old_ent := l_enter;
END LOOP;
RETURN l_out;
END;
APEX_UTIL.URL_ENCODE might do the trick.
SELECT APEX_UTIL.URL_ENCODE('8J5G7Ty8t3Pn7T+9ce/+w/c//nn/Jd1VDTKhadyLoNKDDx/Cl+4/SHN7jFjSVFrj')
FROM dual;
Output:
8J5G7Ty8t3Pn7T%2B9ce%2F%2Bw%2Fc%2F%2Fnn%2FJd1VDTKhadyLoNKDDx%2FCl%2B4%2FSHN7jFjSVFrj
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
