学习一下如何在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-