Friday, July 18, 2008

Show Blob of other table in APEX Form

A few weeks ago I got a mail with this question:
--
I read your answer in the forum on a problem regarding : display BLOB in Apex.

My question is if it is possible (and HOW) to display an image on a form page based on table A but the image is in table B (and table A contains the values to read the blob(s) from table B).

I guess I have to add an (file browse) item in the form but what's next ??
--

It's not that difficult to get around this problem with the standard functionality in APEX 3.0 and up, if we use the BLOB support that the APEX team developed for us.

Our test case:
* Database level


CREATE TABLE ex_A
(
id NUMBER(6) ,
name VARCHAR2(50),
picture_label VARCHAR2(30),
picture_id NUMBER ,
CONSTRAINT EX_A_PK PRIMARY KEY (ID)
);
CREATE TABLE ex_B
(
picture_id NUMBER ,
picture BLOB ,
picture_filename VARCHAR2(100),
picture_mimetype VARCHAR2(100),
CONSTRAINT EX_B_PK PRIMARY KEY (PICTURE_ID)
);

* APEX level

Create a new application with a Form based on table ex_A and a Form based on table ex_B by following the wizard.

We adapt Page 2 - Form based on table ex_B - and change the item P2_PICTURE to


(On Page 2 and Page 1 I also made the Hidden and Protected item - the pk - visible as I don't have a trigger to populate it, so I just give a unique id in myself.)

So now the answer to the question... How do you display the blob from ex_B table on the form that is based on table ex_A?

Add a new item: Display as Text (does not save state) and in the Source (PL/SQL Function Body) you specify:


As you can see we use the APEX_UTIL.GET_BLOB_FILE_SRC function. It will look at page 2 for the format of the blob and will pass the picture_id to it.

If you upload a file on Page 2 and after wards in Page 1 you reference the picture_id, it should get displayed.

You can try to upload a picture yourself here. Click first the Ex_B tab and upload a picture, next go to Ex_A tab and create a reference and add the same id as for the one you used for the picture.

The export of the application can be found here (save as blob_support.sql).

Hope that answer the question.

16 comments:

Anonymous said...

Dimitri apexblogs is down - sent you a mail too

Dimitri Gielis said...

Hello,

For me it works?

Thanks,
Dimitri

Rutger said...

Hi Dimitri,

Nice example of the blob support, I've did this before also. And when I did I asked myself, wouldn't it be logical that when you set a BLOB field to "display only" and define in the format that it is an image, it shows you the image, instead of defining it yourself in HTML with the BLOB function ? Just a thought maybe for the future.

Greetings,
Rutger

ncaldas said...

Hey friend, thanks for you post, please you know how modify the size of the image when it is show...

Anonymous said...

Hello there!
I was wondering if you knew of a way to re-size the blob within the table without changing the size in the database. ie, i the gif image that is in the database to display initially as a smaller (little bigger than an icon) and then the user will click or hover over ands it will expand to the normal size.

Dimitri Gielis said...

Hello,

I use that in DG Tournament, where I use highslide.
Have a look here: http://dgielis.blogspot.com/2007/01/dgt-explained-1-bis-thumbnail-expander.html

Anonymous said...

I followed your directions in your http://dgielis.blogspot.com/2007/01/dgt-explained-1-bis-thumbnail-expander.html tutorial but was just left with the red "x". We store gif images.

Anonymous said...

Dimitri, can you please clear up wthe table ? The app is not working anymore.

Unknown said...

Hi Dimitri

I have a problem, i need to display an image stored in the OS, for example /data/images/image01.jpg

how can i do that?

Thanks.

Anonymous said...

Hi Dimitri,

How do you dynamically show the thumbnail from a regular image ?
rather than having 2 files, thumbnail and image file

What I tried to accomplish is:
Show in-line thumbnail pics on each record, and when you click or mouse over the thumbnail then show the image

Anonymous said...

Thanks Dimitri

garco said...

Hoi,

Beetje oude blog, ik hoop dat hier nog naar gekeken wordt :)

Bij mij staat er in het text veld de hele , het plaatje wordt dus niet getoond.

Wat doe ik niet goed? Bij reports moet je er standard report column van maken, maar bij een form kan dat niet toch?

gr.

garco

Dimitri Gielis said...

ik kan niet alles goed lezen wat je schrijft, maar in APEX 4.2 kan je display image item type gebruiken...

garco said...

Thanks.
Het was inderdaad de display image, daar een beetje aan sleutelen (ook met hulp van otn-users) en het werkt...

Anonymous said...

Hi, thx for article. To other guys - I had a problem with blob i apex beacuse of using table from the second workspace. So in apex workspace I used B.TABLE connections and in that situation it's not working. I can see everything in raport but i cannot download blob. The workaround is to build a view "as select * from B.table". Maybe it was problem with settings of workspace, or private synonym which was created on that table. I don't know. View is working, and it's good for me

Cristina said...

Hi Dimitri,

I have a question. How can I create an Image upload item in a form aria that can be viewed/displayed in a interactive report in the same page?

Thank you,
Cristina