Friday 10 June 2016

PL/SQL Batch Blob Extraction for Oracle

I need to extract LOB segments from the DB before I delete them in an effort to free up datafile space. We do not have an archive system, so I've gone ahead to make a real document backup of the files before I do the delete and data file shrink.

To extract en masse - see the code below. For this to work, I need a table holding LOB segments (<TABLE>) with a filename column and a writable data directory <DB_DIRECTORY>.
I adjust 'rownum < 11' to set the criteria for the data I wish to extract. For my objective, I will be using a data range.

See the PL/SQL block below.

BEGIN
  /* Load the lobs */
  FOR r_blob IN (SELECT report_output, filename FROM <TABLE> WHERE created_date < To_Date('2015-06-01','YYYY-MM-DD')) 
  LOOP
    DECLARE
      v_file      UTL_FILE.FILE_TYPE;
      v_buffer    RAW(32767);
      v_amount    BINARY_INTEGER := 32767;
      v_pos       NUMBER := 1;
      v_blob_len  NUMBER;
     BEGIN
      /* Reset the blob cursor */
      v_pos := 1;
   
      /* Grab the lob size */
      v_blob_len := DBMS_LOB.getlength(r_blob.report_output);
      
      /* Open the file we want to hold the blob */
      v_file := UTL_FILE.fopen(<DB_DIRECTORY>, r_blob.filename,'WB', 32767);
      
      /* Write blocks of 32767 bytes at a time */
      WHILE v_pos < v_blob_len LOOP
     /* Read the contents of the lob from the db in 32767 chunk */
        DBMS_LOB.READ(r_blob.report_output, v_amount, v_pos, v_buffer);
     /* Write the buffer back out to file */
        UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
     /* Increment to load the next chunk */
        v_pos := v_pos + v_amount;
      END LOOP;
      
      /* Close the file */
      UTL_FILE.fclose(v_file);
 END;

  END LOOP;

END;

/

No comments:

Post a Comment