saving gif pdf etc in oracle db
Hi Friends,
I got this excerpt from ASK TOM, about saving and retreiving objects
to Oracle DB. Does this program be able to save movies too? or a
mp3 music?:)
Are there other codes/programs shorter or better than these:D
I am using 10gXe DB, I believe his code is generated from lower
Db versions like 8i coz he is mentioning svrmgrl:rolleyes:
and I think 10g has more advanced features;)
Thanks a lot
==========================================================
Saving and Retrieving .gif .pdf etc. etc
Here is an example that loads a GIF into the datbase and displays it.
You would of course change the mime type and the name of the procedure
from .gif to .pdf. Then you will be able to use HTML like:
Click Me
to retrieve "document 55" for example.
The following code just created a demo table and loads an image into it -- you already
have the pdfs loaded so its not 100% relevant to your problem
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
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;
/
Now here is the package that can retrieve the pdf (or anything for that matter. Just
keep adding procedures that are named after the file type like .doc, .pdf, .xls and so
on. Some browsers really want the extension in the URL to be "correct")
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;
-- make sure to change this for your type!
owa_util.mime_header( 'image/gif' );
begin
loop
dbms_lob.read( l_lob, l_amt, l_off, l_raw );
-- it is vital to use htp.PRN to avoid
-- spurious line feeds getting added to your
-- document
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;
/
UTL_RAW might not be installed on your database. It is part of replication. If you
do not have it installed, simply:
o cd $ORACLE_HOME/rdbms/admin
o find the two files with "raw" in their name (eg: ls *raw*)
o using svrmgrl connect as INTERNAL OR SYS -- only these users, no one else can
successfully install UTL_RAW
o run the .sql and then the .plb file