Hi,

I have a table with 6 CLOB's. I want to read the CLOB values into a text file. Currently I am able to read one CLOB column at a time into a text file. I am using the following procedure.

Now I want to read all the CLOB columns based on a unique key into a text file. Can somebody help me with this?


Your help is greatly appreciated...


create or replace procedure myclob
(l clob file in out utl_file.file_type)
is
buffer varchar2(1000);
offset number(5);
amount number(5);
flag number(5);
locator clob;
f2 utl_file.file_type;
begin
locator :=l;
offset:=1;
amount :=1000;
f2 :=file;
flag :=1;
while (flag = 1) loop
dbms_lob.read(locator,amount,offset,buffer);
if (amount = 0) then
flag:=0;
end if;
offset:=offset+amount;
amount :=1000;

utl_file.put_line(file,buffer);
end loop;
dbms_output.put_line('-------- procedure end ----------------');
exception
WHEN NO_DATA_FOUND
THEN
BEGIN
dbms_output.put_line ('End of LOB value reached');
END;
when others
then
begin
dbms_output.put_line(' The trapped error is :'||sqlerrm);
end;

end;


declare
f utl_file.file_type; -- For file handle
l clob; -- For clob locator
begin
f:=utl_file.fopen('/oracle/ora81/utlfiles','test1.txt','w');
select work_log into l from TEST_CLOB where id=1;
myclob(l,f);
utl_file.fclose(f);
exception
when others then
begin
dbms_output.put_line(' The error in main :'||sqlerrm);
end;
end;
/