APEX

Hello everyone!

I'm afraid I need some assistance. As I've already indicated in a thread title, I have problems while trying to display an image stored within the database. I read zillion threads here on OTN, searched the Internet, but I can't make it work. More or less, it comes to what Denes Kubicek provided [url http://htmldb.oracle.com/pls/otn/f?p=31517:64:850093673123067]here

Tools I use are Oracle 10g XE database (10.2.0.1.0) and Application Express (3.2.1.00.10).

There's a table that contains information about certain products (such as printer toners, cartridges, CD media etc.). This is its description:

SQL> desc pm_materijal
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID                            NOT NULL NUMBER
 IDG                                    NUMBER
 SIFRA                                  VARCHAR2(30)
 SIFRA_KRATKA                           VARCHAR2(30)
 MODEL                                  VARCHAR2(30)
 NAZIV                                  VARCHAR2(255)
 NAPOMENA                               VARCHAR2(255)
 FILE_NAME                              VARCHAR2(200)
 MIME_TYPE                              VARCHAR2(255)
 BLOB_CONTENT                           BLOB

One of its records looks like this (other column values are unimportant) (columns are formatted so that they fit a single line):

 
SQL> select id, naziv, file_name, dbms_lob.getlength(blob_content) len
  2  from pm_materijal
  3  where id = 64;

        ID NAZIV                FILE_NAME                                    LEN
---------- -------------------- ---------------------------------------- -------
        64 CD recordable 1/50

Now I'd like to attach an image to those CDs.

In my Apex application, I created an item (on a page 7) whose name is P7_BROWSE (Display as "File Browse") - it is used to browse directories for files (images, actually). In order to support table record updating, I created a Process (Process point: On submit - after computations and validations).

if :p7_browse is not null then
   update pm_materijal set
     (mime_type, file_name, blob_content) =
     (select 
        mime_type, name, blob_content
        from wwv_flow_files
        where name = :p7_browse
     )
     where id = :p7_id;

   delete from wwv_flow_files
     where name = :p7_browse;
end if;

It seems that it works OK, because - once I select an image (it is a JPG file, its size is 116 x 116) and push the "Apply Changes" button - the result is as following:

 
SQL> select id, naziv, file_name, dbms_lob.getlength(blob_content) len
  2  from pm_materijal
  3  where id = 64;

        ID NAZIV                FILE_NAME                                    LEN
---------- -------------------- ---------------------------------------- -------
        64 CD recordable 1/50   F477411270/cd_50_komada.jpg                 2111

My next step was to create a stored procedure which will be used to display images:

 
SQL> create or replace procedure image_display (p_id in number)
  2  as
  3    l_mime        varchar2 (255);
  4    l_length      number;
  5    l_file_name   varchar2 (200);
  6    l_blob        blob;
  7  begin
  8    select mime_type,
  9           blob_content,
 10           file_name,
 11           dbms_lob.getlength (blob_content)
 12      into l_mime,
 13           l_blob,
 14           l_file_name,
 15           l_length
 16      from pm_materijal
 17      where id = p_id;
 18
 19     owa_util.mime_header (nvl (l_mime, 'application/octet'), false);
 20     htp.p ('Content-length: ' || l_length);
 21     owa_util.http_header_close;
 22     wpg_docload.download_file (l_blob);
 23  end image_display;
 24  /

Procedure created.

As suggested in a few OTN threads, I did this too (although I don't quite understand WHY, as I created the procedure in a schema I use in Apex; there are no other users involved). Anyway: I thought that it won't do any harm (but it didn't do any good either).

SQL> grant execute on image_display to public;

Grant succeeded.

SQL> create public synonym image_display for radni.image_display;

Synonym created.

Back to Application Express: I created a Reports Region (Type: SQL Query). Its source is :

select 
  id, 
  file_name,
  mime_type,
  dbms_lob.getlength(blob_content) len,
  --
  '<img src="#OWNER#.image_display?p_id='
         || NVL (ID, 0)
         || '" height="'
         || 120
         || '" width="'
         || 120
         || '" />' image
from pm_materijal
where id = :P7_ID

Finally, run the page! Reports region contains a single record which displays information I selected in SQL*Plus (so it seems that query DOES return something, and - I'd say that it is correct), but - instead of an image - there's an "invalid (broken) image" icon (you know, a small white rectangle with a red <font color="red">x</font>).

I can't figure out what I did wrong. It should work, but it doesn't. Could someone, please, point me to the right direction?

Regards,

LF