Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to insert a file into Oracle 8 Database
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);
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;
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
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