blob2clob/clob2blob研究

一。两种方法实现



blob到clob的转换

CREATE OR REPLACE FUNCTION blob2clob(v_blob_in IN BLOB) RETURN CLOB IS

  v_file_clob    CLOB;

  v_file_size    INTEGER := dbms_lob.lobmaxsize;

  v_dest_offset  INTEGER := 1;

  v_src_offset   INTEGER := 1;

  v_blob_csid    NUMBER := dbms_lob.default_csid;

  v_lang_context NUMBER := dbms_lob.default_lang_ctx;

  v_warning      INTEGER;

  v_length       NUMBER;

BEGIN

  dbms_lob.createtemporary(v_file_clob, TRUE);

  dbms_lob.converttoclob(v_file_clob,

                         v_blob_in,

                         v_file_size,

                         v_dest_offset,

                         v_src_offset,

                         v_blob_csid,

                         v_lang_context,

                         v_warning);

  RETURN v_file_clob;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Error found');

END;

/

CREATE OR REPLACE FUNCTION blob2clob(b BLOB) RETURN CLOB IS

  c CLOB;

  n NUMBER;

BEGIN

  IF (b IS NULL) THEN

    RETURN NULL;

  END IF;

  IF (length(b) = 0) THEN

    RETURN empty_clob();

  END IF;

  dbms_lob.createtemporary(c, TRUE);

  n := 1;

  WHILE (n + 32767 <= length(b)) LOOP

    dbms_lob.writeappend(c,

                         32767,

                         utl_raw.cast_to_varchar2(dbms_lob.substr(b,

                                                                  32767,

                                                                  n)));

    n := n + 32767;

  END LOOP;

  dbms_lob.writeappend(c,

                       length(b) - n + 1,

                       utl_raw.cast_to_varchar2(dbms_lob.substr(b,

                                                                length(b) - n + 1,

                                                                n)));

  RETURN c;

END;

会出现错误的地方

1.如果转换的blob文件长度为0则会报错   ORA-22994: 源偏移量超出源 LOB 的结尾

2. offset是in out参数，第一次传入后会被更改，如果是loop处理。应该将其值重新初始化。否则会报错ora-22994

如下

declare

  v_cl         clob;

  dest_offset  integer := 1;

  src_offset   integer := 1;

  lang_context integer := dbms_lob.default_lang_ctx;

  warning      integer;

begin

  for pr in (select pr_id, cmms_log

               from purchase_requisition

              where dbms_lob.getlength(cmms_log) > 0) loop

    dbms_lob.createtemporary(lob_loc => v_cl, cache => false);

    dbms_lob.convertToClob(dest_lob     => v_cl,

                           src_blob     => pr.cmms_log,

                           amount       => dbms_lob.lobmaxsize,

                           dest_offset  => dest_offset,

                           src_offset   => src_offset,

                           blob_csid    => dbms_lob.default_csid,

                           lang_context => lang_context,

                           warning      => warning);

    --dbms_output.put_line(warning);

    update purchase_requisition

       set cmms_log_clob = v_cl

     where pr_id = pr.pr_id;

  end loop;

  --commit;

  rollback;

exception

  when others then

    rollback;

    dbms_output.put_line('更新出错：' || sqlcode || ';' || sqlerrm);

end;

正确应该是

declare

  v_cl         clob;

  dest_offset  integer := 1;

  src_offset   integer := 1;

  lang_context integer := dbms_lob.default_lang_ctx;

  warning      integer;

begin

  dbms_output.put_line('开始执行');

  for pr in (select pr_id, cmms_log

               from purchase_requisition

              where dbms_lob.getlength(cmms_log) > 0) loop

    dbms_output.put_line(pr.pr_id);

    dest_offset  := 1;

    src_offset   := 1;

    lang_context := dbms_lob.default_lang_ctx

    dbms_lob.createtemporary(lob_loc => v_cl, cache => false);

    dbms_lob.convertToClob(dest_lob     => v_cl,

                           src_blob     => pr.cmms_log,

                           amount       => dbms_lob.lobmaxsize,

                           dest_offset  => dest_offset,

                           src_offset   => src_offset,

                           blob_csid    => dbms_lob.default_csid,

                           lang_context => lang_context,

                           warning      => warning);

    dbms_output.put_line(warning);

    update purchase_requisition

       set cmms_log_clob = v_cl

     where pr_id = pr.pr_id;

  end loop;

  commit;

exception

  when others then

    rollback;

    dbms_output.put_line('更新出错：' || sqlcode || ';' || sqlerrm);

end;

二。clob到blob的转换

同一，将函数换成 dbms_lob.converttoblob即可