Could not display bfile image properly

From: Meng <m.fangtao_at_genesis.co.nz>
Date: 7 Jul 2003 20:45:24 -0700
Message-ID: <1ddc56b5.0307071945.e367020_at_posting.google.com>

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 Received on Tue Jul 08 2003 - 05:45:24 CEST