Chinaunix首页 | 论坛 | 博客
  • 博客访问: 28902
  • 博文数量: 8
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 10
  • 用 户 组: 普通用户
  • 注册时间: 2011-01-27 08:51
文章分类
文章存档

2014年(8)

我的朋友

分类: Oracle

2014-05-25 17:43:35

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

点击(此处)折叠或打开

  1. create or replace package base64 is

  2.   -- Author : GANGJH
  3.   -- Created : 2013-05-06 16:08:20
  4.   -- Purpose :
  5.   
  6.   -- Public type declarations
  7.    function to_base64(t in varchar2) return varchar2 ;
  8.    PROCEDURE to_base64(dest IN OUT NOCOPY CLOB,src IN CLOB ) ;
  9.    PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in blob ) ;
  10.    PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in bfile ) ;
  11.  
  12.    function from_base64(t in varchar2) return varchar2 ;
  13.    PROCEDURE from_base64(dest IN OUT NOCOPY CLOB, src CLOB ) ;


  14. end base64;
  15. /
  16. create or replace package body base64 is

  17.   function to_base64(t in varchar2) return varchar2 is
  18.   begin
  19.     return utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(t)));
  20.   end to_base64;
  21.   

  22. $IF dbms_db_version.ver_le_10_1 $THEN

  23.   /*for oracle 9i */
  24.   procedure convert_blob(clob_loc in clob, blob_loc in out nocopy blob) is
  25.     maxlen int ;
  26.     amount int := 1 ;
  27.     buffer RAW( 32767 );
  28.   begin
  29.     maxlen := dbms_lob.getlength(clob_loc) ;
  30.     while true loop
  31.       buffer := utl_raw.cast_to_raw( dbms_lob.substr( clob_loc, 16000, amount ) );
  32.       IF utl_raw.length( buffer ) > 0 THEN
  33.         dbms_lob.writeappend(blob_loc, utl_raw.length( buffer ), buffer );
  34.       END IF;

  35.       amount := amount + 16000;
  36.       EXIT WHEN amount > maxlen;
  37.     end loop ;
  38.   end ;


  39.   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src IN CLOB) IS
  40.     tmp blob ;
  41.    begin
  42.     dbms_lob.createtemporary(tmp, false) ;
  43.     convert_blob(src, tmp) ;
  44.     to_base64(dest, tmp) ;
  45.     dbms_lob.freetemporary(tmp) ;
  46.   END ;
  47. $ELSE
  48.   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src IN CLOB ) IS
  49.     v_blob blob;
  50.     v_blob_offset int :=1;
  51.     v_clob_offset int :=1 ;
  52.     v_blob_csid int := dbms_lob.default_csid ;
  53.     v_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  54.     v_warning integer ;
  55.   BEGIN
  56.     IF src IS NULL THEN
  57.       RETURN ;
  58.     END IF ;
  59.     dbms_lob.createtemporary(v_blob,false) ;
  60.     /*
  61.     converttoblob just on oracle 10g or laster
  62.      */
  63.     dbms_lob.converttoblob(v_blob,
  64.                           src,
  65.                           dbms_lob.getlength(src),
  66.                           v_blob_offset,
  67.                           v_clob_offset,
  68.                           v_blob_csid,
  69.                           v_lang_context,
  70.                           v_warning);
  71.     to_base64(dest, v_blob) ;
  72.     dbms_lob.freetemporary(v_blob) ;
  73.   END ;
  74. $END

  75.   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in blob) IS
  76.     --取 3 的倍數 因為如果需要按照64字符每行分行,所以需要是16的倍數,所以下面的長度必需為 48的倍數
  77.     sizeB integer := 6144;
  78.     buffer raw(6144);
  79.     offset integer default 1;
  80.     --coding varchar2(20000);
  81.   begin
  82.     loop
  83.        begin
  84.        dbms_lob.read(src, sizeB, offset, buffer);
  85.        exception
  86.          when no_data_found then
  87.            exit;
  88.        end;
  89.        offset := offset + sizeB;
  90.        dbms_lob.append(dest, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer))));
  91.     end loop;
  92.   END to_base64;
  93.   

  94.   PROCEDURE to_base64(dest IN OUT NOCOPY CLOB, src in bfile) IS
  95.     sizeB integer := 6144;
  96.     buffer raw(6144);
  97.     offset integer default 1;
  98.   begin
  99.     loop
  100.        begin
  101.        dbms_lob.read(src, sizeB, offset, buffer);
  102.        exception
  103.          when no_data_found then
  104.            exit;
  105.        end;
  106.        offset := offset + sizeB;
  107.        dbms_lob.append(dest, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(buffer))));
  108.     end loop;
  109.   END to_base64;

  110.   
  111.   PROCEDURE to_base64_ok(dest IN OUT NOCOPY CLOB, src in out nocopy bfile) is
  112.   v_blob blob;
  113.   dest_offset INTEGER :=1 ;
  114.   src_offset INTEGER :=1 ;
  115.   BEGIN
  116.     dbms_lob.createtemporary(v_blob, TRUE);
  117.     IF dbms_lob.isopen(src) <= 0 THEN
  118.        dbms_lob.open (src);
  119.        dbms_lob.loadblobfromfile(v_blob, src ,dbms_lob.lobmaxsize, dest_offset , src_offset );
  120.        dbms_lob.close (src);
  121.     ELSE
  122.        dbms_lob.loadblobfromfile(v_blob, src ,dbms_lob.lobmaxsize, dest_offset , src_offset );
  123.     END IF ;
  124.      
  125.     base64.to_base64(dest, v_blob) ;
  126.     dbms_lob.freetemporary( v_blob ) ;
  127.   end ;
  128.  
  129.   
  130.   
  131.   function from_base64(t in varchar2) return varchar2 is
  132.   begin
  133.     return utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(t)));
  134.   end from_base64;
  135.   
  136.   PROCEDURE from_base64(dest IN OUT NOCOPY CLOB, src CLOB ) is
  137.     n pls_integer := 0;
  138.     substring varchar2(2000);
  139.     substring_length pls_integer := 2000;
  140.   BEGIN
  141.     n := 0;
  142.   /*then we do the very same thing backwards - decode base64*/
  143.   while true loop
  144.     substring := dbms_lob.substr(src,
  145.                                  least(substring_length, substring_length * n + 1 - length(src)),
  146.                                  substring_length * n + 1);
  147.     if substring is null then
  148.       exit;
  149.     end if;
  150.     dest := dest|| from_base64(substring);
  151.     n := n + 1;
  152.   end loop;
  153.   END ;




  154. end base64;
  155. /

阅读(2950) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~