How to Insert multimedia data (image, video) in 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.
===============================================
sql> create or replace directory temp as 'C:/dir_blob';
2)Grant read permission to the user who work with this directory.
===========================================================
sql> 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) insert data to table see here
http://halimdba.blogspot.com/2011/01/how-to-insert-multimedia-data-image.html
5) selecting table has multiple blob row
===========================================
SQL> select * from atmutl.image ;
DNAME SNAME FNAME IBLOB
TEMP1 This is Image Phoenix-documents.pdf (HUGEBLOB)
TEMP This is pdf1 Phoenix-documents1.pdf (HUGEBLOB)
TEMP2 This is pdf2 Phoenix-documents2.pdf (HUGEBLOB)
6) create a procedure (pass parameter directory name)
========================================================
CREATE OR REPLACE PROCEDURE atmutl.save_blob_jpg_to_disk_halim1 (
p_directory IN VARCHAR2
)
IS
v_blob BLOB;
v_start NUMBER := 1;
v_bytelen NUMBER := 2000;
v_len NUMBER;
v_raw RAW (2000);
v_x NUMBER;
v_output UTL_FILE.file_type;
v_file_name VARCHAR2 (200);
BEGIN
FOR i IN (SELECT DBMS_LOB.getlength (iblob) v_len, fname v_file_name,
iblob v_blob
FROM atmutl.image
WHERE ROWNUM <= 500)
LOOP
v_output :=
UTL_FILE.fopen (p_directory, i.v_file_name || '.PDF', 'wb', 32760);
v_x := i.v_len;
v_start := 1;
v_bytelen := 2000;
WHILE v_start < i.v_len AND v_bytelen > 0
LOOP
DBMS_LOB.READ (i.v_blob, v_bytelen, v_start, v_raw);
UTL_FILE.put_raw (v_output, v_raw);
UTL_FILE.fflush (v_output);
v_start := v_start + v_bytelen;
v_x := v_x - v_bytelen;
IF v_x < 2000
THEN
v_bytelen := v_x;
END IF;
END LOOP;
UTL_FILE.fclose (v_output);
END LOOP;
END save_blob_jpg_to_disk_halim1;
/
7) executing it ----dirtory name is uppercase
----------------------------------------------------
----------------------------------------------------
EXEC atmutl.save_blob_jpg_to_disk_halim1('TEMP');
