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