Steps:
1. DBMS_LOB.createtemporary (store_in_list, TRUE); --获取lob locator
2. DBMS_LOB.OPEN(store_in_list, DBMS_LOB.LOB_READWRITE);--打开
3. dbms_lob.writeappend(store_in_list, LENGTH(v_result), v_result);--writeappend 在后面追加
4. DBMS_LOB.CLOSE(store_in_list); --关闭lob locator
Step1时执行下面的SQL,会发现多了一个 LOB_DATA
select u.tablespace, u.contents, u.segtype, u.extents, u.blocks, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P
where s.saddr = u.session_addr
AND UPPER(P.NAME)='DB_BLOCK_SIZE'
order by MB DESC;
TABLESPACE CONTENTS SEGTYPE EXTENTS BLOCKS MB
------------------------------- --------- --------- ---------- ---------- ----------
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_DATA 48 6144 48
TEMP TEMPORARY LOB_INDEX 1 128 1
TEMP TEMPORARY LOB_INDEX 1 128 1
Step4后,程序退出后,执行上面SQL,LOB_DATA已消除。
declare
clb clob;
ch varchar2(32767);
k number;
begin
dbms_lob.createtemporary(clb,true,dbms_lob.call);
for i in 1..1500 loop
ch:=lpad('o',32767,'Y');
dbms_lob.writeappend(clb,length(ch),ch);
end loop;
k:=dbms_lob.getlength(clb);
dbms_lob.freetemporary(clb); --使用完后手工释放
dbms_output.put_line('the clob length: '||k);
end;
/
简单的使用过程,更深入的原理待以后研究。