Re: substr on BLOBs

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 03 Feb 2000 11:18:08 -0500
Message-ID: <1daj9sone9nufcqdohp1ofk3hla6dcbt5j@4ax.com>

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 Received on Thu Feb 03 2000 - 10:18:08 CST