Search This Blog

Saturday, November 20, 2010

How to Insert Blob data(image, video) into oracle BLOB size

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
PL/SQL procedure successfully completed.

No comments:

Post a Comment