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