来源于:
http://arjudba.blogspot.com/2008/06/how-to-insert-blob-dataimage-video-into.html
How to Insert Blobdata(image, video) intooracle BLOB size
In this post it is shown how I can insert Blob data link image video into oracle database and also how I can determine the size of the BLOB data from oracle.
1)Create Directory Where BLOB resides.
create or replace directory temp as '/oradata2';
2)Grant read permission to the user who work with this directory.
grant read on directory temp to arju;
3)Create the Table which holds lob object.
-- the storage table for the image file
CREATE TABLE pdm (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file
4)Create the procedure that insert BLOB objects.
-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);
-- insert a NULL record to lock
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE pdm
SET iblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- close file
dbms_lob.fileclose(src_file);
END load_file;
/
5)Execute the Procedure.
SQL> exec load_file('TEMP','This is Image','tritha7.png');
PL/SQL procedure successfully completed.
6) From OS see the BLOB size.
SQL> !ls -l /oradata2/tritha7.png
-rwxr-xr-x 1 oracle oinstall 21150 Jun 9 01:55 /oradata2/tritha7.png
7)From Oracle Determine Blob size.
1 declare
2 a blob;
3 begin
4 select iblob into a from pdm;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
21150
1)Create Directory Where BLOB resides.
create or replace directory temp as '/oradata2';
2)Grant read permission to the user who work with this directory.
grant read on directory temp to arju;
3)Create the Table which holds lob object.
-- the storage table for the image file
CREATE TABLE pdm (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file
4)Create the procedure that insert BLOB objects.
-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('TEMP', pfname);
-- insert a NULL record to lock
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB())
RETURNING iblob INTO dst_file;
-- lock record
SELECT iblob
INTO dst_file
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname
FOR UPDATE;
-- open the file
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
-- determine length
lgh_file := dbms_lob.getlength(src_file);
-- read the file
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
-- update the blob field
UPDATE pdm
SET iblob = dst_file
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- close file
dbms_lob.fileclose(src_file);
END load_file;
/
5)Execute the Procedure.
SQL> exec load_file('TEMP','This is Image','tritha7.png');
PL/SQL procedure successfully completed.
6) From OS see the BLOB size.
SQL> !ls -l /oradata2/tritha7.png
-rwxr-xr-x 1 oracle oinstall 21150 Jun 9 01:55 /oradata2/tritha7.png
7)From Oracle Determine Blob size.
1 declare
2 a blob;
3 begin
4 select iblob into a from pdm;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL> set serverout on
SQL> /
21150
最近遇到好多需要存储Oracle BLOB 需求。 众所周知,BLOB 是用来存储图片、PDF、等大数据对象的。由于公司的需要我们所有的数据库操作都要使用存储过程来操作。所以首先先贴上一个BLOB存储的存储过程供大家参考:
- create or replace
- procedure NDSSP_backup_fund (fund_id_in in varchar2 ,selector_in in varchar2,
- time_in in timestamp,blob_in in BLOB)
- is
- key number;
- blob_tmp blob;
- begin
- delete from fund_backup bf where bf.selector = selector_in and bf.fund_id = fund_id_in;
- select fund_backup_seq.nextval into key from dual;
- insert into fund_backup values(key,selector_in,fund_id_in,empty_blob(),time_in);
- select content into blob_tmp from fund_backup where id = key for update;
- dbms_lob.copy(blob_tmp, blob_in, dbms_lob.getLength(blob_in));
- end NDSSP_backup_fund;
大家只要关注BLOB存储的部分就好了,其他的部分都可以忽略。正如大家所看到的,我们想要调用这个存储过程,必须要传递一个BLOB 对象。