Oracle Database Tips by Donald Burleson

Creating the Display Image Application Page with APEX

1.       Click on the Create Page button from the Application home page.

2.       Choose the Report option and click Next.

3.       Choose the SQL Report option and click Next.

4.       On the Page Attributes page:

·        Page: 1091

·        Page Name: Easy Image Display.

·        Click Next.

5.       On the next page, leave Do not use tabs and click Next.

6.       On the SQL Query page, enter the following as the SQL SELECT statement and click Next.  It will select every record from the EASY_IMAGE table.  Only select the IMAGE_ID column because this is what needs to be sent to the DISPLAY_EASY_IMAGE procedure.

select image_id from easy_image;

7.       On the Report Attributes page:

·        Region Template: Report Region, Alternative 1.

·        Report Template: default: Look 1.

·        The two options above are chosen to give an idea of other templates and how they look.

·        Region Name: Easy Images.

·        Click Next.

8.       Click Finish.

9.       On the Success page, click the Run Page icon.

At this point, the only data in the report is that of the IMAGE_ID column.  Next, the HTML Expression property of the IMAGE_ID report column will be used to pull the image from the table using the DISPLAY_EASY_IMAGE procedure.

Using HTML Expression to Display an Image

A lot is going to happen in this next section, so be patient as the details are presented.  

The information on the HTML Expression section explained that it could be used to modify how the report column is displayed to the user.  What is about to happen here will show the great flexibility of this column attribute.  It will actually cause the APEX Engine to execute a stored procedure on our behalf and return the necessary HTML to display an image in the browser.  This is made possible by the mod_plsql module.

1.       Navigate to the Page Definition page for application page 1091.  If the page is still running, click on the Edit Page 1091 link in the developer toolbar.

2.       Click on the Report link next to the Easy Images region.

3.       Click on the  icon next to the IMAGE_ID column.

4.       Enter the following text in the HTML Expression text area and click on the Apply Changes button.

<img src="#OWNER#.display_easy_image?p_id=#IMAGE_ID#" />

Ok, this needs some explaining.  It took me some time to learn and understand what was happening here so I hope I can explain it well so you do not have the same pain I had.

The HTML Expression is going to override the way the report column is rendered to the user.  An image tag, <img>, is being created in order to display the image.

The src part of the tag is calling the stored procedure created earlier.

Previously, it was mentioned that the value from a column in the SQL select statement could be used here by using the substitution string.  This is exactly what is being done here by using the #IMAGE_ID#substitution string.  The value returned in the query will be substituted in by the APEX Engine.  The results to the APEX Engine will look something similar to the following:

<img src="#OWNER#.display_easy_image?p_id=2739007729974300" />

This calls the DISPLAY_EASY_IMAGE PL/SQL procedure.  The text after the question mark is the name of the parameter declared in the PL/SQL procedure as indicated below.

create or replace procedure display_easy_image( p_id number ) as
   …

This is the syntax used in APEX in order to send parameters to stored procedures and functions.  When APEX renders the page, it calls the PL/SQL procedure and sets the p_id parameter with the #IMAGE_ID# value for each row in the report.  It will then return each of the images in a different row in the report.

As an exercise, navigate back to the Easy Image Upload page, load another image, and view the loaded image in the Easy Image Display report.

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm