PB读取Oracle中Clob字段内容

PB读取Oracle中Clob字段内容

现象：
直接用Select或者SelectBlob读取,会出现Sql报错或者数据丢失问题。解决方法如下：

1. 创建方法getclob，一次读取2000个字符：

create or replace function getclob(table_name in varchar2, --表名
                                   field_name in varchar2, --CLOB字段名
                                   v_tj       in varchar2, --表查询条件
                                   v_pos      in number    --字符起始位置)

return varchar2 is
  buffer    varchar2(32767);
  lobloc    clob;
  amount    number := 2000;
  query_str varchar2(1000);
begin
  query_str := 'select ' || field_name || ' from ' || table_name ||
               ' where ' || v_tj || '';
  --initialize buffer with data to be found
  EXECUTE IMMEDIATE query_str
    INTO lobloc;
  --read 2000 varchar2 from the buffer
  dbms_lob.read(lobloc, amount, v_pos + 1, buffer);
  return buffer;
exception
  when no_data_found then
    return buffer;
end;

2. PB代码中：分段读取CLOB的内容，拼接成完整数据

// wf_get_clobnr( as_id )  
//		从表MKB_BZ_ZHISHIKXQ中Clob字段[xiangqingnr]的值

Long		i , ll_len  
String 	ls_temp,ls_data,ls_data1

// 筛选条件
ls_temp = " id='"+ as_id + "'"
Select dbms_lob.getlength(xiangqingnr) Into :ll_len 
From MKB_BZ_ZHISHIKXQ
Where id = :as_id ;   

i = 1
Do
	//调用ORACLE的函数GetClob
	Select getclob('MKB_BZ_ZHISHIKXQ', 'xiangqingnr' ,:ls_temp,:i ) 
		Into :ls_data From dual;
	ls_data1 = ls_data1 + ls_data
	ll_len = ll_len - 2000
	i = i + 2000
Loop While ll_len >= 0

Return ls_data1