Extracting BLOB files from Oracle table with UTL_FILE en masse, some compressed some not

Question

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!

score 3 · Accepted Answer · answered Apr 06 '15 at 19:28

3

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.

answered Apr 06 '15 at 19:28

Dave Costa

47,262
8
56
72

OK, this is where I was heading, but I am not sure where to put my EXCEPTION handling statement (EXCEPTION WHEN...). I tried putting it right before my last END LOOP; statement but I get an error: ORA-06550: line 46, column 5: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open roll – kharvey Apr 07 '15 at 13:14
Then I tried putting it after my last END LOOP and before END, and I don't get a syntax error but when I run it it says "i.FILE_BLOB" must be declared--which makes sense because now it's outside the cursor and it can't "see it". Would you mind helping me insert it in my actual script? (I did add the reset blob_position to position 1 like you said). – kharvey Apr 07 '15 at 13:23
An EXCEPTION handler must be at the end of its enclosing BEGIN/END block; that is why your first attempt gave you a syntax error. However, you can nest BEGIN/END blocks wherever you like in order to declare localized exception handlers. In this case, I'd replace the lines where you assign to `v_blob` and `v_blob_uncomp` with the entire block of code in my answer (or with a call to a function containing that code). And I'd remove the `v_blob_uncomp` variable entirely. – Dave Costa Apr 07 '15 at 13:47
1

Worked PERFECTLY--Now I'm a hero, thanks to you Dave!! And I will give credit to the "nice man" on stackoverflow. MUCH appreciation! – kharvey Apr 07 '15 at 15:10

score 1 · Answer 2 · answered Apr 06 '15 at 19:48

1

Instead of "try and fail" approach you may consider to use magic numbers: Read few bytes from the beginning of the file, if it starts, lets say with 504B (PK), then there is a fat chance that it would be zip archive.

answered Apr 06 '15 at 19:48

vav

4,584
2
19
39

A zip archive as a whole is not a compressed stream. It does (usually) contain compressed streams, of course, which may be decompressed using `lz_uncompress`. Any of these operations may throw the `ora-29294` exception, in particular if the crc check fails. I do not see any way to eschew the 'try-and-fail' approach in this scenario. Note that the outlined error condition does not affect the successful unpacking of any other part of the archive. – collapsar Nov 05 '15 at 13:58

Extracting BLOB files from Oracle table with UTL_FILE en masse, some compressed some not

2 Answers2