Wednesday, June 20, 2012

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
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

5) selecting table has multiple blob row

SQL> select * from atmutl.image ;


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

   FOR i IN (SELECT DBMS_LOB.getlength (iblob) v_len, fname v_file_name,
                    iblob v_blob
               FROM atmutl.image
              WHERE ROWNUM <= 500)
      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
         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
            v_bytelen := v_x;
         END IF;
      END LOOP;

      UTL_FILE.fclose (v_output);
END save_blob_jpg_to_disk_halim1;

7) executing it   ----dirtory name is uppercase
EXEC atmutl.save_blob_jpg_to_disk_halim1('TEMP');

1 comment:

Anonymous said...

Your post saved me a lot of time. I had to dump about 700 pdfs out of the database into a file and your stored procedure did the trick perfectly. Thank you, random internet guy.