Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to insert a file into Oracle 8 Database

Re: How to insert a file into Oracle 8 Database

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 29 Jun 1998 14:18:54 GMT
Message-ID: <3598a1b6.3549804@192.86.155.100>


A copy of this was sent to badami_at_my-dejanews.com (if that email address didn't require changing) On Mon, 29 Jun 1998 12:51:02 GMT, you wrote:

>Hi,
>
> I have read that Oracle 8 architecture supports inserting and retrieving
>any kind of files into the database. I have to develop an application where I
>want to store a file which the user uploads to a server and then retrieve
>when the same user asks for it.
>
> So as any body tried to insert into a file into Oracle 8 database. If any
>code is present please mail it.
>
>Thanks
>Srinivas S Badami
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

Its straight to load the file:

drop table blobs;

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

create or replace directory MY_FILES as '/export/home/tkyte/public_html';

declare

    l_bfile bfile;
    l_blob blob;
begin

    insert into blobs values ( 'MyGif', empty_blob() )     return theBlob into l_blob;

    l_bfile := bfilename( 'MY_FILES', 'aria.gif' );     dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength(l_bfile) );     commit;
end;
/

dbms_lob.loadfromfile does all of the work. the directory object is a new Oracle8 object...

To get the object back over the web, using the pl/sql agent for example, you could code something like:

create or replace procedure get_image( p_name in varchar2 ) as

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

begin

    select image into l_lob

      from image
     where name = p_name;

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

for example (this works with binary stuff). The utl_raw package is typically installed only if you have the procedural gateway or replication installed. If utl_raw is not found, then cd $ORACLE_HOME/rdbms/admin and look for 2 files named "*raw*". Using SVRMGRL or equivalent, install the spec and body of utl_raw using either connect INTERNAL or SYS.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Mon Jun 29 1998 - 09:18:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US