Home » SQL & PL/SQL » SQL & PL/SQL » inserting large blob images
inserting large blob images [message #241314] Tue, 29 May 2007 03:51
Messages: 2
Registered: May 2007
Junior Member
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';

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;


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

if(length >= 2000) then
else if(length = 0) then
cbuff := '1010'; --testing
end if;
end if;
insert into tableB@linkr (id,image)

end loop;
drop database link linkr
Previous Topic: Make SQl purposefully RUN SLOW
Next Topic: Autonomous Transactions
Goto Forum:

Current Time: Tue Aug 22 08:16:08 CDT 2017

Total time taken to generate the page: 0.02053 seconds