Re: Particular file (PDF) cannot be loaded into Oracle BLOB. Makes sense ?
From: prunoki <hegyvari_at_ardents.hu>
Date: Tue, 22 Mar 2011 01:09:07 -0700 (PDT)
Message-ID: <f7aa4a47-11a6-467b-b502-62d08c766578_at_q36g2000yqn.googlegroups.com>
On Mar 21, 3:06 pm, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in message
>
> news:su-dnfjX9eYJ5BnQnZ2dnUVZ8l2dnZ2d_at_bt.com...
>
>
>
>
>
>
>
>
>
>
>
> > "Syltrem" <syltremz..._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
Date: Tue, 22 Mar 2011 01:09:07 -0700 (PDT)
Message-ID: <f7aa4a47-11a6-467b-b502-62d08c766578_at_q36g2000yqn.googlegroups.com>
On Mar 21, 3:06 pm, "Syltrem" <syltremz..._at_videotron.ca> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in message
>
> news:su-dnfjX9eYJ5BnQnZ2dnUVZ8l2dnZ2d_at_bt.com...
>
>
>
>
>
>
>
>
>
>
>
> > "Syltrem" <syltremz..._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
Do you use utl_file to extract the blob? Maybe I am wrong, but I remember that back in the days I had to switch over to using Java because utl_file inserted extra piece of lf-s into my file. If you have a Toad or some other utility, any other means to extract the blob, you should try it to verify.
Krisztian Received on Tue Mar 22 2011 - 03:09:07 CDT