PB读取Oracle中Clob字段内容

布满荆棘的人生 2024-03-26 18:06 75阅读 0赞

## 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的内容，拼接成完整数据 ###

Integer	li_blym
    Long	i , ll_len ,ll_blbh 
    String 	ls_temp,ls_data,ls_data1
    
    ls_temp = " blbh="+String(ll_blbh)+" and blym= "+String(li_blym)
    Select dbms_lob.getlength(html) Into :ll_len From ys_zy_bl_html Where blbh = :ll_blbh And blym = :li_blym;
    
    Long i
    i = 1
    Do
    	//调用ORACLE的函数GetClob
    	Select getclob('ys_zy_bl_html','html',: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