Re: substr on BLOBs

In article <85l04b$6hi$1_at_nnrp1.deja.com>,   knudaage_at_my-deja.com wrote:
> Hi,
>
> I have some problems updateting blob's unsing dbms_lob.substr. I have
> stored som Word documents (created in Access) in a blob field on
Oracle
> 8.1.5EE. I would like to remove a header like this:
> declare
> offset number = 45;
> begin
> update objekt set objekt=
> dbms_lob.substr(objekt, DBMS_LOB.GETLENGTH(objekt) - offset, offset+1)
> where dokument_id=3148;
> end;

Your problem is that dmbs_lob.substr returns a varchar2. Varchar2's are of max length 4,000 bytes. Thus since your getlength returns what 13,000 or so bytes you get the error. I am also interested in seeing how to effectively manipulate CLOB's.

John
>
> but get this error:
> ORA-06502: PL/SQL: numeric or value error: raw variable length too
long
> ORA-06512: at line 1
>
> My table look like this:
> SQL> desc objekt
> Navn NULL? Type
> ----------------------------------------- -------- ------------
> ID NOT NULL NUMBER(12)
> DOKUMENT_ID NOT NULL NUMBER(12)
> BRUGER_ID NOT NULL NUMBER(12)
> VERSION NOT NULL VARCHAR2(30)
> OBJEKT NOT NULL BLOB
> DATO NOT NULL DATE
> LAAST_AF NUMBER(12)
> AKTUEL NOT NULL NUMBER(1)
>
> and it is a small document:
> SQL> select DBMS_LOB.GETLENGTH(objekt) from objekt where
> dokument_id=3148;
>
> DBMS_LOB.GETLENGTH(OBJEKT)
> --------------------------
> 18752
>
> ...hope to get some help from one of you experts!
>
> regards
> knudaage_at_hotmail.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 03 2000 - 09:14:39 CST