这是读取clob的函数,你可以做相应的修改 查询CLOB的PL/SQL函数:getclob create or replace function getclob( table_name in varchar2, field_id in varchar2, field_name in varchar2, v_id in number, v_pos in number) return varchar2 is lobloc clob; buffer varchar2(32767); amount number := 2000; offset number := 1; query_str varchar2(1000); begin query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id '; --initialize buffer with data to be found EXECUTE IMMEDIATE query_str INTO lobloc USING v_id; offset:=offset+(v_pos-1)*2000; --read 2000 varchar2 from the buffer dbms_lob.read(lobloc,amount,offset,buffer); return buffer; exception when no_data_found then return buffer; end; /用法说明:用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual; 可以从CLOB字段中取2000个字符到partstr中, 编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
查询CLOB的PL/SQL函数:getclob
create or replace function getclob(
table_name in varchar2,
field_id in varchar2,
field_name in varchar2,
v_id in number,
v_pos in number) return varchar2
is
lobloc clob;
buffer varchar2(32767);
amount number := 2000;
offset number := 1;
query_str varchar2(1000);
begin
query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';
--initialize buffer with data to be found
EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
offset:=offset+(v_pos-1)*2000;
--read 2000 varchar2 from the buffer
dbms_lob.read(lobloc,amount,offset,buffer);
return buffer;
exception
when no_data_found then
return buffer;
end;
/用法说明:用select getclob(table_name,field_id,field_name,v_id,v_pos) as partstr from dual;
可以从CLOB字段中取2000个字符到partstr中,
编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。
http://www.itpub.net/showthread.php?s=74f01fead067788f43b0a1b707943be1&threadid=7723