Re: loading jpg to BLOB and retrieving

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 30 Dec 1998 15:53:52 GMT
Message-ID: <368e4ba3.10141592@192.86.155.100>

A copy of this was sent to "bla" <dwy001_at_erols.com> (if that email address didn't require changing) On Tue, 29 Dec 1998 15:07:12 -0800, you wrote:

>Does someone have instructions on how to do this for NT 4.0 with Oracle
>8.0.4. Jpg’s are up to 500-600k. Am trying to update records where have one
>BLOB field that I would like to place a diagram in for retrieval. Have
>concatenated primary key (2 fields). Am not using Forms or Reports, looking
>for other ideas for retrieval too.
>

Here is an example. Basically, you will have a table (mine is demo) that you want to load a file into. You need to create a directory object (new in Oracle8) that maps to the OS file system you will load from. Then, you can use dbms_lob.loadfromfile to read the file into the blob.

NOTE: a directory, such as my_files below, is an Oracle object like a table. Like any other identifier, my_files when specified unquoted will always be rolled to upper case. its really MY_FILES -- hence, when we pass the name of the directory object to the bfilename() function, it is important to pass the UPPERCASE name -- otherwise, it will not find the directory.

drop table demo;

create table demo
( id int primary key,
  theBlob blob
)
/

create or replace directory my_files as 'c:\temp';

declare

    l_blob blob;
    l_bfile bfile;
begin

    insert into demo values ( 1, empty_blob() )     returning theBlob into l_blob;

    l_bfile := bfilename( 'MY_FILES', 'aria.gif' );     dbms_lob.fileopen( l_bfile );

    dbms_lob.loadfromfile( l_blob, l_bfile,

                           dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );
end;
/

Here is an example that shows how to retrieve a blob using pl/sql (there are other ways using oci and pro*c and other languages). This particular routine was designed to be called by the pl/sql cartridge that comes with OAS to return the blob to a web browser:

create or replace package image_get
as

    procedure gif( p_id in demo.id%type );

end;
/

create or replace package body image_get as

procedure gif( p_id in demo.id%type )
is

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

begin

    select theBlob into l_lob

      from demo
     where id = p_id;

    owa_util.mime_header( 'image/gif' );

    begin

        loop
            dbms_lob.read( l_lob, l_amt, l_off, l_raw );
            htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
            l_off := l_off+l_amt;
            l_amt := 4096;
        end loop;
    exception
        when no_data_found then
            NULL;

    end;
end;

end;  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
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 Wed Dec 30 1998 - 09:53:52 CST