Particular file (PDF) cannot be loaded into Oracle BLOB. Makes sense ?

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Tue, 15 Mar 2011 13:36:21 -0400
Message-ID: <qsNfp.19889$Lj7.60_at_en-nntp-03.dc1.easynews.com>

Hello

I have a particular set of PDF files that cannot be loaded successfully into an Oracle BLOB column.

I don't know why that is, and Oracle support says that my file is corrupt ???!!!

The file can be opened no problem with Adobe's Acrobat reader. I can load other PDF files into a BLOB, extract them, and they are still good to Acrobat.

But these files, no.

What I can see if when they are loaded into the BLOB, some extra characters are added, and while the BLOB contains the same number of bytes as the original file, it is in fact truncated due to the extra characters added (i.e. 200 extras added = 200 truncated at the end of the file).

I loaded the file with DBMS_LOB:LoadCLOBFromFile like this :

declare

l_blob    BLOB;
l_bfile   BFILE:=BFILENAME('COPY_DB','ORIGINAL_FILE.PDF');
v_dest_offset integer:=1;
v_src_offset integer:=1;

begin
 insert into test
values(1,EMPTY_BLOB())
 returning pdf into l_Blob;
 dbms_lob.fileopen(l_bfile,dbms_lob.FILE_READONLY);  dbms_lob.loadblobfromfile(dest_lob=>l_Blob, src_bfile=>l_bfile, amount=>dbms_lob.getlength(l_bfile),

        dest_offset=>v_dest_offset, src_offset=>v_src_offset);  dbms_lob.fileclose(l_bfile);
 commit;
end;
/

If I do this SQL below into a SPOOLed file (because on the screen it is reall gibberish) from SQLplus, I do see the extra characters when I load the spooled file into a text editor, and compare that with the PDF file also loaded into the text editor.
select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(PDF,2000)) from test;

A hexadecimal dump of the original imported file, compared to that of the exported file, also shows the difference.

Oracle support is trying to convince me that this is normal, certain files cannot be loaded into a BLOB. I don't see that makes any sense. They also blabber about character set conversions, which should not interfere as this is not text (CLOB) but binary (BLOB).

Lastly, if I read the file into a dot net program variable, then update the BLOB column with that variable's value, the file is loaded correctly. And can be extracted correctly too.

So obviously something is wrong with dbms_lob.loadblobfromfile unless I miss some imprtant point.

To me, a byte is a byte and as long as the file is loaded as binaty data, every single byte should be read and moved into the BLOB column without any change whatsoever.

Thanks for your comments. I could not see anything in Google or Metalink expaining that some file could not be loaded into a BLOB.

Thanks !

Syltrem

P.S. I also tried loading the file with SQL*Loader and it too adds extra characters, but does not truncate it (the BLOB colum is larger than the size of the original file). Upon extraction, the file is seen as corrupt by Acrobat (of course). Received on Tue Mar 15 2011 - 12:36:21 CDT