有学生问,在blob里保存了文本,怎么转成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