1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merging extracted blobs

Discussion in 'SQL PL/SQL' started by Jaffat1982, Jul 11, 2016.

  1. Jaffat1982

    Jaffat1982 Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Scottish Borders
    Hi there

    I hope this is the right section, apologies if it isn't.

    I am currently putting together a procedure to extract files stored in a BLOB column from our database. Using a combination of DBMS_LOB and UTL_FILE I have managed to successfully extract the documents.

    Unfortunately, our software provider decided that the best way to store multiple page documents (be they .doc, .tif, .pdf) was to have each page in a separate entry on the table with the BLOB column.
    The result of this is that I can extract them but am left with 10 separate documents for a 10 page tiff file for example. I have been able to assign my own filename to the output files, so have kept
    them constant for each file (apart from page number which I've appended to the end), which makes separating them easier, but I had hoped there would be a way of combining them into single files.
    It's probably best if I describe my problem by example.

    The original 5-page document, test.tif, was stored in the database in 5 separate BLOB rows.

    Using my current procedure I am left with:

    test-1.tif 5kb
    test-2.tif 5kb
    test-3.tif 5kb
    test-4.tif 5kb
    test-5.tif 5kb

    but what I want to try and produce is the original 5-page tiff file. Here is the procedure I use to extract the BLOB entry:

    Code (SQL):
    CREATE OR REPLACE
    PROCEDURE write_blob_to_file ( p_filename IN NUMBER,p_outfile IN VARCHAR2,p_path IN VARCHAR2) IS
      v_blob  BLOB;
      blob_length INTEGER;
      out_file  UTL_FILE.FILE_TYPE;
      v_buffer  RAW(32767);
      chunk_size  BINARY_INTEGER := 32767;
      blob_position INTEGER := 1;
      vErrMsg  VARCHAR2(2000);
      sqlstr VARCHAR2(1000);
      chkval NUMBER;
    BEGIN
    SELECT COUNT(*) INTO chkval FROM all_objects WHERE object_type = 'DIRECTORY' AND object_name = 'BLOBTEMP';
    IF chkval > 0 THEN
    sqlstr := 'drop directory BLOBTEMP';
      EXECUTE immediate sqlstr;
    END IF;
      -- Retrieve the BLOB for reading
      BEGIN
      SELECT DATA
      INTO v_blob
      FROM page_data
      WHERE ID = p_filename;
      EXCEPTION WHEN OTHERS THEN
      vErrMsg := 'No data found';
      END;

      -- Retrieve the SIZE of the BLOB
      blob_length := DBMS_LOB.GETLENGTH(v_blob);
      sqlstr := 'create directory BLOBTEMP as '''||p_path||'''';
      EXECUTE IMMEDIATE sqlstr;

      -- Open a handle to the output file
      out_file := UTL_FILE.FOPEN('BLOBTEMP',p_outfile, 'wb', chunk_size);

      -- Write the BLOB to the file in chunks
      WHILE blob_position <= blob_length LOOP
      IF ( ( blob_position + chunk_size - 1 ) > blob_length ) THEN
      chunk_size := blob_length - blob_position + 1;
      END IF;

      DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer );
      UTL_FILE.PUT_RAW ( out_file, v_buffer, TRUE);
      blob_position := blob_position + chunk_size;
      END LOOP;

    --Close file handle
    UTL_FILE.FCLOSE(out_file);
    sqlstr := 'drop directory BLOBTEMP';
      EXECUTE IMMEDIATE sqlstr;
    DBMS_OUTPUT.PUT_LINE('finished write');
      EXCEPTION
      WHEN OTHERS THEN
    --Close file handle to allow deletion
    UTL_FILE.FCLOSE(out_file);
    --Return error message and variables
      --DBMS_OUTPUT.PUT_LINE(p_filename||', '||p_outfile||', '||p_path||', '||vErrMsg);
    --Return error message and line
      DBMS_OUTPUT.PUT_LINE ( 'WBTF Error raised: '|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || ' - '||sqlerrm);
    END;
    I have tried:

    Using UTL_FILE.FOPEN in append 'a' mode, in append-byte 'ab' mode, inserting a page break in the file in both modes.

    Append or append-byte have the same result - the output file is the correct size (25kb) but only has a single page, a copy of test-1.tif.

    I have tried to insert the page break using both
    Code (SQL):
    UTL_FILE.PUT(out_file,chr(12))
    and
    Code (SQL):
    UTL_FILE.PUTF(out_file,'%s\n'||chr(12))
    I have also tried to extract them as individual pages then just loop through the resulting output files with IrFanView x64 and merge them but this is horrendously slow
    - we are talking 35 million+ pages in 7 million+ distinct documents all stored in 740,000 folders, so looping through them takes a loooong time; I would much rather combine them at the point of extraction.

    Am I missing something obvious?

    DB Version: Oracle 11.2.0.4
    OS: Windows Server 2008R2 x64
    Using: SQL Developer 3.2.20.09
    SQL Plus

    Any help would be greatly appreciated.

    John
     
    Last edited: Jul 11, 2016
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Correctly I understood you:
    it is necessary to give several BLOB columns in one file?
    BLOB are stored in separate ts?