Oracle plsql出参clob类型的操作

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;
/

简单的使用过程，更深入的原理待以后研究。