Friday 24 January 2020

Oracle APEX image gallery

Oracle APEX image gallery

Let's keep it simple

Powerful responsive gallery based on images in database



We all needed a simple image gallery to show case our images. Looking at it from the above things are simple:  
  1. we need a place to store an image
  2. we need something to retrieve this image from DB
  3. have an element on the page to show the gallery
First thing that came to my mind is to go and try out built in APEX component called Carousel region, but since my images needed to be dynamic based on user selection (for a property we could have 1 or 25 images uploaded to database) I started to look for an alternative.  

Another approach was to check APEX Plugin repo on apex.world. There are few galleries available and you should definitely check them out. They might be what you are looking for.

If you are more of DIY person then please keep on reading, after all the beauty of being an APEX developer is having the flexibility to do anything you find elsewhere on the internet. 
Let's get cracking!!! to store an image we need a table. Something simple as this should do: 
CREATE TABLE  DOCUMENTS
   (ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOT NULL ENABLE,
    FILE_NAME VARCHAR2(100),
    BLOB_CONTENT BLOB,
    MIME_TYPE VARCHAR2(255),
    CHAR_SET VARCHAR2(128),
    LAST_UPDATED DATE
   );  
Note: I will assume we know how to upload an image to a table using APEX. There are many blog posts around to help you with this. Or you can even use a SQL Developer to do this for you.

Once we have an image we need a mechanism to return it to the page so we can reference it with something like <img src="myimage.png" />. This is where I will use ORDS. 


Navigate to SQL Workshop and then RESTful Services. Make sure you REST enable your schema first then select Modules from the menu on the left and create a module:

After then click Create Template:

In which we create a GET Handler:

Now if you uploaded any of the images into your table you should be able to test your REST service with link similar to:
https://apex.oracle.com/pls/apex/lschilde/gallery/images/{id}
If you got the image back you are ready for the last step. Notice that for this demo purpose I have not secured my REST API. Very detailed info how to do REST part you can find here. There is also an example how to create and handle POST requests that you may use to implement file upload functionality too. Plus there is info how to secure your REST handlers.

Last part is how do we get a nicely looking component that is above all light, responsive and support what all today's image galleries support things like: 

  • Dimensions
  • Fullscreen
  • Thumbnails
  • Video
  • HTML
  • Fit
  • Transition
  • Captions
  • Loop
  • Autoplay
  • Keyboard
  • Shuffle
  • Arrows, click, swipe
  • Navigation position
  • Right-to-left
  • Initialization
  • Lazy load
This is where external JavaScripts come in handy. If you google for JS image galleries there is tons of them making it hard to pick the right one. I decided to use fotorama.io

       
You can not go wrong here, reason why I ended using fotorama is because it is so damn simple to integrate into APEX. 
How do we do this? 
Reading at basics doco, fotorama.io requires one JavaScript and once CSS file which we can easily add to our apps either as Application images or referencing stored files from the server under page properties like:

 All now that is needed is a simple HTML region:
<div class="fotorama">
  <img src="https://s.fotorama.io/1.jpg">
  <img src="https://s.fotorama.io/2.jpg">
</div>
and you can see how fotorama is getting the images. 

If we create a static content region where we replace sample URLs with our REST URLs we are almost there. Images should be displayed on your page:
<div class="fotorama" data-nav="thumbs" data-fit="contain" data-width="100%" data-ratio="800/600" data-minwidth="400"
data-maxwidth="1000" data-minheight="300" data-maxheight= "100%">
<img src="https://apex.oracle.com/pls/apex/lschilde/gallery/images/1" >
<img src="https://apex.oracle.com/pls/apex/lschilde/gallery/images/2" >
<img src="https://apex.oracle.com/pls/apex/lschilde/gallery/images/3">
<img src="https://dummyimage.com/600x400/000/fff">
<img src="https://dummyimage.com/800x600/000/fff">
</div>
Brilliant!! How do we make it dynamic so that we can refresh it as needed.

Create a classic report region with SQL query as a source:
select LISTAGG('<img src="https://apex.oracle.com/pls/apex/lschilde/gallery/images/'|| ID || '">', ' ')
       WITHIN GROUP (ORDER BY id)
       AS images
from documents
Set template to Blank with attributes (No Grid).
Set report template options to: 
Change report column property to:
Save and run your page. Isn't it so great how all of these APEX and DB components simply come all together. For those of you newer to APEX some of these techniques will not look familiar but with all steps listed hoping this will make it an easy read. 

Live demo.

Happy APEXing,
Lino

5 comments:

  1. Nice one.. Can we expect plugin on that library?
    Best regards,
    Andrej Grlica

    ReplyDelete
  2. Hi Andrej, that is a great idea. This shouldn't be hard to do so I may even give it a go...... I agree this makes a good material for a plugin. Thanks Lino ;)

    ReplyDelete
  3. Very useful article.
    Thank you!

    Cheers,
    Stefan

    ReplyDelete
  4. Hi, I am using apex 19.1 and follow all the step but I have a problem

    - Change report column property to --> I cannot find that property

    thx,
    Juan

    ReplyDelete
    Replies
    1. In your report region (classic report) select a column and on property panel on your right find Column Formatting section. It should be same on all versions of APEX

      Delete