Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: substr on BLOBs

Re: substr on BLOBs

From: <johnwag_at_my-deja.com>
Date: Thu, 03 Feb 2000 15:27:30 GMT
Message-ID: <87c6ou$eak$1@nnrp1.deja.com>


In article <87c60s$dqa$1_at_nnrp1.deja.com>,   johnwag_at_my-deja.com wrote:
> 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.
  Sorry about that- stuck my foot in my mouth. Varchar2's can hold up to 32767 in PL/SQL such as you are using, my bad. Hmm.. Perhaps the problem is the type returned from substr is Varchar2 and hence when you are doing the update since you are assigning a varchar2 to a blob there is a problem? I am curious about any results and sorry again for the confusion.
  John

>
> 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.
>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US