mysql blob转成clob_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的.