Re: HELP ORACLE8 BLOB & PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/26
Message-ID: <34a611a8.14907936@inet16>#1/1

On Wed, 24 Dec 1997 12:34:27 -0700, Sateesh Burgadda <sburgadd_at_evolving.com> wrote:

>Hi!
>
>I am trying to read and write data into BLOB field
>using DBMS_LOB.READ & WRITE Procedures.
>
>I could able to write data as per the parameters but when
>I want to read the same data, it is giving error ora-1403(no data
>found).
>
>For writing into BLOB field, I have a table one ID field and BLOB field
>
>First :
>
>insert into my_table values(id, empty_blob());
>
>select blob_field into blob_locater for update;
>
>DBMS_LOB.WRITE(blob_locater, amt, offset, buffer);
>
>amt = 10; offset = 1; buffer (of raw) =
>'0101010101010101010100101001010101';
>
>Successfully inserted.
>
>When I want to read
>
>select blob_field into blob_locater;
>
>DBMS_LOB.READ(blob,amt, offset, buffer);
>
>Gave error saying that ORa-1403.
>
>PL help me in this - We need to implement BLOBs in our project
>in place of RAW fields of oracle7.3.
>
>I would appreciate if u send a copy of ur reply to my email
>sburgadd_at_evolving.com
>
>Thanks IN Advance
>
>Sateesh

It sounds like you are trying to piecewise read the lob and are hitting the EOF condition..... dbms_lob.read raises NO_DATA_FOUND when you index past the end of the lob.

For example, the following works OK:

create table blobs
( id varchar2(255),
  the_Blob blob
)
/

declare

    l_lob   blob;
    l_raw   raw(200);
    l_amt   number default 200;
    l_pos   number default 1;

begin

    insert into blobs values ( 'x', empty_blob() )     return the_Blob into l_lob;

    for i in 1 .. 100 loop

        l_raw := l_raw || hextoraw('01');     end loop;

    l_amt := length(l_raw)/2;
    dbms_lob.write( l_lob, l_amt, l_pos, l_raw );     commit;

    dbms_output.put_line( 'Wrote "x", length = ' || l_amt ||

                          ' blob = ' || substr(l_raw,1,30) || '...' );

end;
/

declare

    l_lob   blob;
    l_amt   number default 30;
    l_off   number default 1;
    l_raw   raw(30);

begin

    select the_blob into l_lob

      from blobs
     where id = 'x';

    begin
        loop
            dbms_lob.read( l_lob, l_amt, l_off, l_raw );
            dbms_output.put_line( 'read ' || l_amt || ' bytes from '|| l_off);
            l_off := l_off+l_amt;
            l_amt := 30;
        end loop;
    exception
        when no_data_found then
            dbms_output.put_line( 'Caught the EOF condition' );

    end;
end;
/

Note that in the read loop, you must catch the exception "when no_data_found" if you don't explicitly compute the number of bytes you want to read...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 26 1997 - 00:00:00 CST