在Oracle中存储图片、影像等大文件

http://www.blogjava.net/wxqxs/archive/2009/03/16/263800.html

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