CLOB與BLOB的轉換

CLOB轉換為BLOB在Oracle中可以通過utl_raw和dbms_lob兩個包來實現。

 

1) 在utl_raw中FUNCTION cast_to_raw(c IN VARCHAR2 CHARACTER SET ANY_CS) RETURN RAW

    用來把字串轉為raw。由於傳入的varchar2必然受到32767位元組長度的限制。

SQL> set serveroutput on size 2000;
SQL>
SQL> declare
  2    b_blob blob;
  3    c_clob varchar2(32767);
  4  begin
  5    for i in 1..32767 loop
  6     c_clob := c_clob ||'a';
  7    end loop;
  8    dbms_output.put_line(dbms_lob.getlength(c_clob));
  9    b_blob := utl_raw.cast_to_raw(c_clob);
 10    dbms_output.put_line(dbms_lob.getlength(b_blob));
 11  end;
 12  /
 
32767
32767
 
PL/SQL procedure successfully completed

 

但把 c_clob varchar2(32767)　換為　c_clob clob會怎樣呢

SQL> declare
  2    b_blob blob;
  3    c_clob clob;
  4  begin
  5    for i in 1..32767 loop
  6     c_clob := c_clob ||'a';
  7    end loop;
  8    dbms_output.put_line(dbms_lob.getlength(c_clob));
  9    b_blob := utl_raw.cast_to_raw(c_clob);
 10    dbms_output.put_line(dbms_lob.getlength(b_blob));
 11  end;
 12  /
  
ORA-06502: PL/SQL: 數字或值錯誤
ORA-06512: 在 line 10

 

SQL> declare
  2    b_blob blob;
  3    c_clob clob;
  4  begin
  5    for i in 1..16383 loop   -- 修改這兒的長度（如果16384就會報錯）
  6     c_clob := c_clob ||'a';
  7    end loop;
  8    dbms_output.put_line(dbms_lob.getlength(c_clob));
  9    b_blob := utl_raw.cast_to_raw(c_clob);
 10    dbms_output.put_line(dbms_lob.getlength(b_blob));
 11  end;
 12  /
 
16383
16383
 
PL/SQL procedure successfully completed

 

我們再來試試中文如何

SQL> declare
  2    b_blob blob;
  3    c_clob clob;
  4  begin
  5    for i in 1..16383 loop
  6     c_clob := c_clob ||'鄧';
  7    end loop;
  8    dbms_output.put_line(dbms_lob.getlength(c_clob));
  9    b_blob := utl_raw.cast_to_raw(c_clob);
 10    dbms_output.put_line(dbms_lob.getlength(b_blob));
 11  end;
 12  /
 
16383
32766
 
PL/SQL procedure successfully completed

 

看來cast_to_raw對於clob的型別最大是支援16383字元長度（一個漢字只當一個字元長度）。

 

2) DBMS_LOB中

  PROCEDURE convertToBlob(dest_lob IN OUT NOCOPY  BLOB,
                          src_clob       IN        CLOB CHARACTER SET ANY_CS,
                          amount         IN             INTEGER,
                          dest_offset    IN OUT         INTEGER,
                          src_offset     IN OUT         INTEGER,
                          blob_csid      IN             NUMBER,
                          lang_context   IN OUT         INTEGER,
                          warning        OUT            INTEGER);

 

SQL> declare
  2    b_blob    blob;
  3    c_clob    clob;
  4    l_lang    NUMBER := DBMS_LOB.default_lang_ctx;
  5    i_dest_offset number := 1;
  6    i_src_offset  number :=1;
  7    l_warning NUMBER;
  8  begin
  9    DBMS_LOB.CREATETEMPORARY(b_blob,TRUE,2);
 10    for i in 1 .. 40000 loop
 11      c_clob := c_clob || '鄧';
 12    end loop;
 13    dbms_output.put_line(dbms_lob.getlength(c_clob));
 14    dbms_lob.convertToBlob(b_blob,
 15                           c_clob,
 16                           dbms_lob.lobmaxsize,
 17                           i_dest_offset,
 18                           i_src_offset,
 19                           DBMS_LOB.default_csid,
 20                           l_lang,
 21                           l_warning);
 22    dbms_output.put_line(dbms_lob.getlength(b_blob));
 23  end;
 24  /
 
40000
80000

 

結論：字元轉換為位元組：對於小於32767的字串可以使用utl_raw包；對於超長的字元必須使用dbms_lob包.

 

注：要計算ＣＬＯＢ的位元組長度，要先轉換為blob型別，再用dbms_lob.getlength來計算長度.

  lengthb是不支援ＣＬＯＢ和NCLOB的.