Merging BLOB [message #653651] |
Wed, 13 July 2016 04:23 |
|
Jaffat1982
Messages: 4 Registered: July 2016
|
Junior Member |
|
|
Hi there
I'm currently writing 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:
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
--If directory object exists already, drop it.
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);
--Create directory object
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);
--Drop directory object
sqlstr := 'drop directory BLOBTEMP';
EXECUTE IMMEDIATE sqlstr;
--Output finished message for testing
--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
UTL_FILE.PUT(out_file,chr(12))
and
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
|
|
|
Re: Merging BLOB [message #653657 is a reply to message #653651] |
Wed, 13 July 2016 08:17 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'd love to know how exactly they're being split in the first place.
Binary files aren't really designed to be treated that way.
|
|
|
|
|
|
Re: Merging BLOB [message #653680 is a reply to message #653679] |
Thu, 14 July 2016 08:33 |
ThomasG
Messages: 3211 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
The problem is that you can nut just append the binary data, but you have to re-create a correct multi-page TIFF from single-Page TIFFs.
Which is a more complex process than just binary joining them with ASCII page breaks in between. You could have a look at ImageMagick, and see if you can somehow add that to the export process.
|
|
|
|