Click here to Skip to main content
15,880,132 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I Have an oracle database with a table have a blob field have images store in it

how I can export these images stored in blob field back to a file in a folder on the computers harddisk

I have oracle 11g and pl/sql developr 8.0 and visual basic 2013
Posted
Updated 7-Jul-17 17:43pm

You could use UTL_FILE utility for file operation.

Loop through the table containing the blob field and create file as you wish using the UTL_FILE utility.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#BABDEJDH[^]
 
Share this answer
 
could you please give me practical example how I can do my job using utl_file
 
Share this answer
 
THANK YOU I'VE MANAGED TO DO IT AT LAST

HERE IS MY PROCEDURE PLEASE ADVISE IF THERE IS A BETTER WAY

SQL
CREATE OR REPLACE PROCEDURE SAVE_FINGERS (
   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 (FINGERPRINT) v_len, CARD_NUMBER v_file_name,
                    FINGERPRINT v_blob
               FROM RECEPT_CARDS)
 
   LOOP
      v_output := UTL_FILE.fopen (p_directory, i.v_file_name || '.JPG', '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_FINGERS;
 
Share this answer
 
Comments
Sanju TV 28-Jul-19 4:05am    
In which drive the file will be saved? in Oracle server or in Client?
samerselo 6-Aug-19 4:00am    
I don't remember Sorry

The code is old and I've used it only once
I'm Using now SQl Server
Hi,
This works for me like a champ

FUNCTION F_WRITE_XML_TO_FILE
(
p_filename varchar2,
p_xml xmlType,
p_export_dir varchar2,
p_encoding_nr IN number default 871
) RETURN boolean AS
v_blob BLOB;
v_length NUMBER;
v_offset NUMBER:=1;
v_chunk NUMBER:=32767;
v_output UTL_FILE.file_type;
BEGIN
v_blob:= p_xml.getblobval (p_encoding_nr);
v_length:= DBMS_LOB.getlength (v_blob);

v_output := UTL_FILE.fopen (p_export_dir, p_filename, 'wb', 32767);
LOOP
EXIT WHEN v_offset > v_length;
UTL_FILE.put_raw (v_output, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset), TRUE);
v_offset := v_offset + v_chunk;
END LOOP;
UTL_FILE.fclose (v_output);
return true;
END;
 
Share this answer
 
Comments
Patrice T 8-Jul-17 1:36am    
Question was answered 2 years ago !

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900