??? 學習一下如何在Oracle中存儲圖片、影像等大文件。廢話不多說了，直接上代碼。
??? 注：后面那段是用于直接輸出html代碼在網頁展現的腳本。
?
SQL> create table demo
? 2? ( id??????? int primary key,
? 3??? theBlob???blob
? 4? )
? 5? /
?
Table created.
?
?
SQL> create or replace directory my_files as 'D:\TEST\Image';
?
Directory created.
?
?
SQL> declare
? 2????? l_blob??? blob;
? 3????? l_bfile?? bfile;
? 4? begin
? 5????? insert into demo values ( 1, empty_blob() )
? 6????? returning theBlob into l_blob;
? 7
? 8????? l_bfile := bfilename( 'MY_FILES', 'af73.jpg' );
? 9????? dbms_lob.fileopen( l_bfile );
?10
?11????? dbms_lob.loadfromfile( l_blob, l_bfile,
?12?????????????????????????????dbms_lob.getlength( l_bfile ) );
?13
?14????? dbms_lob.fileclose( l_bfile );
?15? end;
?16? /
?
PL/SQL procedure successfully completed.
?
--done
?
?
?
?
--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")
SQL> create or replace package image_get
? 2? as
? 3????? procedure gif( p_id in demo.id%type );
? 4? end;
? 5? /
?
Package created.
?
SQL>
SQL> create or replace package body image_get
? 2? as
? 3
? 4? procedure gif( p_id in demo.id%type )
? 5? is
? 6????? l_lob???blob;
? 7????? l_amt???number default 30;
? 8????? l_off?? number default 1;
? 9????? l_raw?? raw(4096);
?10? begin
?11????? select theBlob into l_lob
?12??????? from demo
?13???????where id = p_id;
?14???????-- make sure to change this for your type!
?15????? owa_util.mime_header( 'image/gif' );
?16
?17????????? begin
?18???????????? loop
?19??????????????? dbms_lob.read( l_lob, l_amt, l_off, l_raw );
?20
?21??????????????? -- it is vital to use htp.PRN to avoid
?22??????????????? -- spurious line feeds getting added to your
?23??????????????? -- document
?24??????????????? htp.prn( utl_raw.cast_to_varchar2( l_raw ) );
?25??????????????? l_off := l_off+l_amt;
?26??????????????? l_amt := 4096;
?27???????????? end loop;
?28????????? exception
?29???????????? when no_data_found then
?30??????????????? NULL;
?31????????? end;
?32? end;
?33
?34? end;
?35? /
?
Package body created.
?
?
?
?
??? 注：以上腳本涉及到的系統包有：DBMS_LOB | OWA_UTIL | UTL_RAW | HTP
?
??????? 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
?
??? 系統包使用方法可查看《PLSQL Packages and Types Reference》
?
?
?
-The End-