« 在Oracle参加OLAP培训 | Blog首页 | 使用PL/SQL从数据库中读取BLOB对象 »
使用存储过程(PL/SQL)向数据库中存储BLOB对象
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2005/04/eoaiplsqlieoeao.html
以下存储过程用于向数据库加载BLOB对象链接:https://www.eygle.com/archives/2005/04/eoaiplsqlieoeao.html
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.创建存储过程
4.加载Blob对象
通过以上方式,我们可以很容易的把大对象存储到数据库中。
历史上的今天...
>> 2020-04-28文章:
>> 2009-04-28文章:
>> 2008-04-28文章:
>> 2006-04-28文章:
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
通过以上方式,我们可以很容易的把大对象存储到数据库中。
历史上的今天...
>> 2020-04-28文章:
>> 2009-04-28文章:
>> 2008-04-28文章:
>> 2006-04-28文章:
By eygle on 2005-04-28 10:12 | Comments (11) | SQL.PLSQL | 274 |
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可能需要考虑采用别的途径来进行解决 。