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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:su-dnfjX9eYJ5BnQnZ2dnUVZ8l2dnZ2d_at_bt.com...
>
> "Syltrem" <syltremzulu_at_videotron.ca> wrote in message
> news: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.
>>
>>
>> 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;
>> /
>>
>
>> 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.
>>
>
>
>
> Your interpretation seems very unlikely - unless you are using an early
> version of Oracle that has some (hypothetical) silly bugs that have been
> eliminated in more recent patches releases.
>

It's not my interpretation, it's that of the Oracle support person handling my SR.
He said the files loaded by loadblobfromfile must be "supported". Was news to me but he said this is so.

> If this happens to pdf files it would also happen arbitrarily to wav, doc,
> avi, mov, xls, etc ... and there would probably be lots of people
> complaining about LOBs in the database not working.
>

I cannot agree more, and tried to expain this to Oracle but failed somehow...

> Checks:
>
> Is the directory you read from NFS mounted or Samba mounted across
> platforms ? (Yes, your comment about BLOB/CLOB should make this
> irrelevant, probably)
>

No, the file is local to the server. I did this also with Oracle XE (Win XP) with the PDF on my C drive, with the same results. I'll try that again just to be 200% sure as it makes less and less sense.

> Is the character set of the server the same as the characterset of the
> session that runs your SQL*Plus extract ? (And your comments about
> BLOB/CLOB aren't relevant here because you're using raw_to_varchar2() to
> convert a stream of raw bytes into characters.
>
> Finally: when you spool the raw_to_varchar2() output, it may be
> SQL*Plus that is taking every LF and turning it into CRLF. If you want to
> compare input and output you'll probably have to do it the hard way, use
> rawtohex() on the substr() result, and compare with an od -x (or similar)
> of the input file and compare by eye.
>

Agreed, but it shows the same extra characters in the same spot as I see them in the file I create when the BLOB is extracted with UTL_FILE. But it's not CRLF, it's FFLF or LFFF (can't remember if LF is added in front of FF or after it)

And I know it's not my extraction procedure that's wrong as it can successfully extract the PDF it it was originally imported to the BLOB with a dot net program.
Unless of course there really is something weird about character set conversion (which should not happend with BLOB). I run the load and extract PLSQL from the same session... so NLS settings can't change.

>
> Solving the problem - you have a reproducible test case, have you sent it
> to support ask them what they see ?

They say they can reproduce, and the problem is my PDF which is probably "wrong".

???

> Find the smallest file that reproduces the problem - preferably something
> that fits inside your 2000 byte limit, and sent it to Oracle with your
> input script, output script, and output file.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
>
Received on Mon Mar 21 2011 - 09:06:06 CDT