title: Use Oracle SQL Developer to export BLOB's from table to a directory
body: <p>I've been tasked with writing a script to export BLOB's from an Oracle 11g database table, there are around 14,000. I've installed the most recent version of SQL Developer and can connect to the database.</p>
  
  <p>My background is in MSSQL so this has been quite the learning curve! And after lots of googling, sweat, tears and cups of tea I've managed to cobble together a couple of scripts to do the job by using a cursor to iterate through the images table and a script to export the BLOB's from that table to a directory.</p>
  
  <p>Basis of script 1 - Exporting BLOB's - <a href="https://stackoverflow.com/questions/6332032/how-can-i-extract-files-from-an-oracle-blob-field">How can I extract files from an Oracle BLOB field?</a> 
  Basis of script 2 - Using Cursors - <a href="http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.htmlhttp://" rel="nofollow noreferrer">http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html</a></p>
  
  <p>However I think it might be slow, my calculations show it will take around 11 hours to export the 14,000 images.</p>
  
  <p>The script loops through the table and then there is a secondary loop which goes through the BLOB's themselves, so I'm guessing this can be improved upon?</p>
  
  <p>I was running into an issue where utl_file process was locking files that were being exported however inserting "utl_file.fclose" after the BLOB export loop seemed to cure that for all but the last file being exported. </p>
  
  <p>Bear in mind I'm a complete newby when it comes to Oracle!</p>
  
  <p>The table I'm exporting from is caled "Images"
  The BLOB Field is called "Image"
  I am also exporting the columns "ID","ImageDate" and "ImageID" and appending ".jpg" as part of the filename.</p>
  
  <p>I've tried to write some comments in case anyone comes across this at a later date. Here's the script, any help or comments will be greatly appreciated!</p>
  
  <pre><code>SET serveroutput on                     --Set to output to query window for this session ONLY
  
  CREATE OR REPLACE DIRECTORY TEMPDIR     --Sets the export directory
  AS 'C:\TEMP\IMAGES\';
  
  
  DECLARE                                 --Declare a series of variables
  t_blob BLOB;
  t_len NUMBER;
  t_file_name VARCHAR2(1000);
  t_output UTL_FILE.FILE_TYPE;
  t_TotalSize NUMBER;
  t_position NUMBER := 1;
  t_chucklen NUMBER := 4096;
  t_chuck RAW(4096);
  t_remain NUMBER;
  
  
  BEGIN                                   --Begin the initial loop
  
    FOR var_a IN                          --Set the name of the cursor to be used
    (                                     --Define the cursor's properties
      SELECT id, imagedate, imageid
      FROM images
      WHERE imageid IS NOT NULL
      ORDER BY imageid
    )
    LOOP                                  --export loop
  
    BEGIN                                 --Set the variables to be blank or 0
    --t_blob BLOB;
    t_len := 0;
    t_file_name := '';
    --t_output UTL_FILE.FILE_TYPE;
    t_TotalSize := 0;
    t_position  := 1;
    t_chucklen  := 4096;
    --t_chuck := (4096);
    t_remain := 0; 
  
        -- Get length of blob
        SELECT DBMS_LOB.GETLENGTH (image), TO_CHAR (id)  || '-' || TO_CHAR (imagedate) || TO_CHAR (imageid) || '.jpg'
        INTO t_TotalSize, t_file_name
        FROM images
        WHERE imageid = var_a.imageid;
  
        t_remain := t_TotalSize;
        t_output := UTL_FILE.FOPEN ('TEMPDIR', t_file_name, 'wb', 32760);
  
        -- Get BLOB
        SELECT image
        INTO t_blob 
        FROM images
        WHERE imageid= var_a.imageid;
  
        -- Retrieving BLOB
        WHILE t_position &lt; t_TotalSize
          LOOP
          DBMS_LOB.READ (t_blob, t_chucklen, t_position, t_chuck);
          UTL_FILE.PUT_RAW (t_output, t_chuck);
          UTL_FILE.FFLUSH (t_output);
          t_position := t_position + t_chucklen;
          t_remain := t_remain - t_chucklen;
            IF t_remain &lt; 4096
              THEN
              t_chucklen := t_remain;
            END IF;
  
          END LOOP;
          UTL_FILE.FCLOSE (t_output);     --Close each file once it has been exported
  
        END;  
  
    END LOOP;
  
  END;
  
  /
  </code></pre>