Clear Cache for all Pages within a Page Group

Most client sites I have been on tend to love wizards that hold the users hand through often complex business process / workflows.

As a general rule, if I have a 10 step wizard (I generally use 1 page per step) I like to ensure that all 10 pages have their cache cleared as the user enters the wizard to ensure no values from a previous attempt to complete the process are hanging around. This is typically done by enter a comma delimited string of all the page numbers you wish to clear the cache of upon branching from page to page.

In an ideal world, what I wanted to do was take advantage of the fact that I had taken to time to put all 10 pages into a page group and clear the cache of ALL pages contained within. Furthermore, using this logic, pages can come and go from the page group without the need to worry about how this effect my clearing of session state. Pretty cool huh, but wait, "APEX only allows you to enter page numbers and not page group names into the clear cache text box" I hear you say.

Not a problem. This can be achieved very simply by using an Application Item, A Single Application Process and Page Groups. Here is how:

Solution.

1. Create a Page Group with a name the logically describes the functional area it servers. In this example I will use the name: "CREDIT_CARD_APPLICATION".

2. Edit the definition of all pages (10 in my case) and them to this newly created group.

3. Create an Application Item called the same name as your group created in step 1 i.e. my Application Item was named: "CREDIT_CARD_APPLICATION".

4. Create an Application Process called: "Get CRETID_CARD_APPLICATION_PAGES" and of Process Type: "On New Instance (new session)" and add the following code to the Process Text region:

BEGIN



select LISTAGG(page_id,',') WITHIN GROUP (ORDER BY page_id)
  into :CREDIT_CARD_APPLICATION_PAGES
  from apex_application_pages where application_id = :APP_ID
   and page_group = 'CREDIT_CARD_APPLICATION';


EXCEPTION
WHEN NO_DATA_FOUND
THEN
 :CREDIT_CARD_APPLICATION_PAGES := NULL;


WHEN OTHERS
THEN


 RAISE;


END;

5. Finally, in the branch that you want to clear the cache for all pages within the page group, simply place a substitution string reference to &CREDIT_CARD_APPLICATION_PAGES. in the clear cache text box and viola!!!

Explanation.

This very simply queries the APEX Data Dictionary to work out all the pages within your names Page Group.

It then uses the XMLAGG aggregation function native to 11gR2 (I blogged about this here) to build a comma delimited string of all these pages and assigns the output to your application item. (Not is done once per session for performance reasons).

Now that you have a list of all the pages in your page group as a value in session, you can substitute this into any branch using the standard substitution syntax. Simple really but ideally, it would be nice if Oracle provided this out of the tin. Until then, this seems to be a pretty good workaround.

Comments

Dan McGhan said…
Hi Duncan,

I like your workaround for what could be a neat little feature if expanded upon in a more general sense: the ability to declaratively reference page groups.

This could be used in various places in APEX. As a target for clearing cache as you said, but for other things too, like component conditions. Rather than use "Current Page Is Contained Within Expression 1 (comma delimited list of pages)", you could just select "Current Page Is Within Page Group" and then somehow specify the page group. Very cool indeed.

I recommend you post this to the new feature request application for APEX: https://apex.oracle.com/pls/apex/f?p=55447

Then leave a note here about which key it is so others that like it can vote for it.

Regards,
Dan
6 July 2012 at 16:18
Duncan said…
Cheers dan. Thanks for adding it to feature request list for 4.2

It makes total sense to have declarative access as you mentioned so let's hope this simple request makes the cut.

Duncs
6 July 2012 at 16:29
Dan McGhan said…
Hi Duncan,

Actually, I didn't add it - I didn't want to steal your thunder ;)

I was just suggesting that you do so. Having said that I'd be more than happy to add this if you like.

Regards,
Dan
6 July 2012 at 17:45
Duncan said…
Done.
Request added as Row Key: ABOD

https://apex.oracle.com/pls/apex/f?p=55447:1
6 July 2012 at 18:56
Post a Comment

Popular posts from this blog

Custom Authentication / Authorisation Schemes (Part 1)

I often see posts on the APEX forum asking how to implement custom Authentication / Authorisation schemes within their applications. The following is something I have used in several apps over the last couple of years and provides a great base for securing your application. The code base is largley based on the rather excellent article " Storing Passwords in the Database " found here with a few tweaks and changes. Firstly, just to clarify, Authentication Schemes control access to the application and Authorisation Schemes control access to page items / regions and even pages themselves. Implementing your own Authentication I tend to set up an APP_USERS table that stores Username and encrypted passwords that I Authenticate against when page 101 is submitted. All this will be explained in detail as we go. Create the APP_USERS table CREATE TABLE APP_USERS ( USERNAME VARCHAR2(10), PASSWORD VARCHAR2 (255) ); Create the Application Security Package CREATE OR REPLACE PACKAGE app_sec...
Read more

Custom Error Handling in APEX

If like me you have written several PL/SQL procedures within your APEX app, you will have had to deal with the issue of error handling. As a general rule, I try to hide all Oracle Error Messages from the user and replace these with something more meaningful for example: If a user breaks a unique key by trying to insert the same value twice, the following error message is not uncommon: ORA-00001 Unique constraint violated There is little or no merit rendering this message to a user as those outside the Oracle community, may not be able to deduce what went wrong. We can capture this error and translate it to a user friendly message explaining what went wrong for example: The record you tried to create already exists. Please try again In order to achieve this in APEX, the process is relativly simple. Set up your Custom Error Page 1. Create a new page with the following attributes: Page type: Blank Page Page Number: your choice (for this example I used page 500) Page Alias: ERRPAGE Name:...
Read more

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy. For example: CREATE TABLE DEPT ( DEPT_ID NUMBER NOT NULL ,PARENT_ID NUMBER ,DEPT_NAME VARCHAR2 (100) NOT NULL ,EMPLOYEES NUMBER NOT NULL ,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID) ,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID) REFERENCES DEPT (DEPT_ID) ); INSERT INTO DEPT VALUES (1,NULL,'IT', 100); INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12); INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15); INSERT INTO DEPT VALUES (4,1,'TEST', 25); INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2); INSERT INTO DEPT VALUES (6,2,'Java', 1); INSERT INTO DEPT VALUES (7,2,'SQL', 11); INSERT INTO DEPT VALUES (8,2,'C++', 3); INSERT INTO DEPT VALUES (9,4,'Functional', 3); INSERT INTO DEPT VALUES (10,4,'Non Functional', 5); COMMIT; A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child rel...
Read more