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

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.

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.