Oracle Apex: show picture in report from a different table?

Question

I have the following apex report :

---

select id,
    user_ as participant,
    --This is a blob
    dbms_lob.getlength(MY_UTILITIES.GetUserPicture(user_)) as Picture,  
    order_ as order_
from My_Operations

---

I would like to display the column Picture as an image in the report. It's a blob stored in another table : Table My_Users (the Primary key column name is 'ID')

I configured as follows the Picture Column :

enter image description here

The report generates but the picture doesn't show :

enter image description here

I'm sure using this implementation Apex has no way to know that the field picture corresponds to the row X in the table My_Users.

Does anyone know how to correct that please ?

Cheers,

Answer 1

The way APEX displays images in reports is somewhat weird and wacky it would seem. What you need to do is apparently undocumented but I believe it is as follows:

1. The column that will be displayed as an image must contain the length of the BLOB column (which you have done correctly). I found this out from this blog post.

2. The column specified for Primary Key Column 1 specified in the BLOB attributes of that column must also appear in the report with the same name. I figured that out by trial and error.

You have aliased column user_ as participant. APEX requires it to be called ID - but you already have a different column called ID! So I think you should try this:

select id as op_id,
    user_ as id,
    dbms_lob.getlength(MY_UTILITIES.GetUserPicture(user_)) as Picture,  
    order_ as order_
from My_Operations