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

"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.

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.

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)

    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.

Solving the problem - you have a reproducible test case, have you sent it to support ask them what they see ?
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