| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: substr on BLOBs
Hi there,
This is how I solved it:
PROCEDURE RemoveAccessHeader(p_dokument_id number) IS lobd BLOB; -- destination blob lobs BLOB; -- source blob src_offset INTEGER; amt INTEGER;
In article <1daj9sone9nufcqdohp1ofk3hla6dcbt5j_at_4ax.com>,
tkyte_at_us.oracle.com wrote:
> A copy of this was sent to johnwag_at_my-deja.com
> (if that email address didn't require changing)
> On Thu, 03 Feb 2000 15:27:30 GMT, you wrote:
>
> >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;
> >>
>
> That won't work. We can do it like this (shows loading a file,
dumping it,
> getting rid of the first N bytes and dumping it again)
>
> ops$tkyte_at_8.0> create table demo
> 2 ( id int primary key,
> 3 theBlob blob
> 4 )
> 5 /
>
> Table created.
>
> Elapsed: 00:00:00.16
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> create or replace directory my_files as '/tmp';
>
> Directory created.
>
> Elapsed: 00:00:00.02
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> declare
> 2 l_blob blob;
> 3 l_bfile bfile;
> 4 begin
> 5 insert into demo values ( 1, empty_blob() )
> 6 returning theBlob into l_blob;
> 7
> 7 l_bfile := bfilename( 'MY_FILES', 'binary.dat' );
> 8 dbms_lob.fileopen( l_bfile );
> 9
> 9 dbms_lob.loadfromfile( l_blob, l_bfile,
> 10
dbms_lob.getlength(
> l_bfile ) );
> 11
> 11 dbms_lob.fileclose( l_bfile );
> 12 end;
> 13 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.03
> ops$tkyte_at_8.0> column dump format a50
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> select dbms_lob.getlength(theBlob) len,
> 2 dump( utl_raw.cast_to_varchar2(dbms_lob.substr
(theBlob,10,1)) )
> dump
> 3 from demo
> 4 /
>
> LEN DUMP
> ---------- --------------------------------------------------
> 769 Typ=1 Len=10: 71,73,70,56,55,97,26,1,44,0
>
> Elapsed: 00:00:00.01
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> declare
> 2 l_blob blob;
> 3 l_amt number default 5;
> 4 begin
> 5
> 5 select theBlob into l_blob from demo where id = 1 FOR
UPDATE;
> 6
> 6 dbms_lob.copy( l_blob, l_blob, dbms_lob.getlength
(l_blob)-l_amt, 1,
> l_amt+1 );
> 7 dbms_lob.trim( l_blob, dbms_lob.getlength(l_blob)-
l_amt );
> 8 end;
> 9 /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:00.01
> ops$tkyte_at_8.0>
> ops$tkyte_at_8.0> select dbms_lob.getlength(theBlob) len,
> 2 dump( utl_raw.cast_to_varchar2(dbms_lob.substr
(theBlob,10,1)) )
> dump
> 3 from demo
> 4 /
>
> LEN DUMP
> ---------- --------------------------------------------------
> 764 Typ=1 Len=10: 97,26,1,44,0,179,0,0,192,192
>
> Elapsed: 00:00:00.02
>
> the dump shows the move was successful, we can see that 97,26,....
(the 6'th
> byte on) is now the first byte and the lengh is corrected as well.
>
> utl_raw may not be installed on your system if you want to test this
yourself.
> cd $ORACLE_HOME/rdbms/admin and do "ls *raw*". Install the two files
you find
> when connected as SYS OR INTERNAL (only these users) using svrmgrl.
>
> >> 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.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Feb 08 2000 - 08:55:56 CST
 |
 |