Thursday, November 17, 2022

Oracle PLQSL Script to convert Binary data (BLOB) to BASE64 encoded data (UTL_ENCODE.BASE64)

 







In this post, we have given a small script to convert BINARY data to BASE64 encoded data.  


Before seeing the script, lets have a quick background,  


Background: We have some binary data that we want to send across a network. Let's assume, we send them as bits and bytes over the network in a raw format.  


Possibility 1: Few combinations of Binary data may be considered as control characters by the intermediate hardware components. (modem). This will result Unexpected behaviour.  


Possibility 2: Binary data could be corrupted because the underlying protocol might think that you've entered a special character combination (like how FTP translates line endings). 


So, to avoid all these, people encode the binary data into characters. Base64 is one of these types of encodings. 

 

Script: 

CREATE OR REPLACE FUNCTION convert_blob_to_base64(pic_blob IN BLOB)
RETURN CLOB
IS
  lc_clob CLOB;
  ln_chunk_size PLS_INTEGER := 24000;
BEGIN
  FOR i IN 0..TRUNC((DBMS_LOB.GETLENGTH(pic_blob) - 1 ) / ln_chunk_size)
  LOOP
    lc_clob := lc_clob 
              || UTL_RAW.CAST_TO_VARCHAR2
	 	       ( UTL_ENCODE.BASE64_ENCODE
			        ( DBMS_LOB.SUBSTR( pic_blob,
                                       ln_chunk_size,
                                       i * ln_chunk_size + 1
                                     )
                    )
               );
  END LOOP;
  RETURN lc_clob;
END convert_blob_to_base64; 


0 Responses to “Oracle PLQSL Script to convert Binary data (BLOB) to BASE64 encoded data (UTL_ENCODE.BASE64)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.