I have a script that extracts documents en masse out of an Oracle BLOB table. This is necessary for a huge rewrite and database conversion from Oracle to SQL where the files are going to be stored in an SQL file table. Since the documents have to sit on the file system, I have to get them out and write them out as files. It works great for MOST of my documents. After lots of banging my head on my desk, I finally figured out it's because there is some logic on the front end system that compresses some of the documents--although I really can't figure out the criteria for it doing that. At any rate, I've searched and searched and can't find any sort of Boolean check to see if they are compressed inside the Oracle BLOB table or not, before I extract them. If I try to decompress them ALL as I'm extracting them, I get an error on the ones that weren't compressed. So now I'm thinking I can run them all with the decompression, and then catch the exception and handle the others by exporting without the decompression. I just can't get my syntax correct in my script. This is a new challenge for me, and I don't have a ton of experience writing scripts of this sort, so please forgive my ignorance. Here's the error I get when I try to decompress all of them, so this is what I'm trying to catch:
ORA-29294: A data error occurred during compression or uncompression.
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 56
ORA-06512: at "SYS.UTL_SYS_COMPRESS", line 226
ORA-06512: at "SYS.UTL_COMPRESS", line 89
ORA-06512: at line 21
Here is the script:
DECLARE
CURSOR C1 IS Select FILE_ID || '---' || substr(DOCUMENTLOCATION,1,instr (DOCUMENTLOCATION,'.')-1)||'.doc' as FILE_NAME, FILE_BLOB, FILE_ID
From DOCUMENTS d inner join CASEJOURNAL c on d.FILE_ID = c.JOURNALENTRYID where (JOURNAL_ENTRY_TYPE = 117 or JOURNAL_ENTRY_TYPE = 3) AND c.DOCUMENTLOCATION Is Not Null AND d.MIME_TYPE = 'application/msword' AND FILE_ID between 1 and 10000;
v_blob_uncomp BLOB;
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;
filename varchar2(255);
BEGIN
--Select BLOB file into variables
FOR I in C1
LOOP
filename := i.FILE_NAME;
v_blob_uncomp := UTL_COMPRESS.LZ_UNCOMPRESS(i.FILE_BLOB);
v_blob := i.FILE_BLOB;
-- Define the output directory
out_file := UTL_FILE.FOPEN('fileloc',filename,'wb',chunk_size);
--Get length of BLOB file and save to variable.
blob_length := DBMS_LOB.getlength(v_blob);
-- Write the data to the file
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_uncomp, chunk_size, blob_position, v_buffer);
UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
blob_position := blob_position + chunk_size;
END LOOP;
UTL_FILE.FCLOSE(out_file);
END LOOP;
END;
I know that the script works when I don't decompress any blobs, however the ones that were compressed don't open. It also works when I do the decompress on certain files that I know were compressed. I'm just trying to get this to work within my loop for ALL files somehow. TIA!
When you want to catch a particular Oracle error code in PL/SQL, you basically have two options:
A) catch all exceptions; in the handler, test whether the error message matches the one you are looking for; if so, handle it; if not, re-raise it. This would look something like:
BEGIN
v_blob := := UTL_COMPRESS.LZ_UNCOMPRESS(i.FILE_BLOB);
EXCEPTION
WHEN OTHERS THEN
IF sqlerrm LIKE 'ORA-29294%' THEN
v_blob := i.FILE_BLOB;
ELSE
RAISE;
END IF;
END;
B) declare an exception variable and map it to the specific error code you care about, then catch only that exception. This would look something like this:
DECLARE
compression_error EXCEPTION;
pragma exception_init ( compression_error, -29294 );
BEGIN
v_blob := UTL_COMPRESS.LZ_UNCOMPRESS(i.FILE_BLOB);
EXCEPTION
WHEN compression_error THEN
v_blob := i.FILE_BLOB;
END;
Either way, I'd suggest wrapping this in a function.
I also note that your code shown doesn't reset blob_position
to 1 when it starts processing a new BLOB.