有学生问,在blog里保存了文本,怎么转成clob。方法如下:
有dbms包可以转换,不过它是个proc,需要封装下
- CREATE OR REPLACE FUNCTION f_blob2clob(p_blob BLOB) RETURN CLOB AS
- l_clob CLOB;
- amount NUMBER;
- dest_offset NUMBER;
- src_offset NUMBER;
- blob_csid NUMBER;
- lang_context NUMBER;
- warning NUMBER;
- BEGIN
- dbms_lob.createtemporary(l_clob, TRUE);
- amount := dbms_lob.getlength(p_blob);
- dest_offset := 1;
- src_offset := 1;
- blob_csid := dbms_lob.default_csid;
- lang_context := dbms_lob.default_lang_ctx;
- dbms_lob.converttoclob(l_clob,
- p_blob,
- amount,
- dest_offset,
- src_offset,
- blob_csid,
- lang_context,
- warning);
- dbms_output.put_line(l_clob);
- RETURN l_clob;
- EXCEPTION
- WHEN OTHERS THEN
- RETURN NULL;
- END;
然后用这个函数就可以了
- SQL> desc test;
- Name Type Nullable Default Comments
- ---- ---- -------- ------- --------
- B BLOB Y
- C CLOB Y
- SQL> col c form a20
- SQL> select * from test;
- B C
- - --------------------
- <
- 1 row selected
- SQL> update test set c = f_blob2clob(b);
- 1 row updated
- SQL> select * from test;
- B C
- - --------------------
- < 今天下午,神木县委宣传部此前的一位负责人
- 称,相关网帖已注意到,但睡着的并非副县长
- ,而是县长助理。
- 1 row selected