Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle blob

Re: Oracle blob

From: Peter Sylvester <>
Date: Thu, 22 Jul 2004 16:56:01 -0400
Message-ID: <cdp9l2$rf9$>

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.


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

Original text of this message