Re: Oracle blob

You should be able to update an existing record with a new empty_blob().
Then if you retrieve the handle on the newly created blob, you can add
its new contents, if desired.

If you update an existing blob, they act kind of like a random access
file, where exiting contents, unless overwritten, will still be there.
I believe you can use dbms_lob.trim() to get rid of trailing content.

--Peter

kirschk wrote:
> Someone wrote once:> > May be this is a bug in oracle? Here is what I found out.> > 1. create a table with blob field and insert a empty blob record.> SQL>create table demo(id number, data blob);> SQL>insert into demo values(1,empty_blob());> > 2. Insert a file of size 1 MB into the blob field using JDBC API.> After inserting the file, try reading and verifying the contents.> > 3. Now insert another file of size 500kb ( file size should be less> than previouly inserted file) into the same record( id=1) blob field.> > When you read this blob, you'll see that blob size is returning a> size of 1MB. And the worst thing is, the blob stream in addition to> the new file, has old files junk characters in the end.> > So once a empty blob is used to store some data, it should not be> modified and used to store another set of data. To resolve this issue,> I delete the record and insert a new record in order to update the> blob field with the new data.> -----------------------------> No I have a very similar problem.> I have a oracle table with a blob field.> In the blob field are many records of X kb.> Now I need to get rid of the X kb,> since the blobs are no more useful, but the rest of the recordset must not> be deleted!> > If I use null as blob value, then I loose the reference,> to other records in other tables.> If I use empty blob, I dont gain free space, which is my goal. (see upper> message)> > How can I get rid of the blob data, without loosing the structure?> > I am talking about 160 Gbyte blob Data, which must be dropped somehow…and> also quick, since we are running out of space here,> so please answer as soon as possible,> > Many many thanks in advance,> > kostas> > Received on Thu Jul 22 2004 - 15:56:01 CDT