Home » SQL & PL/SQL » SQL & PL/SQL » inserting large blob images
inserting large blob images [message #241314] Tue, 29 May 2007 03:51
shamsallana
Messages: 2
Registered: May 2007
Junior Member
hi!
i wrote the following script to transfer fields id and image(blob) in table A at database 1 over a db-link to fields id and image(blob) in table B at database 2. The problem is that in this script i am reading max 2000 bytes from image field in tableA and inserting them in tableB over the link.but how can i insert images having lengths, say 1MB. we can't read in buffer more than 2000 bytes at any one time and can't store more than 32767 of any blob data in pl/sql variable that we read (kindly correct me if i m wrong). Please help!

conn user/pass@string -- connecting to database 1
create database link linkr
connect to database2
identified by pass
using 'tns_entry_here_for_database2';

declare
cursor c1 is select id,image,dbms_lob.getlength(image) as len from tableA;
idvar varchar2(12);
c blob;
cbuff varchar2(32767);
read_size binary_integer :=2000;
read_offset integer :=1;
length integer;

Begin

for i in c1 loop
idvar := i.id
c := i.image;
length := i.len;

if(length >= 2000) then
dbms_lob.read(c,read_size,read_offset,cbuff);
else if(length = 0) then
cbuff := '1010'; --testing
else
dbms_lob.read(c,length,read_offset,cbuff);
end if;
end if;
insert into tableB@linkr (id,image)
values(idvar,cbuff);
commit;

end loop;
end;
/
drop database link linkr
disconnect
Previous Topic: query on update
Next Topic: numeric or value error
Goto Forum:
  


Current Time: Wed Dec 07 12:26:33 CST 2016

Total time taken to generate the page: 0.08462 seconds