Toad World® Forums

Writing to Clobs


#1

How can we save some data in clob , so you can create function converting rowset data into clob.
I my example rowset is represented as collection. An we have:

CREATE OR REPLACE
TYPE varchar2_long_list
AS TABLE OF VARCHAR2(32000)
/

function write2clob(v_rows_in in Tvarchar2_long_list) return clob is
v_file clob := null;
begin
if dbms_lob.istemporary(v_file)>0 then
dbms_lob.FreeTemporary(v_file);
end if;

if v_rows_in.count > 0 then --if there is something
dbms_lob.CreateTemporary(v_file,True);
for line in v_rows_in.first … v_rows_in.last loop
dbms_lob.append(v_file,v_rows_in(line)||chr(10));
end loop;
return v_file;
else
dbms_output.put_line('No data found ');
return null;
end if;
exception
when others then
if dbms_lob.istemporary(v_file)>0 then
dbms_lob.FreeTemporary(v_file);
end if;
raise;
end write2clob;

Ps. How can we fill varchar2_long_list??
declare
x varchar2_long_list;
begin
select col_name bulk collect into x from table;

To speed up writing large clobs (100KB<) we can reduce appending clob type:

FUNCTION write2clob(v_rows_in in Tvarchar2_long_list) return clob is
v_file clob := null;
temp_row varchar2(32000);
piece_size constant pls_integer:=16000;
i pls_integer;
begin
if dbms_lob.istemporary(v_file)>0 then
dbms_lob.FreeTemporary(v_file);
end if;

if v_rows_in.count > 0 then
dbms_lob.CreateTemporary(v_file,True);
for line in v_rows_in.first … v_rows_in.last loop
temp_row:=temp_row||v_rows_in(line)||chr(10); --filling buffer
if length(temp_row)>=piece_size then
dbms_lob.append(v_file,temp_row); --save clob
temp_row:=null; --clean buffer
end if;
end loop;
dbms_lob.append(v_file,temp_row);

    return v_file;
else
    dbms_output.put_line('No data found ');
    return null;
end if;
exception
    when others then
         if dbms_lob.istemporary(v_file)>0 then
                dbms_lob.FreeTemporary(v_file);
         end if;
         raise;

end write2clob;

Regards Piter