Oracle plsql出参clob类型的操作

来源：互联网发布：pdf制作软件编辑：程序博客网时间：2024/06/07 07:52

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) MBfrom v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER Pwhere 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         48TEMP                            TEMPORARY LOB_DATA          48       6144         48TEMP                            TEMPORARY LOB_INDEX          1        128          1TEMP                            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;
/

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

0 0