Thursday, 27 February 2020

Oracle APEX trigger JavaScript action after PLSQL validation

Oracle APEX validation JavaScript after PLSQL

Execute JavaScript after server side validations


apex.message.setThemeHooks to the rescue


Simple use case you have lots of existing PL/SQL validation on the page already implemented and you want to add new one that should validate and as it completes and raises error message you want to execute some JavaScript code.

In example below after I click Validate a popup message comes up and I need to show a region using JS.

How would we achieve this?

There are many blog posts covering both client and server side validations so I will not go into deep details here. Point to take is since we are doing PL/SQL validation here we are talking about server side validation. 

From what I know there is no silver bullet how you can simply call and execute JS from PL/SQL code so what is the catch.

This is how my validation is defined

It does not do much as it is more to showcase this demo. Every time button Validate is clicked it will fail and raise a validation error.

How do we execute a JavaScript call after my validation raises a popup on a screen?

Luckily there is very handy method exposed to us through apex.message widget called setThemeHooks which some of you may have come across if you ever wanted to auto dismiss your SUCCESS or ERROR messages on a screen in Universal Theme.

What this will allow us is to listen for APEX notification message being shown (or hidden) on a screen when validation fails which is perfect place for us to orchestrate JS actions we want to execute immediately after it.

If we now add little bit of JavaScript code to our page Execute when Page Loads section
    beforeShow: function( pMsgType, pElement$ ){
        if ( pMsgType === apex.message.TYPE.ERROR ) {
          alert('MESSAGE Triggering');               
We are catching the moment error messages are showed on a page. Since we are here now talking JavaScript it is not hard to trigger additional JS calls we want to do.

Note that code in my demo is actually this 
    beforeShow: function( pMsgType, pElement$ ){
      if ( pMsgType === apex.message.TYPE.ERROR ) {
        if ( $('li.a-Notification-item:contains("LINOTEST")').length > 0 ){
             alert('Good point');               
where I am only filtering messages that contain certain text. At this point I can easily do something like $('#my_region_id').show(); and region would show up after my validation failed. 

I know we can argue if this is the best way to handle the situation but still knowing how to hook/listen for your validation messages might come handy in other situations too. 

Live demo.



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 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: 
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:{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

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, 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="">
  <img src="">
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="" >
<img src="" >
<img src="">
<img src="">
<img src="">
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="'|| 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,