Click here to Skip to main content
15,867,957 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 my computers harddisk oracle installed on server.


What I have tried:

CREATE OR REPLACE PROCEDURE SAVE_ATTACHMENT (
   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 (FILE_ATTACHED) v_len, '1.jpeg' as v_file_name,
                    FILE_ATTACHED v_blob
               FROM GATEPASS_ATTACHMENT WHERE ROWNUM=1)
 
   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_ATTACHMENT;


tried the above code but getting error

Error starting at line : 1 in command -
EXECUTE SAVE_ATTACHMENT('/IMAGES')
Error report -
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at "GPCS.SAVE_ATTACHMENT", line 20
ORA-06512: at "GPCS.SAVE_ATTACHMENT", line 20
ORA-06512: at line 1
29280. 00000 -  "invalid directory path"
*Cause:    A corresponding directory object does not exist.
*Action:   Correct the directory object parameter, or create a corresponding
           directory object with the CREATE DIRECTORY command.


Where the file will be downloaded? in my local PC ? or in Oracle server ?
exactly in which Drive the file will be downloaded
Posted
Updated 27-Jul-19 23:37pm
v2
Comments
Richard MacCutchan 28-Jul-19 4:12am    
The error message is perfectly clear, you are trying to write into a directory that does not exist.
Sanju TV 28-Jul-19 4:13am    
where i has to create the directory in which path in server? or in my local PC?
Richard MacCutchan 28-Jul-19 4:25am    
I have no idea; where do you want to save it?

1 solution

SOLVED: it was a permission issue , SOlved the file is saved in oracle server
 
Share this answer
 

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