/*======================================================================
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 ;
