clob类型转换为string类型的orcal函数

 create   or   replace   function   clob_to_string(
          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   ';
      EXECUTE   IMMEDIATE   query_str   INTO   lobloc   USING   v_id;
      offset:=offset+(v_pos-1)*2000;
      dbms_lob.read(lobloc,amount,offset,buffer);
               return   buffer;
exception
        when   no_data_found   then
               return   buffer;
end;

 

 

CREATE OR REPLACE FUNCTION CLOB_TO_STRING (clob_in IN CLOB) RETURN CLOB
   AS
    offset INTEGER;
    amount INTEGER;
    output VARCHAR2(32767);
    BEGIN
        offset := 1;
        amount := 32767;
        LOOP
          DBMS_LOB.read(clob_in,amount,offset,output);
          --DBMS_OUPUT.PUT_LINE(output);
          offset := offset + amount;
        END LOOP;
        return output;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        return output;
    END CLOB_TO_STRING;

 

SELECT
c.catentry_id,xcs.store_id,xcs.field2 as author,
(select xcac.content from  X_CATENTRY_ATTR_CLOB xcac where  c.catentry_id = xcac.catentry_id(+) and xcs.store_id = xcac.store_id(+))
 as fieldsAll,
sc.catalog_id,c.partnumber,g.member_id,null as parentCatentry_id,c.catenttype_id,