Re: LOB question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 22 Jan 1999 14:11:18 GMT
Message-ID: <36a98503.167995945@inet16.us.oracle.com>

On Wed, 20 Jan 1999 11:50:39 -0600, Ryan <"rrichards[NO SPAM]"@benham.com> wrote:

>I am creating a database that will hold large graphic files. I am not
>familiar with using LOB's in oracle and was wondering if anyone who has
>done something similar to this could give me a few pointers of what to
>watch out for. Basically we have these files written to the server and
>need to store them in a database. I am a little confused on HOW these
>files are stored in tables.
>

Here is a quick example of how to get files on the server into the database and a procedure to get then back out and send it to a browser. If you need more information, review the application developers guide.

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

create sequence image_seq;

create or replace directory my_files as '/export/home/clbeck/public_html';

create or replace
procedure load_image( p_image_name varchar2 ) is

    l_blob blob;
    l_bfile bfile;
begin

    insert into demo values ( image_seq.nextval, empty_blob() )     returning theBlob into l_blob;

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

    dbms_lob.loadfromfile( l_blob, l_bfile,

                           dbms_lob.getlength( l_bfile ) );

    dbms_lob.fileclose( l_bfile );
end load_image;
/

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 image_get;

Hope this helps.

chris.

>Thanks for any help,
>Ryan

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jan 22 1999 - 08:11:18 CST