Re: Could not display bfile image properly

Meng wrote:

> We are doing oracle imgration from Tru64Unix Oracle 8.1.7.0.0 to Linux Oracle
> 9.2.0.1.0 for testing. I use export/import whole Database to do migration.
> Everything looks fine except image display. We have a table that contains
> "BFILE" column to store image. We are using PL/SQL + HTP packages to diaply it.
> I recompiled all PL/SQL application packages and no error was found. But the
> image was displayed as mosaic
> Our table structure and PL/SQL source codes are below
> SQL> desc observation;
> Name Null? Type
> ----------------------------------------- --------
> OBSERVATION_ID NOT NULL NUMBER(16)
> IMAGE_RESULT BINARY FILE LOB
> ---------------------
>
> procedure display_observation_image (p_observation_id varchar2) is
> v_blob bfile;
> begin
> select image_result
> into v_blob
> from observation
> where observation_id = to_number(p_observation_id);
> display_image(v_blob);
> end display_observation_image ;
> -----------------------------------
> procedure display_image (p_blob in out bfile) is
> v_buffer raw(32000);
> v_buffer_size integer := 32000;
> v_offset integer := 1;
> v_length number;
> BEGIN
> owa_util.mime_header('image/jpeg');
> dbms_lob.open(p_blob);
> v_length := dbms_lob.getlength(p_blob);
> while v_offset < v_length
> loop
> dbms_lob.read(p_blob, v_buffer_size, v_offset, v_buffer);
> htp.prn(utl_raw.cast_to_varchar2(v_buffer));
> v_offset := v_offset + v_buffer_size;
> end loop;
> dbms_lob.close(p_blob);
> exception
> when others then
> ui.print(sqlerrm);
> end display_image;
> ---------------------
> I could not find any error in the PL/SQL codes. I can also use Oracle Apache to
> display the image directly ( I copied the image file (.jpg) to Apache directory
> to be displaied via IE or Netscape).
> The JPG image file is store at a Linux directory.
> I tried to import whole database and one Oracle account containing the table
> and PL/SQL packages. Both are the same problem.
> I did other 2 tests today. I export an account's schema then import
> it into 8i and 9i separately (OS is Tru64 Unix, imp username/passwd fromuser=**
> touser=** file=**), compiled all application PL/SQL packages and no error was
> found. The image was displayed very well in 8i but it still has the same
> problem in 9i. I think the differences are
> 1. Oracle version 8i,9i
> 2. Apache version and port number (8i: port number 7777,9i port number:7781)
> Does it make sense?
> Thanks.
>
> Meng
> I did Oracle migration between same plateform

This could be a typical 'big-indian', 'small-indian' problem. Meaning the arrangement of double words, High half of value is in lower address or lower half of value in lower address.

Address ....A1000 value 12345678
Address ....A1001 value 9ABCDEF0

Now is the double word value 9ABCDEF0123456789 or is it 123456789ABCDEF0, that is OS dependent. And to my best knowledge True 64 uses reverse order as Linux, Windows NT/200x and others. I think it originated from the Alfa processor and its internal workings.

So, if you just copied (tar, cpio,...) the bfile is 'reversed', and this is not a database problem - the bfile lives outside the data files. It is an external file only referenced from the database by a pointer.

To fix use cpio -pR... (see man or info for the OS) cpio will pass and reverse upper/lower values and your problem is fixed.

Regards

/Svend Jensen

Remove the spamkiller [S.] from reply address. Received on Tue Jul 08 2003 - 22:11:56 CEST