Apex page call stack

Page Call Stack Implementation

Purpose

The goal is to have a way to navigate back to the previous page. Typically this is done by a button, sometimes also by a page brache or a link column. A global application item A_LAST_PAGE is used to hold the numberof the last page.

The link inside the “Back to previous page button” then is rendered using this item with a page target: &A_LAST_PAGE.

This post describes how to setup the logic for populating this item using a complete page call stack. Thereby allowing to navigate several pages forth and back as wanted and always having some logical choice set as the last page.

Setup

Needed are two apex application items, one application process and a plsql module. The page stack is implemented using an apex collection.

The names of the items are declared as constants in the plsql module. So if you use a different item name, then you need to change the constant value there too.
Also if your homepage is not number 1 then you must change that constant value.

application items

A_LAST_PAGE => Will hold the page number. This item can be referred wherever you need to go back to the last page.

A_PAGESTACK_POINTER => the current position in the page stack. Entries are never deleted if we move back in the stack, just overwritten. So in theory the stack can also be used to “go forward” again.

application page process
The application process needs to run in the page head for each page. You can / should set a condition so that it doesn’t run for some special pages like Login, Help, Feedback or certain Modal pages. Although modal pages are taken into account if you use the default Apex 5 mechanism.

database code

The logic should work in all apex versions but for the rule 4. The isModalPage subfunction should only be used if you are on Apex 5.0 yet. Older version will raise an error because the page_mode column does not exists in previous versions of the apex_application_pages view.

  -- adds the current apex page to the page stack
  procedure managePageStack
  is
    -- constants
    co_modul_name          constant varchar2(100) := $$PLSQL_UNIT||'.managePageStack';
    c_appItem_last_page    constant varchar2(30)  := 'A_LAST_PAGE';
    c_appItem_page_pointer constant varchar2(30)  := 'A_PAGESTACK_POINTER';
    c_PageStack            constant varchar2(30)  := 'PAGESTACK';
    c_PageStack_max_size   constant number        := 500;
    c_Homepage             constant varchar2(30)  := '1';

    -- types
    type pageStack_t      is table of apex_collections.c001%type index by binary_integer; 

    -- variables
    v_pageStack           pageStack_t;
    v_current_page        varchar2(30);
    v_ps_pointer          number;
    v_last_page           varchar2(30);
    v_new_last_page       varchar2(30);

    -- sub modules
    function isModalPage(p_page in varchar2) return boolean
    is
      v_page_mode APEX_APPLICATION_PAGES.page_mode%type;
    begin

      select page_mode
      into v_page_mode
      from APEX_APPLICATION_PAGES
      where application_id = v('APP_ID')
      and page_id = p_page;

      return (v_page_mode='Modal Page');

    exception
      when no_data_found then
        return null;
    end isModalPage;

  begin
    ----------------------------------------------------------------------------
    -- read the current application items
    ----------------------------------------------------------------------------
    v_current_page  := v('APP_PAGE_ID');
    v_ps_pointer    := coalesce(to_number(v(c_appItem_page_pointer)),0);
    v_last_page     := coalesce(v(c_appItem_last_page),c_Homepage);

    ----------------------------------------------------------------------------
    -- make sure the collection exists
    ----------------------------------------------------------------------------
    if v_ps_pointer <= 0 then       -- create the collection       -- first page is always the homepage       --if not apex_collection.collection_exists(c_PageStack) then       apex_collection.create_collection(c_PageStack);       --end if;       -- just in case add the homepage       apex_collection.add_member(c_PageStack, p_c001 => c_Homepage);
      -- point to first page
      v_ps_pointer := 1;
    end if;  

    ----------------------------------------------------------------------------
    -- load the apex_collection into a plsql collection
    ----------------------------------------------------------------------------
    select c001
    bulk collect into v_pageStack
    from apex_collections
    where collection_name = c_PageStack;

    ----------------------------------------------------------------------------
    -- implement rules
    ----------------------------------------------------------------------------
    case 

    -- rule 0
    -- if something is wrong with the collection and we can not transfer it to a plsql collection
    when v_pageStack.count = 0
    then v_new_last_page := c_Homepage;
         if not apex_collection.collection_exists(c_PageStack) then
           apex_collection.create_collection(c_PageStack);
         end if;
         apex_collection.add_member(c_PageStack, p_c001 => c_Homepage);
         v_ps_pointer := 1;

    -- rule 1
    -- if the current page is the same as we have currently in stack, then do nothing
    -- this happens during a redirect to the same page
    when v_current_page = v_pageStack(v_ps_pointer)
    then v_new_last_page := v_last_page;

    -- rule 2
    -- if the new page is the same as the last page
    -- then go back in the stack one step. But never go below the first page
    -- we probably went back to the previous page, therefore last page needs to be even one more back in the stack
    when v_current_page = v_last_page
    then v_ps_pointer    := greatest(v_ps_pointer-1,1);
         -- the new last page is even one more page back
         v_new_last_page := v_pageStack(greatest(v_ps_pointer-1,1));

    -- rule 3
    -- if we are back to the home page reset everything!
    when v_current_page = c_homepage
    then v_ps_pointer := 1;
         v_new_last_page := v_pageStack(v_ps_pointer);
    -- rule 4
    -- ignore modal pages
    when isModalPage(v_current_page)
    then v_new_last_page := v_last_page;

    -- rule 5
    -- the new page is not under the current or last page, so we need to add it to the stack and increase pointer
    else
      v_new_last_page := v_pageStack(v_ps_pointer);
      v_ps_pointer:=v_ps_pointer+1;

      -- check to lessen the impact of endless loops and other nasty things
      if v_ps_pointer > c_PageStack_max_size then
        v_new_last_page := v_pageStack(1);
        v_ps_pointer := 2;
      end if;

      -- are we at the end of the stack already?
      if v_PageStack.count >= v_ps_pointer then
        -- change page in stack
        -- use the update_member_attribute function,
        -- because that is slightly faster than the update_member function
        apex_collection.update_member_attribute(c_PageStack, p_seq => v_ps_pointer, p_attr_number => 1, p_attr_value => v_current_page);
      else
        -- add page to stack
        apex_collection.add_member(c_PageStack, p_c001 => v_current_page);
        -- no need to add it to the plsql collection too!
      end if;
    end case;

    ----------------------------------------------------------------------------
    -- set the last page item
    apex_util.set_session_state(c_appItem_last_page    , coalesce(v_new_last_page,c_Homepage));
    apex_util.set_session_state(c_appItem_page_pointer ,v_ps_pointer);

  exception
     when others then
       -- add your own custom logging framework here
       logger.logError( co_modul_name, 'Problem during management of page call stack !'||'pointer='||v_ps_pointer||', current page='||v_current_page||', last page ='||v_last_page);
       raise;
  end managePageStack;

Check scripts

A DBA can execute the following statements to see what is happening.

alter session set current_schema = apex_050000;
execute wwv_flow_security.g_security_group_id := 10;
select * from wwv_flow_collections$;
select * from wwv_flow_collection_members$
where collection_id in (select id from wwv_flow_collections$ 
                       where collection_name = 'PAGESTACK');

Side Notes

Ideas

When we have such a call stack it opens up some other possibilities.
For example we can implement a dynamic breadcrumb bar that shows not only the static way to one page, but instead shows the way we used in our session. And if we go back in the call stack, we could even show the pages that we just left.

Tuning

While implementing the collection part I wondered
which is better (=faster) to use.
apex_collection.update_member or apex_collection.update_member_attribute

They both work slightly differently, but for my purpose (only one column) they are identical.

Here is the performance test that I did. Result is that apex_collection.update_member_attribute is almost a second faster when calling it 10000 times. This matched my expectation.

-------------------------------
-- test member_attribute 
-- performance test
set serveroutput on
declare
  v_time timestamp := systimestamp;
begin 
 apex_collection.create_collection('PageStack');
 -- add new page 10 to the stack
 apex_collection.add_member('PageStack', p_c001 => '10');

 v_time := systimestamp;
 -- update the member 10000 times
 for i in 1..10000 loop
    apex_collection.update_member('PageStack', p_seq => 1, p_c001 => '20');
 end loop;    
 dbms_output.put_line('Member           updated 10000 times: '||to_char(systimestamp-v_time));

 -- update the member 10000 times using attribute
 v_time := systimestamp;
 for i in 1..10000 loop
    apex_collection.update_member_attribute('PageStack', p_seq => 1, p_attr_number => 1, p_attr_value => '10');
 end loop;    
 dbms_output.put_line('Member attribute updated 10000 times: '||to_char(systimestamp-v_time));

 apex_collection.delete_collection('PageStack');
end;
/

Elapsed: 00:00:05.048
Member           updated 10000 times: +000000000 00:00:02.980000000
Member attribute updated 10000 times: +000000000 00:00:02.043000000

To run this yourself, you need to create a valid apex session state.
For example using Martin D’Souzas logic: http://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql.html

Nasty surprises

When you create an apex collection using the apex_collection package, the collection name will always be written in UPPERCASE. This cost me some time to identify the issue, because when reading it from apex_collections I used a lowercase name. And the collection was never found. So remember to always write collection names in UPPERCASE.

about IDLE session timers in Apex 5

Introduction

In a recent project I wanted to use the idle session timer. The final goal was to give the end user a possibility to see how soon his session will end. It happens when the user enters a lot of data in a tabular form and work is interupted, that all the effort is in vain because the idle timeout forces a new login.

Martin D’Souza wrote an article about that some time ago: http://www.talkapex.com/2009/09/enhanced-apex-session-timeouts.html. But it is a bit outdated now, and he does not reuse the Apex settings, and simply uses his own timer that accidentically is the same as the Apex timeout. Better would be an approach to use the Apex settings.

Surprisingly it was not so easy to find where the idle session time is set and how to use it progammatically. Here are my findings.

How to set the idle timeout

See also this older blog post from Patrick Wolf about setting the timeout in Apex 3.2. Some of it is still true, however some has change since then. For example we can now set the timeouts also on workspace level.

Is your Oracle APEX session timing out?

There are 3-4 levels where the session idle timeout can be set.
Each specific level overwrites the setting of the more generic level.

Instance Level

Login as instance administrator (INTERNAL workspace).

Manage instance / Security / Session Timeout

The default is 1 hour (=3600 seconds).

Workspace Level

As a instance administrator (INTERNAL workspace) go to

Manage Workspaces / Existing Workspace / Click on Workspace Name / Session Timeout
 

Application Level

Application / Shared Components / Application Definition Attributes / Security / Session Management

 

Session Level

This can only be set programmatically.

http://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_util.htm#AEAPI355

BEGIN
APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 1200);
END;

How to read the settings

Here are some commands that help to find out about the current session timeouts/idle timeouts. Note that to get the instance level settings (e.g. the default if nothing else was set), we can not use the official apex apis.
Update! This has been changed with version 5.0.4!

--- Find apex session idle times

-- Application level
select application_id, application_name, maximum_session_idle_Seconds, maximum_session_life_seconds, session_lifetime_exceeded_url, session_idle_time_exceeded_url
from apex_applications
--where application_id = :APP_ID
;

-- Application level - DBA access only
select security_group_id, display_id as application_id, name as application_name, max_session_length_sec, on_max_session_timeout_url, max_session_idle_sec, on_max_idle_timeout_url
from apex_050000.wwv_flows
--where display_id = :APP_ID
;

-- Workspace level
select workspace, workspace_display_name, maximum_session_idle_Seconds, maximum_session_life_seconds
from apex_workspaces;

-- Workspace level - DBA access only
select id, short_name, display_name, source_identifier, max_session_length_sec,max_session_idle_sec
from apex_050000.wwv_flow_companies;

-- Instance level - minimum Apex 5.0.4 needed + APEX_ADMINISTRATOR_READ_ROLE
select name, value
from APEX_INSTANCE_PARAMETERS
where name in ('MAX_SESSION_LENGTH_SEC','MAX_SESSION_IDLE_SEC');

-- Instance level - DBA access only
select name, value
from apex_050000.WWV_FLOW_PLATFORM_PREFS
where name in ('MAX_SESSION_LENGTH_SEC', 'MAX_SESSION_IDLE_SEC');

-- Instance level alternative - DBA access only
select apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_LENGTH_SEC') as MAX_SESSION_LENGTH_SEC
      ,apex_050000.wwv_flow_platform.get_preference('MAX_SESSION_IDLE_SEC') as MAX_SESSION_IDLE_SEC
from dual;

-- Workspace settings including Instance default overwrites - DBA access only
alter session set current_schema = APEX_050000;

set serveroutput on
declare
  v_ws wwv_flow_security.t_workspace;
  v_security_group_id number;
begin
  wwv_flow_security.g_security_group_id := 10; -- Internal
  v_security_group_id := wwv_flow_security.find_security_group_id (p_company => 'MYWORKSPACE');
  v_ws := wwv_flow_security.get_workspace(v_security_group_id);
  dbms_output.put_line('ws max kill timeout='|| v_ws.qos_max_session_kill_timeout );
  dbms_output.put_line('ws max session time in sec='|| v_ws.max_session_length_sec );
  dbms_output.put_line('ws max idle time in sec='|| v_ws.max_session_idle_sec );
end;
/

Please note that since Apex 5.0.4 we are able to read the instance settings from an official Apex view.

Therefore the following little script will give us the session idle time, regardless where it is set.

select coalesce(
   ( -- Application level
    select maximum_session_idle_Seconds
    from apex_applications
    where application_id = v('APP_ID'))
   ,( -- Workspace level
     select maximum_session_idle_Seconds
     from apex_workspaces
     where workspace = v('WORKSPACE_ID'))
   ,(-- Instance level
     select to_number(value)
     from APEX_INSTANCE_PARAMETERS
     where name ='MAX_SESSION_IDLE_SEC')
     ) max_idle_time     
from dual;

Unfortunatly to read from this APEX_INSTANCE_PARAMETERS view we need the new APEX_ADMINISTRATOR_READ_ROLE. The role is automatically granted to DBAs, Apex Admins and some other privileged database accounts during installation of Apex 5.0.4.

If you don’t have it already, then you can grant it like this

grant APEX_ADMINISTRATOR_READ_ROLE to mySchema;

How to run Node.js (Websocket) as windows service

In a recent project we are using websockets to respond in Apex to certain database events.

The websocket server is based upon node.js. One of the tasks was to setup this server as an service under a normal Windows Server 2003 (yes I know it is old) infrastructure. This post describes the steps how to setup such a service and also includes some monitoring information.

The prerequisties are that you already need to have node installed on your machine.

To start the websocket server we would usually call

node synwsserver.js

This starts the server. All the output (console.log) will be written to the terminal.

But we don’t want to run it manually each time. Instead we would like to setup it as a windows service. So here is how to achieve that. The same logic can be applied for any node module, not just for websockets.

 

1) Load node-windows

The node package that I used is node-windows. It is very lightweight and did not have dependencies to node-gyp which often gives trouble.

The command to install it is:

npm install node-windows

The author recommends to install it using the global flag -g. You might want to consider it. I did not encounter issues without the global flag.

2) Install the service

In the node “shell” run the following commands.
The script name is the same script that would be called directly from node.

File: installServiceApexWS.js

var Service = require('node-windows').Service;

// Create a new service object
var svc = new Service({
  name:'syn-apex-websocket',
  description: 'The websocket server for the APEX project.',
  script: 'D:\\tomcat\\nodejs\\synwsserver.js'
});

// Listen for the 'install' event, which indicates the
// process is available as a service.
svc.on('install',function(){
  svc.start();
});

// install the service
svc.install();

The name and the description can then be seen in the windows service tool.

Result

And we are able to start and stop the service.

Problem solved!

Ok there is a tiny bit more. We want to be able to uninstall it as well. And we need to think about the messages that were previously written to the console.

Run in Batch

The sequence of javascript commands can also be put into a .BAT file. I choose to separate the batch call from the js code, so there are two files now.

File: installServiceApexWS.bat

echo "Installing service..."
start "install service - Syntegris APEX websocket server" node installServiceApexWS.js
echo "Service installiert."
exit;

 

3) Uninstall the service

The logic to deinstall the service is very similar to installing it.

File: uninstallServiceApexWS.js

var Service = require('node-windows').Service;

// Create a new service object
var svc = new Service({
  name:'syn-apex-websocket',
  description: 'The websocket server for the APEX project.',
  script: 'D:\\tomcat\\nodejs\\synwsserver.js'
});

// Listen for the 'uninstall' event so we know when it is done.
svc.on('uninstall',function(){
  console.log('Uninstall complete.');
  console.log('The service exists: ',svc.exists);

});

// Uninstall the service.
svc.uninstall();

File: uninstallServiceApexWS.bat

echo "Uninstalling service..."
start "uninstall service - Syntegris APEX websocket server"  node uninstallServiceApexWS.js
echo "Service deinstalliert."
exit;

 

 

 

4) Add event logging

Node-windows comes with same basic windows event logging. That means certain type of actions can be written into the default windows eventlog.

An example

/*
  add logging for Windows events
*/
var EventLogger = require('node-windows').EventLogger;
var log = new EventLogger('syn-apex-websocket');

...

/* Start listening on the configured port and write to standard output*/
server.listen(config.port, function() {
    console.log((new Date()) + ' Server is listening on port ' + config.port);
    log.info('Server is listening on port ' + config.port);

});

An this is how the result looks like in the Event Viewer (search for Event on your Windows Server) to find the tool.