oracle9中使用預存程序處理CLOB文字資料流

/*======================================================================
   PROGRAM        getClob.sql
   AUTHOR        李曉輝
======================================================================
 CHANGE LOG
 -----------
 DATE  2007-02-07        VERSION   1.0     DEVELOPER 李曉輝
======================================================================
======================================================================*/
create or replace procedure getClob is
       bloc clob;
       bloc_2 clob;--取得的CLOB位移量
       amount number;
       amount_2 number;--CLOB位移量的大小
       offset number :=1;
       offset_2 number :=1;--取得CLOB位移量的起始位置
       buffer varchar2(32767);
       buffer_2 varchar2(32767);--CLOB位移量轉化的字串
begin
       for MyRecord in (select t.* from sygovdat.cms1170136235569 t) loop
           select c2 into bloc from sygovdat.cms1170136235569 where contentid=MyRecord.Contentid;
           amount:=dbms_lob.getlength(bloc);
           if(amount  is not null)then
           begin
             dbms_lob.read(bloc,amount,offset,buffer );
             insert into temp_clob  (c3,contentid) values (buffer,MyRecord.Contentid);
           exception
             when others then
               Dbms_Output.put_line(amount);
           end;
           end if;
           select C1170136235581 into bloc_2 from sygovdat.cms1170136235569 where contentid=MyRecord.Contentid;
           amount_2:=dbms_lob.getlength(bloc);
           if(amount_2  is not null)then
           begin
             dbms_lob.read(bloc_2,amount_2,offset_2,buffer_2 );
             insert into temp_clob2  (c4,contentid) values (buffer_2,MyRecord.Contentid);
           exception
             when others then
               Dbms_Output.put_line(amount);
           end;
           end if;
       end loop;
       commit;
end ;