eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 在Oracle参加OLAP培训 | Blog首页 | 使用PL/SQL从数据库中读取BLOB对象 »

使用存储过程(PL/SQL)向数据库中存储BLOB对象

以下存储过程用于向数据库加载BLOB对象
1.创建directory并授权
关于Directory可以参考: Using Create directory & UTL_FILE in Oracle
C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Tue Apr 26 07:11:51 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> create user eygle identified by eygle default tablespace users;

User created.

SQL> grant connect ,resource,dba to eygle;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';

Directory created.

SQL> grant read on directory BLOBDIR to eygle;

Grant succeeded.

SQL>

2.创建测试表
SQL> connect eygle/eygle
Connected.
SQL> CREATE TABLE eygle_blob (
  2  fid    number,
  3  fname       varchar2(50),
  4  fdesc  varchar2(200),
  5  fpic        BLOB)
  6  /

Table created.

SQL>
SQL> create sequence S_EYGLE_SEQ
  2  start with 1
  3  increment by 1
  4  /

Sequence created.

SQL>

3.创建存储过程
SQL> CREATE OR REPLACE PROCEDURE eygle_load_blob (pfname VARCHAR2,pdesc varchar2)
  2  IS
  3  src_file BFILE;
  4  dst_file BLOB;
  5  lgh_file BINARY_INTEGER;
  6  BEGIN
  7     src_file := bfilename('BLOBDIR', pfname);
  8
  9     INSERT INTO eygle_blob (fid,fname,fdesc,fpic)
 10     VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
 11     RETURNING fpic INTO dst_file;
 12
 13     SELECT fpic INTO dst_file
 14     FROM eygle_blob  WHERE fname = pfname FOR UPDATE;
 15
 16     dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
 17     lgh_file := dbms_lob.getlength(src_file);
 18     dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
 19
 20    UPDATE eygle_blob  SET fpic = dst_file
 21    WHERE fname = pfname;
 22
 23    dbms_lob.fileclose(src_file);
 24    commit;
 25  END eygle_load_blob;
 26  /

Procedure created.

SQL> col segment_name for a30
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                   .0625
EYGLE_BLOB                     TABLE                        .0625


4.加载Blob对象
SQL> exec eygle_load_blob('ShaoLin.jpg','少林寺-康熙手书');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                       4
EYGLE_BLOB                     TABLE                        .0625

SQL> exec eygle_load_blob('DaoYing.jpg','倒映');

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                       7
EYGLE_BLOB                     TABLE                        .0625


SQL> col fname for a20
SQL> col fdesc for a30
SQL> select fid,fname,fdesc,dbms_lob.getlength(fpic) siz from eygle_blob;

       FID FNAME                FDESC                                 SIZ
---------- -------------------- ------------------------------ ----------
         1 ShaoLin.jpg          少林寺-康熙手书                   1768198
         2 DaoYing.jpg          倒映                              2131553


D:\oradata\Pic>ls -l
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg

通过以上方式,我们可以很容易的把大对象存储到数据库中。

历史上的今天...
    >> 2009-04-28文章:
    >> 2008-04-28文章:
           我的假期 我的关注
    >> 2006-04-28文章:

无觅

By eygle on 2005-04-28 10:12 | Comments (11) | SQL.PLSQL | 274 |

11 Comments

eygle :
能再进一步,把本地xml文件,导入到数据库中(ver>9i).

RETURNING fpic INTO dst_file;

SELECT fpic INTO dst_file
FROM eygle_blob WHERE fname = pfname FOR UPDATE;

是否有些重复?如果没有,为什么?对于Blob类型的处理必须这样吗?

如何使用存储过程(入参是blob类型)java客户端调用存储过程将客户端的文件保存到数据库中呢?

http://www.oracle.com/technology/global/cn/sample_code/tech/java/codesnippet/jdbc/lob/LobToSP.html

这是个关于clob的例子

老大,怎么更新这个blob字段呢?

单表上传的例子,我已经搞定了。
能否把把表和字段也做成参数传递进过程?
我这里有多个表具有BLOB字段,想通过一个过程来实现上传文件。不知道行不行!

D:\oradata\Pic>ls -l
ls不是unix下的命令吗?D:\oradata\Pic>看上去像windows啊?

Windows上也是可以安装类Unix软件包,来获得这些命令增强。

大侠 你好
我按你的步骤怎么出现错误呢:
--建立目录:
create or replace directory BLOB_DIR as 'F:\oradata';

--创建存储过程
CREATE OR REPLACE PROCEDURE microle_load_blob (pfname VARCHAR2,pdesc varchar2)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN

src_file := bfilename('BLOB_DIR', pfname);

INSERT INTO microle_blob(fid,fname,fdesc,fpic)
VALUES (S_MICROLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
RETURNING fpic INTO dst_file;

SELECT fpic INTO dst_file
FROM microle_blob
WHERE fname = pfname
FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

UPDATE microle_blob SET fpic = dst_file
WHERE fname = pfname;

dbms_lob.fileclose(src_file);
commit;
END microle_load_blob;

--调用
exec microle_load_blob('P5140116.JPG','CS');

--出现错误如下
ORA-22288: 文件或 LOB 操作FILEOPEN失败
设备未就绪。

这个方法应该适用于BLOB,CLOB这两种类型。
对于Long,Long Raw,Raw可能需要考虑采用别的途径来进行解决 。

这个方法应该适用于BLOB,CLOB这两种类型。
对于Long,Long Raw,Raw可能需要考虑采用别的途径来进行解决 。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com