oracle 對blob或clob類型base64 編碼

oracle 發送email 時用到,單獨分成一個package

create or replace package base64 is

  -- Author  : GANGJH
  -- Created : 2013-05-06 16:08:20
  -- Purpose :

  -- Public type declarations
   function to_base64(t in varchar2) return varchar2 ;
   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB,src IN CLOB ) ;
   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in blob ) ;
   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in bfile ) ;

   function from_base64(t in varchar2) return varchar2 ;
   PROCEDURE from_base64(dest IN OUT NOCOPY CLOB, src CLOB ) ;


end base64;
/
create or replace package body base64 is

  function to_base64(t in varchar2) return varchar2 is
  begin
    return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
  end to_base64;


$IF dbms_db_version.ver_le_10_1 $THEN

  /*for oracle 9i */
  procedure convert_blob(clob_loc in clob, blob_loc in out nocopy blob) is
    maxlen int ;
    amount int := 1 ;
    buffer RAW( 32767 );
  begin
    maxlen := dbms_lob.getlength(clob_loc) ;
    while true loop
      buffer := utl_raw.cast_to_raw( dbms_lob.substr( clob_loc, 16000, amount ) );
      IF utl_raw.length( buffer ) > 0 THEN
        dbms_lob.writeappend(blob_loc, utl_raw.length( buffer ), buffer );
      END IF;

      amount := amount + 16000;
      EXIT WHEN amount > maxlen;
    end loop ;
  end ;


  PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src IN CLOB) IS
    tmp blob ;
   begin
    dbms_lob.createtemporary(tmp, false) ;
    convert_blob(src, tmp) ;
    to_base64(dest, tmp) ;
    dbms_lob.freetemporary(tmp) ;
  END ;
$ELSE
  PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src IN CLOB ) IS
    v_blob blob;
    v_blob_offset int :=1;
    v_clob_offset int :=1 ;
    v_blob_csid int := dbms_lob.default_csid ;
    v_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
    v_warning integer ;
  BEGIN
    IF src IS NULL THEN
      RETURN ;
    END IF ;
    dbms_lob.createtemporary(v_blob,false) ;
    /*
    converttoblob just on oracle 10g or laster
     */
    dbms_lob.converttoblob(v_blob,
                          src,
                          dbms_lob.getlength(src),
                          v_blob_offset,
                          v_clob_offset,
                          v_blob_csid,
                          v_lang_context,
                          v_warning);
    to_base64(dest, v_blob) ;
    dbms_lob.freetemporary(v_blob) ;
  END ;
$END

  PROCEDURE  to_base64(dest IN OUT NOCOPY CLOB, src in blob) IS
    --取 3 的倍數 因為如果需要按照64字符每行分行，所以需要是16的倍數，所以下面的長度必需為 48的倍數
    sizeB integer := 6144;
    buffer raw(6144);
    offset integer default 1;
    --coding varchar2(20000);
  begin
    loop
       begin
       dbms_lob.read(src, sizeB, offset, buffer);
       exception
         when no_data_found then
           exit;
       end;
       offset := offset + sizeB;
       dbms_lob.append(dest, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer))));
    end loop;
  END to_base64;


  PROCEDURE  to_base64(dest IN OUT NOCOPY CLOB, src in bfile) IS
    sizeB integer := 6144;
    buffer raw(6144);
    offset integer default 1;
  begin
    loop
       begin
       dbms_lob.read(src, sizeB, offset, buffer);
       exception
         when no_data_found then
           exit;
       end;
       offset := offset + sizeB;
       dbms_lob.append(dest, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer))));
    end loop;
  END to_base64;


  PROCEDURE to_base64_ok(dest IN OUT NOCOPY CLOB, src in out nocopy bfile) is
  v_blob blob;
  dest_offset INTEGER :=1 ;
  src_offset INTEGER :=1 ;
  BEGIN
    dbms_lob.createtemporary(v_blob, TRUE);
    IF dbms_lob.isopen(src) <= 0 THEN
       dbms_lob.open (src);
       dbms_lob.loadblobfromfile(v_blob, src ,dbms_lob.lobmaxsize, dest_offset , src_offset );
       dbms_lob.close (src);
    ELSE
       dbms_lob.loadblobfromfile(v_blob, src ,dbms_lob.lobmaxsize, dest_offset , src_offset );
    END IF ;

    base64.to_base64(dest, v_blob) ;
    dbms_lob.freetemporary( v_blob ) ;
  end ;



  function from_base64(t in varchar2) return varchar2 is
  begin
    return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
  end from_base64;

  PROCEDURE from_base64(dest IN OUT NOCOPY CLOB, src CLOB ) is
    n                pls_integer := 0;
    substring varchar2(2000);
    substring_length pls_integer := 2000;
  BEGIN
    n := 0;
  /*then we do the very same thing backwards - decode base64*/
  while true loop
    substring := dbms_lob.substr(src,
                                 least(substring_length, substring_length * n + 1 - length(src)),
                                 substring_length * n + 1);
    if substring is null then
      exit;
    end if;
    dest := dest|| from_base64(substring);
    n := n + 1;
  end loop;
  END ;




end base64;
/