How to save blob data to disk from oracle table using UTL_FILE ?

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');