[PL/SQL] Convert BLOB to CLOB

So you decided for some reasons that a BLOB would be a good type choice to store something for which the CLOB was invented, but now you'd like to take full advantage of all the CLOB functions or you need to query that XML you stored.

After blaming the gods for that poor choice, you may want to ease your pain by converting it to a CLOB:

 DECLARE  
   
 l_blob BLOB;  
 l_clob CLOB;  
 l_dest_offsset PLS_INTEGER := 1;  
 l_src_offsset PLS_INTEGER := 1;  
 l_lang_context PLS_INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;  
 l_warning PLS_INTEGER;  
   
 BEGIN  
   
 SELECT myBlob  
 INTO l_blob  
 FROM myTable  
 WHERE myConditions;  
   
 DBMS_LOB.CONVERTTOCLOB(  
      dest_lob => l_clob,  
      src_blob => l_blob,  
      amount => DBMS_LOB.LOBMAXSIZE,  
      dest_offset => l_dest_offsset,  
      src_offset => l_src_offsset,  
      blob_csid => NLS_CHARSET_ID('YOUR_CHARSET'), --optional, you may also use DBMS_LOB.DEFAULT_CSID if you don't have specific requirements  
      lang_context => l_lang_context,  
      warning => l_warning  
 );  
   
 END;  

You'll then have your converted BLOB in the l_clob variable