Apex 5 and the mview refresh

I've been trying to create a 'simple' app in apex to replace something that was written by one of my colleagues about 15 years ago in perl/apache on AIX. AIX is in it's final death throes here and this is practically the last thing we have to move to linux.

I tried initially to just port it but it was proving too difficult and my perl isn't up to much so time for a rewrite and a ditch of a lot of now unused functionality.

The screen i was trying to create was one to allow users (app support in this case) to refresh materialized views in our environment (we are using them  as local caches of reference data rather than any kind of aggregates or data summaries).

All the screen has to do is give a list of mviews, allow them to be selected and then refreshed which should be simple enough - in the end it was but i had some very frustrating bits along the way where it didn't seem to work - i'll gloss over those and just explain what i did.

(this is all in Apex 5.0.3 by the way but the general principles will work fine in older versions I'm sure).

First up i create a brand new app by just clicking through the create wizard and accepting all the defaults:

create ->desktop-> create application->create application

So now i have an 'empty' app - lets add something into it

I click on the home page that the wizard created and then do the following

1) drag a classic report region onto the page in the content body area - see screenshot below

2) Once that's added i then fill in the SQL query property with the following SQL (i'll come back to this in a minute)

select mview_name,LAST_REFRESH_DATE,apex_item.checkbox(10,mview_name) checker from user_mviews@dblink

(by the way the database link already exists i'm just making use of that - the SQL for that would just be something like "create database link dblink connect to user identified by pass using 'tns entry'")

3) I then change the property of the 'checker' column so that special characters are not escaped - otherwise it shows up as html text rather than an html checkbox

4) If i now run the application i get a screen like this

So the SQL has returned the mview name from the remote db link (blurred out in the case above) - the last refresh date of that mview and a checkbox column that apex create using the special apex function - lets review quickly that part of the SQL.

apex_item.checkbox(10,mview_name) checker 

So what this code does is easily seen by actually running it in sqlplus (i used dual below but you get the idea)

select apex_item.checkbox(10,sysdate) checker from dual;

<input type="checkbox" name="f10" value="06-JAN-16"  />

So the APEX function just returns the html code for a checkbox which Apex then turns into the visual checkbox on screen - note here that the value 10 i pass into the function determines the name used in the html (f10 in the case above) - this is important later on.

OK - so far so good but i currently have no way of actually refreshing those mviews - at the moment all i have is a view of a remote list of mviews and nothing else.

What i want to happen is when the checkbox is checked and i submit the page then some plsql is called to do that refresh - so how is that done?

Lets deal with the easy bit first - just adding the button

5)  So i drag the text button icon onto the items area (i actually want it in the bottom of region area but that didn't seem to show at first) - i then go through and changed the highlighted properties to match what i wanted

Now when i run the screen i get the new button added and clicking it submits the page - but that doesn't really do anything at this point

So clicking the above button submits the page but actually this does nothing other than causing the page to reload again.

So now the clever part, how do i make it so that something happens when the button is pressed - this is where i got stuck for quite a while - initially by just doing things in the wrong place and then secondly when debug wasn't actually showing me the debug i though it should be!

Let me show you the way to make this work

6) To do the magic we have to add a new process that runs on submission of the page - so we navigate to the process tab - right click after submit and add a new process. We then just need to choose the plsql type and paste in some plsql code that will work out which checkboxes we ticked and run an mview refresh for them (code for this pasted just below the pic)

    v_sql varchar2(4000);
    FOR i in 1 .. apex_application.g_f10.COUNT LOOP
     v_sql := ' BEGIN dbms_mview.refresh@'||'DBLINK'||'('||''''||apex_application.g_f10(i)||''''||','||''''||'?'||''''||'); END;';
      execute immediate v_sql;


Few comments on this code to give some explanation here.

First up (and this is the key clever part) - the built in apex_application.g_fxx (xx= 10 in the example above - i think it goes up to 50 that can be used) will parse out values from the submitted page that match that name - so in my case my checkbox code was specifically chosen as '10' which became f10 that we saw in the sqlplus output i pasted. This is then available by the function above to be referenced as g_f10 in plsql - this is very useful indeed. The screen data then just becomes available as a plsql array to work with.

So in the code above i just loop through the array (which is only populated with checked values in the case of a checkbox by the way).

For each value in that array i then build up a plsql call to a remote DBMS_MVIEW.REFRESH call to execute that refresh - in the example above i hardcoded in the DBLINK as at the moment i'm just proving the concept - eventually this will be a variable too.

I then output the command to be run to debug and then execute immediate that command.

The single quotes are a complete mess i know and you have to be very careful but it does work fine.

So if i run the screen now and select item 3 and 5 ( i screen grabbed before i checked the boxes but they were ticked :-))

And then click the refresh mview button the screen submits , there is a little delay while the plsql executes the refreshes and then i see this

So the page comes back - re executes the SQL that populates the data and i can see that the last refresh date has changed to today for the two i check marked.

Now that's surprisingly easy isn't it....? How long would that take to build in some other tool?

What caught me out and meant this took a lot longer than i thought it would is that initially i tried to do everything in a dynamic action when the button was pressed - i couldn't get this to work - i imagine it can be made to work but i wasn't getting anywhere - the process method seem to be the correct (and very simple) way to do this.

The other problem i had is that debug wasn't working seemingly - maybe i'm missing something in hows it's setup and what default levels are etc but the messages i wanted only seemed to appear when i used this code


When i just let the p_level default like below nothing appeared


Something more to read up on i guess.

Anyway to summarize this was a very useful exercise in how apex works and has got me along way towards porting the app off apache/perl.


Post a Comment