Converte uma coluna CBLOD com conteudo cod64 para um arquivo.

Oracle_cblod_tofile.sql

create or replace procedure tofilejrgg(id integer) as
  l_file    UTL_FILE.FILE_TYPE;
  l_clob    CLOB;
  l_buffer  VARCHAR2(32767);
  l_amount  BINARY_INTEGER := 32767;
  l_pos     INTEGER := 1;
  l_filename varchar2(30);
  JRGG varchar2(32767);
BEGIN
  SELECT attach_content_bytes, attach_filename
  INTO   l_clob, l_filename
  FROM   CC_CEM_ATTACHMENTS
  WHERE  attach_id = id;
 
  l_file := UTL_FILE.fopen('IMG', l_filename,'w', 32767);
 
  LOOP
    DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
    JRGG := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(l_buffer)));
    UTL_FILE.put(l_file, JRGG);
    l_pos := l_pos + l_amount;
  END LOOP;
 
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- Expected end.
    UTL_FILE.fclose(l_file);
  WHEN OTHERS THEN
    UTL_FILE.fclose(l_file);
  RAISE;
END;
/