ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Insert BLOB

Oracle Insert BLOB

原创 Linux操作系统 作者:sudn 时间:2012-02-24 10:55:34 0 删除 编辑

来源于:

http://arjudba.blogspot.com/2008/06/how-to-insert-blob-dataimage-video-into.html

How to Insert Blob data(image, video) into oracle 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16860121/viewspace-717065/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-12-20

  • 博文量
    73
  • 访问量
    146224