Tuesday, January 11, 2011

How to Insert multimedia data (image, video) in oracle table

How to Insert multimedia data(image, video) in oracle table.
-----------------------------------------------------------

Follow the bellows steps...

How to save blob data to disk from oracle table?

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?



1)Create Directory Where multimedia resides.

create or replace directory temp as 'C:/dir_blob';

2)Grant read permission to the user who work with this directory.

grant read on directory temp to test;

3)Create the Table which holds Lov object.

-- the storage table for the image file
CREATE TABLE image (
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 multimedia objects.

/* Formatted on 2011/01/11 18:10 (Formatter Plus v4.8.8) */
-- 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 image
(dname, sname, fname, iblob
)
VALUES (pdname, psname, pfname, EMPTY_BLOB ()
)
RETURNING iblob
INTO dst_file;

-- lock record
SELECT iblob
INTO dst_file
FROM image
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 image
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','image.png');
PL/SQL procedure successfully completed.


7)From Oracle Determine multimedia size..

1 declare
2 a blob;
3 begin
4 select iblob into a from image;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
66666

PL/SQL procedure successfully completed.


How to save blob data to disk from oracle table?





2 comments:

Anonymous said...

Hi Muhammad,
Your way is to create a path for each image(video,etc.). I wonder there is the way to insert multimedia data into table directly, not use the path. My teacher told me there is that way. If you know, please help me that way.
Sorry for my English.

Venkatesh Koneri said...

When I try to insert a null record it gives column not allowed here