在Oracle中存储Image

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